Using Google Forms and Google Calendar for a reservation system is handy. Having create the basic program last time, I want to start adding more features. First, and very much needed, is a bit of code that will prevent the script for scheduling two events at the same time.

This can be especially helpful if the calendar is posted publicly. After submitting the form, people can check and see if there event was scheduled. It will not be if there were conflicting events, after they notice, they can then go back and reschedule an event. Next feature I plan to add will be an email confirmation that goes out when an event is scheduled or if there is a conflict with the event.

 

[code]// Calendar Reservation Project
// Kurt Kaiser, 2018
// All rights reserved</pre>
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();
// Adjust time and make end time
this.date.setHours(this.time.getHours());
this.date.setMinutes(this.time.getMinutes());
this.endTime = new Date(this.date);
this.endTime.setHours(this.time.getHours() + 1);
return this
}

// Check for date conflicts
function getConflicts(request){
var conflicts = calendar.getEvents(request.date, request.endTime);
return conflicts.length;
}

// Creates a calendar event using the submitted data
function updateCalendar(request){
var event = calendar.createEvent(
request.name,
request.date,
endTime
)
}

// ————–Main————–
function main(){
var request = getSubmission();
if (getConflicts(request) > 1){
updateCalendar(request);
}
}

//[/code]