Form submission handler creates duplicate Google Calendar events

Question

I modified code from this question to suit my needs. I use Google Forms that fill into Google Sheets, and then the script inserts an event into Google Calendar. I have installed a "form submit" trigger to execute the script. The issue I have is that my code starts with the data in the first row of the sheet, adding the same events every time a new form response is added.

Here is my version:

//push new events to calendar
function pushToCalendar() {
  //spreadsheet variables
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow(); 
  var range = sheet.getRange(2,1,lastRow,16);
  var values = range.getValues(); 

  //calendar variables
  var defaultCalendar = CalendarApp.getDefaultCalendar()

  var numValues = 0;
  for (var i = 0; i < values.length; i++) {     
    //check to see if Start DateTime and End DateTime are filled out
    if ((values[i][3]) && (values[i][4])) {
      //check if it's been entered before  
      if (values[i][6] != 'y') {
        //create event https://developers.google.com/apps-script/class_calendarapp#createEvent
        var newEventTitle = values[i][1] + ' - ' + values[i][2];
        var startDay = Utilities.formatDate(new Date(values[i][3]), "GMT", "yyyy-MM-dd'T'HH:mm:ss'Z'");
        var endDay = Utilities.formatDate(new Date(values[i][4]), "GMT", "yyyy-MM-dd'T'HH:mm:ss'Z'");
        var newEvent = defaultCalendar.createEvent(newEventTitle, new Date(startDay), new Date(endDay), {location: values[i][2]});
        //get ID
        var newEventId = newEvent.getId();

        //mark as entered, enter ID
        sheet.getRange(i + 2, 6).setValue('y');
        sheet.getRange(i + 2, 7).setValue(newEventId);
      }  
    }

    numValues++;
  }
}

Answer 1

Found the solution by myself: on row 20 i changed the code

if (values[i][6] != 'y') { 

to

if (!values[i][6]) {

No need to write that 'y' sign. I also changed

sheet.getRange(i+2,6).setValue('y');

to sheet.getRange(i+2,6).setValue('');

And no duplicates anymore. The code works perfect!

Answer 2

The issue with the original code is that you are reading the wrong column - JavaScript arrays are 0-base, while the spreadsheet columns are 1-base. Column 6 (from getRange(i + 2, 6) is in the array index 5, i.e. values[i][5], not values[i][6]. So you were comparing the newEventId with the string 'y', which was never going to be the same.

As your solution indicates, removing the condition that values[i][6] (aka column 7, where your script writes the created event's ID) is not equal to 'y' (a condition that was always true), and instead testing for any value will appropriately guard the event creation code. Given the presence of the event ID column, the column in which 'y' was written is entirely unnecessary.

If you remove that column from your form response sheet, the code guard would then be:

//check to see if Start DateTime and End DateTime are filled out
if ((values[i][3]) && (values[i][4])) {
  //check if it's been entered before, by looking in Column F:
  var existingEventId = values[i][5]; // Will be "" (falsy) if not yet added
  if (!existingEventId) {
    ... 
    // Log the event's ID so we don't make a duplicate:
    sheet.getRange(i + 2, 6).setValue(newEventId);
  ...