Google Apps Script is creating calendar entry twice

Question

I have mashed together a Google Apps script to create an event in the calendar based on values in my Google sheet. The script should work in the following conditions:

1. Whenever a new row is added (achived by a trigger - the 1st function)
2. Only apply to the last row on the spreadsheet
3. Only create an event if the "id" cell (28) is empty

It should then create the calendar event and populate cell 28 with the event ID.

The new rows are created when a client fills out a form on my website. I'm using Ninja Forms in WordPress that has a Google Sheets plugin. So the filled out form is automatically added to the sheet, then this function fires.

Everything is almost working. When I test manually (eg I delete the id cell in the last row or I create a new row or copy an existing row) it works perfectly. The event is created only if there is no event ID in cell 28 already, and it successfully fills out cell 28 with the ID. Great!

However, when the row is created using the WordPress form I get two calendar events. IE - it's like the function runs twice. Each event is identical and both are created at the same time.

I'm guessing this has something to do with how the form integrates with the sheet. It is somehow triggering my function twice. I have tried using Utilities.sleep at different points in the function with different values to see if maybe waiting between strps helps, but to no avail.

Can anyone think of a way I can stop this from occurring? Is there some kind of check I might be able to build into my function? Or have I missed something obvious? I would really appreciate any suggestions. Here is my code:

function initializeTrigger(){ // run once only to create the trigger
  var sheet = SpreadsheetApp.getActive();
 ScriptApp.newTrigger("NewCalEvent")
   .forSpreadsheet(sheet)
   .onChange()
   .create();
}

function NewCalEvent() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Bookings2');
  var row = sheet.getLastRow();
  var calId = "xxxyyy@group.calendar.google.com";
  var cal = CalendarApp.getCalendarById(calId);
  var date = sheet.getRange(row, 1).getValue();
  var title = sheet.getRange(row, 26).getValue();
  var tz = sheet.getRange(row, 23).getValue();
  var tstart = new Date(sheet.getRange(row, 32).getValue());
  var tstop = new Date(sheet.getRange(row, 33).getValue());
  var loc = sheet.getRange(row, 2).getValue();
  var desc = sheet.getRange(row, 27).getValue();
  var guests = sheet.getRange(row, 29).getValue();
  var id = sheet.getRange(row, 28).getValue();
if (id == ""){    
  var newEvent = cal.createEvent(title, tstart, tstop, {description:desc,location:loc,guests:guests,sendInvites:true}).getId();
  sheet.getRange(row, 28).setValue(newEvent)
  }
}

Answer 1

Possible Solution:

Add a check to see if the calendar event has already been created for this time.

More Information:

As it is not clear exactly where this trigger duplication is coming from, a way to circumvent the event being created twice is to search the calendar for the specified time frame for events which have the description you intend to enter, and then wrap the event creation code inside the check so it only runs if the event doesn't yet exist.

Code Snippet:

// ...
var id = sheet.getRange(row, 28).getValue();
var events = cal.getEvents(tstart, tstop, {search: desc})
      .map(function(e) {
        try {
          return e.getDescription();
        }
        catch {
          return "";
        }
      });

if (!events.includes(desc)) {
  if (id == ""){
    var newEvent = cal.createEvent(title, tstart, tstop,
      {description:desc,location:loc,guests:guests,sendInvites:true}).getId();
    sheet.getRange(row, 28).setValue(newEvent)
  }
}

This way, if the script is run twice, then as the event already exists the second time, it will not be created again.

NB: This checks for all Calendar event exists within the time frame tstart to tstop that have a description of desc. If you happen to have other events within this time frame with the same description, this script may not behave as expected.

References:

• Class CalendarApp - getEvents(startTime, endTime, options) | Apps Script | Google Developers