Google Apps Script – Check Availability in Google Sheets and Calendar

google sheetsgoogle-apps-scriptgoogle-calendar

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 = "-hidden-@group.calendar.google.com";
    //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)

Best Answer

The getEvents(startTime, endTime) method of the Calendar Class can be used. That method returns an array of events.

First you need to get a calendar and then chain the getEvents() method to the calendar.

var cal = CalendarApp.getDefaultCalendar();//Get a calendar
var array_Of_Events = cal.getEvents(startTime, endTime);

An array has a length property, which you can use to get the number of events that were found:

var number_Of_Events = array_Of_Events.length;//Get the number of events found

You can check the length with a conditional test:

if (theArray.length > 2) {
  //The code got to this line.  The length is 3 or more
  //Send an email to the user that the slots are already taken

  return;//Don't try to add a new calendar event - quit here
};

The documentation shows an example of how to check for the number of calendar events there are in a given time period.

Apps Script Documentation

function fncName() {
  var allEvents,startDt,endDt;

  startDt = sheet.getRange(lr,startDtId,1,1).getValue();
  endDt = sheet.getRange(lr,endDtId,1,1).getValue();

  allEvents = getEvents(startDt, endDt);//Returns an array of events

  if (allEvents.length > 2) {
    //The code got to this line.  The length is 3 or more
    //Send an email to the user that the slots are already taken

    return;//Don't try to add a new calendar event - quit here
  };
};
Related Topic