Friday, 7 February 2014

Enhance Event Booking template further with a date and time picker

Thanks to a post made by Serge Gabet I was able to enhance further the Google Sheets booking template described in my earlier post

With the new script in place, a new menu item is added to the Sheet, allowing users to pick a date and time for the event, using a generated picker.  Apart from ease of use, this also has an important benefit, as it ensures that dates entered into the sheet are correctly formatted before the event is imported into the Google Calendar.

My onOpen script now looks like this, with the new function "listBoxVersion" added to the menu.

function onOpen() {
  var menuEntries = [{
    name: "Import Events in Calendar",
    functionName: "importIntoCalendar"
  },null, {
    name: "Notification Setup (on form submit)",
    functionName: "notificationSetup"
  }, {
    name: "Reminder Setup (24hrs before the event)",
    functionName: "reminderSetup"
  }, {
    name: "Instructor Feedback Surveys (sent after event)",
    functionName: "InstructFeedbackSetup"
    name: "Attendee Feedback Surveys (sent after event)",
    functionName: "FeedbackSetup"
  }, {
    name: "Alert Setup (if not enough participants)",
    functionName: "alertSetup"
  },null, {
    name: "Report an issue",
    functionName: "reportIssue"
    name: "insert date/time", 
     functionName: "listBoxVersion"
  ss.addMenu("Event booking", menuEntries);

The remaining script was just appended to the end of the main script

function listBoxVersion() {
  var app = UiApp.createApplication().setTitle('Time Picker');
  var main = app.createGrid(2, 4);
  var date = app.createDateBox().setName('date');
  var hour = app.createListBox().setName('hour').setWidth('100');
  var min = app.createListBox().setName('min').setWidth('100');
  for (h=0;h<24;++h){
  if(h<10){var hourstr='0'+h}else{var hourstr=h.toString()}
  for (m=0;m<60;++m){
  if(m<10){var minstr='0'+m}else{var minstr=m.toString()}
  var button = app.createButton('validate')
  main.setWidget(0,0,app.createLabel('Choose Date')).setWidget(0,1,app.createLabel('Choose Hours')).setWidget(0,2,app.createLabel('Choose minutes'))
  var handler = app.createServerHandler('show').addCallbackElement(main)
function show(e){
  var cel1 = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getActiveCell().getA1Notation();
  //ss.getRange(cel1).setValue(Utilities.formatDate(,'GMT+02:00','MMM-dd-yyyy')+'  @ '+e.parameter.hour+':'+e.parameter.min)
  var date = new Date(;

No comments:

Post a Comment