I have a container-bound script that reads data from a Google Sheet (its container), creates an event in a Google Calendar and then updates the Google Sheet with a confirmation to say that it has created a calendar event. The script is triggered to execute every time there is a change to the spreadsheet.

During testing, the script works perfectly as it should. However, when data is automatically entered into the Google Sheet from a Google Form, the script executes but does not update the Sheet with the confirmation of a new calendar event. This then results in the script creating duplicate calendar events because it does not see the confirmation in the Google Sheet.

Here is an excerpt of my Google Sheet data: Google Sheet data for employee leave details

And below is my script:

function synctocalendar() {
  var spreadsheet = SpreadsheetApp.getActiveSheet(); 
  var calendarId = spreadsheet.getRange("Calendar_sync!H2").getValue();
  var eventCal = CalendarApp.getCalendarById(calendarId);

  var submissions = spreadsheet.getRange("Calendar_sync!A2:F").getValues();

  var last = submissions.length-1 

  for (x=last; x>0; x--) {

    var shift = submissions[x];
    var startTime = shift[2];
    var endTime = shift[3];
    var title = shift[0] " | " shift[1];
    var ssr = x 2
    
    if (shift[4]!=="" && shift[4]!=="Complete" && shift[4]!=="Declined" && shift[4]!=="In progress") {
      break

    } else if (shift[4]=="Complete" && shift[5]!=="Y" && shift[5]!=="N") {
      eventCal.createEvent(title, startTime, endTime);
      var endf = spreadsheet.getRange(ssr,6)
      endf.setValue("Y")
      break
      
    }
  }
}

When I run the script directly from the editor it works perfectly. It reads the data, creates a calendar event, then returns a "Y" in column F (provided it satisfies the IF criteria).

When I have a trigger set to run the script whenever a change is made to the sheet, I can again get the script to run as I expect by manually deleting the "Y" from column F in any one of the rows.

However, when a new submission is written to the Google Sheet from a Google Form, the script still runs and still creates a calendar event, but does not write a "Y" back into the sheet.

CodePudding user response：

The script has several problems

1. Use of the wrong installable trigger. If the function should be triggered on a form submission use a on form submit trigger instead of an installable trigger. Tip: Using the of form submit trigger you could simplify the script a lot by taking advantage of the event object. Refs.
   • https://developers.google.com/apps-script/guides/triggers/installable
   • https://developers.google.com/apps-script/guides/triggers/events
2. The variables names are confussing. i.e.

   var spreadsheet = SpreadsheetApp.getActiveSheet(); 
   

   The variable name is spreadsheet but it points to a SpreasheetApp.Sheet object, not to an SpreadsheetApp.Spreadsheet object.
3. Some steaments are using a form of getRange that corresponds to SpreadsheetApp.Spreadsheet but are appliead to SpreasheetApp.Sheet
4. var submissions = spreadsheet.getRange("Calendar_sync!A2:F").getValues(); besides the problem mentioned in the previous comment, it's potentially problematic and inefficient as it reads all rows instead of reading only the rows holding data.
5. The script doesn't use logging to help debug the code.

CodePudding user response：

I think that the issue there is you're not calling any specific sheet when trying to write "Y". Try modifying the definition of the variable endf:

var endf = spreadsheet.getSheetbyName('Calendar_sync').getRange(ssr,6)