Delete Events from Google Calendar based on Google Sheets using Google Apps Script

user_17020205 0
Viewed 990

Absolute noob here !

Background:

  • am trying to create a Google Sheet which I can update for a series of events and
  • create Google Calendar events based on those entries
  • so far, am successful in creating calendar events and also updating back the last column of the sheet with the EventID (iCalUID) - thanks to other stackoverflow posts
  • am also successful in not creating Duplicates by checking if the EventID (iCalUID) is already present in the last column - thanks again to other stackoverflow posts

But... have another requirement, where am failing:

  • need to mark an existing event as 'Cancelled' in one of the columns in the sheet and
  • if this is 'true' then look-up the EventID (iCalUID) from the corresponding last cell (of that row which has a 'Cancelled' entry) and
  • delete that particular event from the calendar
  • also, calendar events should NOT be created again as long as that cell remains/retains the word 'Cancelled'.
  • the "var check1 = row[23]; //Booked/Blocked/Cancelled" in below script was just added to bring in this logic that I wanted, but am kind of unable to proceed

Relevant screen-shot of the sheet

Code that I used so far as below:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Sync to Calendar')
  .addItem('Sync Now', 'sync')
  .addToUi();
}

function sync() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
  var calendar = CalendarApp.getCalendarById('myemailid@gmail.com');
  var startRow = 2;  // First row from which data should process > 2 exempts my header row
  var numRows = sheet.getLastRow();   // Number of rows to process
  var numColumns = sheet.getLastColumn();
  var dataRange = sheet.getRange(startRow, 1, numRows-1, numColumns);
  var data = dataRange.getValues();

  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var name = row[1];  //Name of Guest
    var place = row[4];  //Add2
    var room = row[9]; //Room Number    
    var inDate = new Date(row[10]);  //Check-In Date
    var outDate = new Date(row[11]); //Check-Out Date
    var check1 = row[23];  //Booked/Blocked/Cancelled
    var check2 = row[24]; //Event created and EventID (iCalUID) populated 
    
   if (check2 == "") {
      var currentCell = sheet.getRange(startRow + i, numColumns);
      var event = calendar.createEvent(room, inDate, outDate, {
      description: 'Booked by: ' + name + ' / ' + place + '\nFrom: ' + inDate + '\nTo: ' + outDate
      });
      var eventId = event.getId();
      currentCell.setValue(eventId);
    }
  }
}
google-calendar-api google-apps-script google-sheets-api google-sheets
1 Answers

I believe your goal is as follows.

  • You want to check the columns "X" and "Y".
  • When the column "X" is not Cancelled and the column "Y" is empty, you want to create a new event.
  • When the column "X" is Cancelled and the column "Y" is not empty, you want to delete the existing event.
  • When the column "X" is Cancelled, you don't want to create a new event.

In this case, how about the following modification?

Modified script:

In this script, in order to check whether the event has already been deleted, Calendar API is used. So please enable Calendar API at Advanced Google services.

function sync() {
  var calendarId = 'myemailid@gmail.com'; // Please set your calendar ID.

  var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
  var calendar = CalendarApp.getCalendarById(calendarId);
  var startRow = 2;  // First row from which data should process > 2 exempts my header row
  var numRows = sheet.getLastRow();   // Number of rows to process
  var numColumns = sheet.getLastColumn();
  var dataRange = sheet.getRange(startRow, 1, numRows - 1, numColumns);
  var data = dataRange.getValues();
  var done = "Done";  // It seems that this is not used.
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var name = row[1];  //Name of Guest
    var place = row[4];  //Add2
    var room = row[9]; //Room Number    
    var inDate = new Date(row[10]);  //Check-In Date
    var outDate = new Date(row[11]); //Check-Out Date
    var check1 = row[23];  //Booked/Blocked/Cancelled
    var check2 = row[24]; //Event created and EventID (iCalUID) populated 
    
    // I modified below script.
    if (check1 != "Cancelled" && check2 == "") {
      var currentCell = sheet.getRange(startRow + i, numColumns);
      var event = calendar.createEvent(room, inDate, outDate, {
        description: 'Booked by: ' + name + ' / ' + place + '\nFrom: ' + inDate + '\nTo: ' + outDate
      });
      var eventId = event.getId();
      currentCell.setValue(eventId);
    } else if (check1 == "Cancelled" && check2 != "") {
      var status = Calendar.Events.get(calendarId, check2.split("@")[0]).status;
      if (status != "cancelled") {
        calendar.getEventById(check2).deleteEvent();
      }
    }
  }
}

Reference:

user_71086530
Related