Prevent duplicate calendar events from Apps Script

Question

I've read through a number of posts on SO and elsewhere as to how to prevent a script from continuing to add duplicate calendar events each time it's run. I've been unsuccessful so far in stopping the duplicates.

Here's my code:

function onOpen() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [ {name: "Push to Calendar", functionName: "sendCalendarEvents"} ];
  ss.addMenu("Custom Menu", menuEntries);

}

function sendCalendarEvents() {
  var spreadsheet = SpreadsheetApp.getActiveSheet();
  var calendarId = spreadsheet.getRange('G1').getValue();
  var eventCal = CalendarApp.getCalendarById(calendarId);
  var lastRow = spreadsheet.getLastRow();
  var count = spreadsheet.getRange("A3:E"+lastRow+"").getValues();//five columns
  var minutesBefore = 462
  for (x=0; x<count.length; x++) {
      var row = count[x];
      var title = row[0];
      var startTime = row[1];
      var endTime = row[2];
      var guests = row[3];
      var description = row[4];
      var location = row[5];      
      var id = row[7];no row[7]

    var options = {
          'location': location,
          'description': description,
          'guests':guests +',',
          'sendInvites': 'True',
      }

   if(!id) {

   var event = eventCal.createAllDayEvent(title, startTime, options); 
    var newEventId = event.getId();
     spreadsheet.getRange(x+3,7).setValue('yes');
     spreadsheet.getRange(x+3,8).setValue(newEventId);

    event.addEmailReminder(minutesBefore);

     Logger.log('Event ID: ' + event.getId());

and here's my spreadsheet (with 'test' data)

I've also tried variations of the 'if' statement (like if (row[x][7] != 'yes')...create the event) but that hasn't worked either.

Any help? After the duplicate issue is resolved, I then want to be able to have a user edit the date or title or such of an event in the spreadsheet and have the existing event deleted and then a new event (with the updated title/date) be created...if that's possible.

Thanks for any help you can provide!

Every time you create an event in the calendar use one column as the flag to tell that this event has been created and read that column to make sure that it's empty before creating any further events. Of course one problem is that if events are deleted from the calendar then you have to have another function that reads your spreadsheet regularly to keep it in sync with your calendar. — Cooper
how did you create those EventId values @google.com in the H column in your test spreadsheet? Were they created by some generator or retrieved from the corresponding events within your calendar? And in this case, how did you do it? thanks — John Galassi

Cooper Cooper · Accepted Answer · 2020-06-15T20:05:44

You only have five columns in your data. There is no row[7]

 var count = spreadsheet.getRange("A3:E"+lastRow+"").getValues();//only five columns in your data
  var minutesBefore = 462
  for (x=0; x<count.length; x++) {
      var row = count[x];
      var title = row[0];
      var startTime = row[1];
      var endTime = row[2];
      var guests = row[3];
      var description = row[4];
      var location = row[5];      
      var id = row[7];//Problem is right here...there is no row[7];

Prevent duplicate calendar events from Apps Script

1 Answers