Calendar Reservation Project
My new project uses a data submitted to a Google Form to create events on a calendar. The Google Form asks for the person’s name, reason for the visit and allows them to select the day and time for their appointment. The code uses an on Form submission trigger, it takes the day and time of the requested appointment and adds it to a specified calendar. The person’s name is used as the title for the new event.
I will be posting a video tutorial for this project shortly.
[code]
// Calendar Reservation Project
// Kurt Kaiser, 2017
// All rights reserved
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var lastRow = sheet.getLastRow();
var lastColumn = sheet.getLastColumn();
// Calendar to output requests
var calendar = CalendarApp.getCalendarById(‘e6i9tfp012mt4m5ic81v9lisp0@group.calendar.google.com’);
// Creates an object from the last form submission
function getSubmission(){
this.timestamp = sheet.getRange(lastRow, 1).getValue();
this.name = sheet.getRange(lastRow, 2).getValue();
this.reason = sheet.getRange(lastRow, 3).getValue();
this.date = new Date(sheet.getRange(lastRow, 4).getValue());
this.time = sheet.getRange(lastRow, 5).getValue();
return this;
}
// Creates a calendar event using the submitted data
function updateCalendar(request){
request.date.setHours(request.time.getHours());
request.date.setMinutes(request.time.getMinutes());
var endTime = new Date(request.date);
endTime.setHours(endTime.getHours() + 1);
var event = calendar.createEvent(
request.name,
request.date,
endTime
)
}
// ————–Main————–
function main(){
var request = getSubmission();
updateCalendar(request);
}
[/code]
hi Kurt. txs for sharing.
is it possible to include a limit on the total number of bookings per day/time-slot?
I recently did a tutorial on a booking system that allows for a manager to approve of each appointment. That could be used to limit the number of bookings. Check out this post, https://techyesplease.com/education/google-calendar-reservation-approval-system/.
I am getting this error code: TypeError: Cannot call method “getSheets” of null. (line 6, file “Code”)Dismiss
How do I fix this?
Thanks!
What is the line before that? getSheets() is a method of the SpreadSheetApp object. You would need to have:
SpreadsheetApp.getSheets()
Or you would could assign SpreadsheetApp to a new variable, like I do, ss, and then call the getSheets() method on that variable, such as shown below.
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];