I'm using google form+calendar to create a reservation system for my makers lab. The user fills out the form and an event is created on the calendar so the TA knows when to goto the lab to help the user.
Currently I'm using this script(which i found online and modified it a bit to fit my usage)+trigger to create events based on the form submissions
var calendarId = "[email protected]";
//below are the column ids of that represents the values used in the spreadsheet (these are non zero indexed)
//Column containing the Start Date+Time for the event
var startDtId = 6;
//Column containg the End Date+Time for the event
var endDtId = 7;
//Column containing the First Part of the Title for the event (In this case, user ID)
var titleId = 4;
//Column containing the Second part of the Title for the event (In this case, user Name)
var titleId2 = 3;
//Column containing the user's mobile number
var descId = 5;
//Column containing the Time Stamp for the event (This will always be 1)
var formTimeStampId = 1;
//Column containing the machine Selected
var mId = 8;
function EnSubmitToCalendar() {
//Allow access to the Spreadsheet
var sheet = SpreadsheetApp.getActiveSheet();
var rows = sheet.getDataRange();
var numRows = rows.getNumRows();
var values = rows.getValues();
var lr = rows.getLastRow();
var startDt = sheet.getRange(lr,startDtId,1,1).getValue();
var endDt = sheet.getRange(lr,endDtId,1,1).getValue();
//Create an addition to the Description to included when
var subOn = "TimeStamp :"+sheet.getRange(lr,formTimeStampId,1,1).getValue();
//Setting the Comments as the description, and adding in the Time stamp
var desc = subOn;
//Create the Title
var title = sheet.getRange(lr,mId,1,1).getValue()+"-"+sheet.getRange(lr,titleId,1,1).getValue()+" "+sheet.getRange(lr,titleId2,1,1).getValue()+" "+sheet.getRange(lr,descId,1,1).getValue();
//Run the Crete event Function
createEventEN(calendarId,title,startDt,endDt,desc);
};
function createEventEN(calendarId,title,startDt,endDt,desc) {
var cal = CalendarApp.getCalendarById(calendarId);
var start = new Date(startDt);
var end = new Date(endDt);
//Set the Options, in this case we are only using Description and Location, as we do not need Guests or sendInvites
var event = cal.createEvent(title, start, end, {
description : desc,
});
};
it works well, and there's really no major problems to it, except when the user doesn't check availability
since we only have so many machines(3),if more than 3 users fill in the same time, the calendar will show more than 3 events, and it gets complicated.
is there a way to script it so the script checks to see if there's 3 events during that time, and if so, it doesn't create a event?
example:
event A: 13:30-15:30
event B: 13:30-14:30
event C: 14:00-15:30
User D submits a form that'll create event D: 14:00-15:30
but because 14:00-14:30 there's already 3 events, event D isn't created (or is adjusted by the script to start at 14:30 instead)
I'm thinking of using getEvents(startTime, endTime, options)
var checkAvail = CalendarApp.getDefaultCalendar().getEvents(startDtId, endDtId);
but I got no idea how to integrate this into my current script can someone give me some pointers/comments? am I even on the right track?
(sorry, I'm not at a level where I know how to write scripts, I only know how to modify existing scripts)