Calendar Service


Contents

  1. 1 Date Calculations Tutorial:
  2. 2 Calendar Service Example:
    1. 2.1 Calendar Service Function:
    2. 2.2 On Form Submit:
    3. 2.3 Calendar Service Result:
    4. 2.4 Mail Service:



Date Calculations Tutorial:


Using The Date Calculation Tutorial as a starting point to Demonstrate the Calendar Service.


Live Google Calendar (Guest House Demo) 
to > Booking Form 
to > Date Calculations .

NOW...for the Calendar Service...

Adding the Calendar Service Apps Script the Result will be:

Live Google Calendar (Guest House Demo) 
to > Booking Form 
to > Date Calculations.
to > Add the Last Form Submission to Live Google Calendar (Guest House Demo)  

So the system can be completely automated , Authorization and form verification can remove any Overlaps, Conflicts or errors.

Calendar Service Example:

The Calendar Service in Google Apps Script allows you to send Dates and Information automatically to a Calendar.

You can also Create Calendars/Events, Modify and Add Users/Guests and much more...


Calendar Service Function:

So we use the following Function to Send the last submission to the Calendar.

function createCalendarEvent() {
  
  
  //Get the Active spreadsheet
  var sheet = SpreadsheetApp.getActiveSheet()
  
  //Get the (Last submissions) Arrival and Departure Dates
  var last = sheet.getDataRange().getLastRow() // Last Row Number
  var arrival = sheet.getRange("C"+last+":C"+last).getValues() //arrival - Last Cell Column C
  var departure = sheet.getRange("E"+last+":E"+last).getValues() //departure - Last Cell Column E
  var submitDate= sheet.getRange("A"+last+":A"+last).getValues() //Last Submission Date - Last Cell Column A 


  //Create the Calendar Event
  var myCal = CalendarApp.getDefaultCalendar();
  myCal.createEvent("Online Booking" +, new Date(arrival), new Date(departure));
  
  
  
//-----------------------------------------------------------------------------------------------------------  
   // Get the Last Row for Emailing (Uncomment 'Mailapp...' and add an valid email address)
   var value = sheet.getRange("A"+last+":E"+last).getValues()
   //MailApp.sendEmail("name@website.com", "Spreadsheet Information", "***Information sent from Spreadsheet via Apps Script ***" + value);

//-----------------------------------------------------------------------------------------------------------  
   //Message Box popup in spreadsheet
   //Browser.msgBox("Information" + value);
            
      }​



On Form Submit:

The Calendar Service will be triggered by the "On Form Submit" Event Handler.


Calendar Service Result:

  1. Submit 2 Dates for Calculation
  2. Press F5 to Refresh Date Calculations 
  3. Form Submissions added to Calendar as "Online Booking"


1. Submit 2 Dates for Calculation (F5 to Refresh "Online Booking"s)

"Guest House" - Form and Google Calendar

The gadget spec URL could not be found
2. Press F5 to Refresh Date Calculations 
The gadget spec URL could not be found
3. Form Submissions added to Calendar as "Online Booking" (Refresh F5)
Timezone Issues ??? Calendar Bugs ???





Mail Service:


By adding the following Mail Service code to the function you can also send the last submission to and Email Address.

// Get the Last Row for Emailing (Uncomment 'Mailapp...' and add an valid email address)
   var value = sheet.getRange("A"+last+":E"+last).getValues()
   MailApp.sendEmail("name@website.com", "Spreadsheet Information", "***Information sent from Spreadsheet via Apps Script ***" + value);


Uncomment  (Delete // ) in the Code line in the main "createCalendarEvent()"Calendar Service function.