CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-calendar-api
KMB
KMB

Reputation: 3

Prevent duplicate calendar events from Apps Script

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)

enter image description here

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!

Upvotes: 0

Views: 1626

Answers (1)

Cooper
Cooper

Reputation: 64062

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];

Upvotes: 2

Related Questions