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

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

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('[email protected]');
  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);
    }
  }
}

CodePudding user response：

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 = '[email protected]'; // 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:

Events: get