CODEWITHSUNDEEP
javascripttriggersgoogle-apps-scriptgoogle-sheetsgoogle-calendar-api
AnPocArBuile
AnPocArBuile

Reputation: 665

onEdit trigger google scripts not working

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

}**

Upvotes: 1

Views: 2249

Answers (1)

Serge insas
Serge insas

Reputation: 46792

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.

Upvotes: 1

Related Questions