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.
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
//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
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:
- Submit 2 Dates for Calculation
- Press F5 to Refresh Date Calculations
- Form Submissions added to Calendar as "Online Booking"
1. Submit 2 Dates for Calculation (F5 to Refresh "Online Booking"s)
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);var value = sheet.getRange("A"+last+":E"+last).getValues()
Uncomment (Delete // ) in the Code line in the main "createCalendarEvent()"Calendar Service function.