onEdit trigger google scripts not working

Question

So I have a Google spreadsheet which is constantly being updated by a script called "writeToSheet" (this is working fine). This writes a new row at the end of the spreadsheet, the last column of which is a date.

I also have a second script with a function called "writeToCal" which takes some of the data from this new row in the spreadsheet and creates a Google calender event on the date given by the column mentioned above.

The problem arises when I try to trigger the "writeToCal" so that "writeToCal" is executed whenever the spreadsheet is updated. I have tried simple triggers and installable triggers however neither seem to work for me. (https://developers.google.com/apps-script/guides/triggers/installable)

The code works (i.e. it creates the calender event) when I run it from script manager in the Google spreadsheet, but I want it to run automatically every time the spreadsheet is updated. Since the script works fine when I run it I believe it is a matter of the triggering, but I cannot find out where the issue lies. Any help would be much appreciated

My code is as follows, this script is bound to the spreadsheet in question:

    **function triggerWhyDoYouCallMeDave() {
  var ss = SpreadsheetApp.openById("BlahBlah");
  ScriptApp.newTrigger('writeToCal')
  .forSpreadsheet(ss)
  .onEdit()
  .create();
}

function writeToCal(e) {    
  var ss= SpreadsheetApp.openById("BlahBlah");
  var tt = ss.setActiveSheet(ss.getSheets()[0]);

  var lastRow = tt.getLastRow();
  var lastColumn = tt.getLastColumn();

  //Title is from the User and the follow up message
  var userCell = tt.getRange(lastRow, 2);
  var usr = userCell.getValue();

  var followUpCell = tt.getRange(lastRow, 8);
  var follUp =  followUpCell.getValue();

 var target = tt.getRange(lastRow, 6);
  var targetName = target.getValue();

  var title = (usr+": "+follUp+" with "+targetName);

  //Get the date from the follow up date.
  var dateCell = tt.getRange(lastRow, 9);
  var follUpDate = new Date(dateCell.getValue());

  var calendar =      CalendarApp.getCalendarById('BlahBlah');
   calendar.createAllDayEvent(title, follUpDate);

}**

Are you editing your sheet manually or only using the other script you mentioned? — Serge insas

Serge insas Serge insas · Accepted Answer · 2014-09-16T15:46:53

You don't need to use an onEdit trigger if the sheet is only modified using another script, you can simply call your writeToCal() function directly from the other function, add a SpredsheetApp.flush() before calling it so that you are sure all modifications are duly recorded.

onEdit trigger google scripts not working

1 Answers