mysql - How to prevent Double Booking in Apps Script between Google Sheets and Calendar

Question

I am working on a spreadsheet that will help automate our meeting scheduled. I want to make sure my Team does not put meetings on the same date and time. How do I add this to my existing usable code? (I have not been able to find this answer ANYWHERE!!)

function addEvents(){
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lr = ss.getLastRow();
  var cal = CalendarApp.getCalendarById("[email protected]");

  var data = ss.getRange("A2:F"+ lr).getValues();

  for(var i = 0;i<data.length;i++){

    if(cal.getEvents(data[i][1], data[i][2])==null || cal.getEvents(data[i][1], data[i][2]).length==0 ){

      cal.createEvent(data[i][0], data[i][1], data[i][2],{guests: ''+data[i][3]+','+data[i][4]+'', description:data[i][5]});
}

} }

function onOpen() {
    var ui = SpreadsheetApp.getUi();
   ui.createMenu('Sync to Calendar')
        .addItem('Schedule events now', 'addEvents')
        .addToUi()

}

Thank YOU!

Answer 1

My answer is similar to what Cooper provided so I'll have this posted instead to help your sheet maintained properly.

I added a behaviour during onEdit() in which when a user added an existing set of start and end date in the sheet, the recently added/edited row is automatically deleted.

This can reduce the number of calls to calendar at the expense of constantly checking sheets during edits.

Feel free to use the code below

function onEdit(e) {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var currentSheet = SpreadsheetApp.getActiveSheet();
  var cell = currentSheet.getActiveCell();
  var cellValue = cell.getValue();
  var date, dates, occurences, limit;

  // newly added date in active sheet
  if (cell.getColumn() == 2) { 
    date = [cellValue, cell.offset(0, 1).getValue()];
  }
  else if (cell.getColumn() == 3) {    
    date = [cell.offset(0, -1).getValue(), cellValue];
  }

  // traverse other sheets
  sheets.forEach(function(sheet){
    var sheetName = sheet.getName();

    // dates from other sheets
    dates = sheet.getRange(2, 2, sheet.getLastRow(), 2).getValues();

    occurences = 0;

    // check if dates in other sheets have the newly added date
    for(var index in dates){
      if(JSON.stringify(dates[index]) === JSON.stringify(date)) {
        // increment if found
        occurences++;
      }
    }
    if(sheet.getName() != currentSheet.getName()) {
      // should not occur in other sheets
      limit = 0;
    }
    else {
      // should occur at most once in the active sheet
      limit = 1;
    }
    // if date was found more than the limit
    if (occurences > limit) {
      // prompt user that the newly added date is present in other sheets
      SpreadsheetApp.getUi().alert('WARNING!: Dates are already booked in ' + sheetName + '. Deleting newly added event.');

      // delete newly added date
      currentSheet.deleteRow(cell.getRow());
    }
  });
}