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

2. Press F5 to Refresh Date Calculations 
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.