Time-Driven triggers not working properly

Question

We're working with Spreadsheets and Apps-Script to do some automation, therefore we've set up some time based triggers, some hourly, some daily based, all set up in the apps-script dashboard. It happens more and more often, that the triggers simply don't run. They're just not executed, so I don't see any failure in the dashboard (because the code is valid, though).

I thought, it was about the .getActiveSpreadSheet() method which might not work, when the sheet is not open or visible (which it would be in the most cases), but I recently have a sheet where I open it by id (openById), and the trigger still works just as he pleases.

Simple example code is:

function testTrigger(){
 var sheet = SpreadsheetApp.openById($sheetid).getSheetByName("ha")
 sheet.getRange("A1").setValue(new Date());
  Logger.log("test");
}

And before I used

function testTrigger(){
 var sheet = SpreadsheetApp.getActiveSpreadsheet.getSheetByName("ha")
 sheet.getRange("A1").setValue(new Date());
  Logger.log("test");
}

The trigger for this function is set to fire every minute. As I can see in the sheet, the last time it ran was 12:35:48, but it's now 12:40. So why isn't it working?

By the time I'm writing this, it suddenly fired at 12:40:48. But now it's 12:44 again, so there are already 3 runs missing! Why does this keep happening?

And: Would SpreadsheetApp.getActiveSpreadsheet.getSheetByName("ha") even work with a closed sheet? Has the sheet to be open AND active in the browser? We were using Spreadsheets and Apps-Script for months now, I used getActiveSpreadsheet.getSheetByName("ha") every time and it worked. It seems, the time driven triggers are buggy atm, I have no other conclusion (since the code and everything else is valid).

Is there any way to find out, why a trigger didn't fire (beside a code error, which I would of course see in the dashboard). And would these problems also occur, if I would use programmatical triggers?

Thanks in advance!

Here's an image of the problem (nothing changed in the code or in the trigger during these runs):

ziganotschka ziganotschka · Accepted Answer · 2019-12-04T12:51:14

To answer some your questions

As specified by the documentation for time-driven triggers "The time may be slightly randomized — for example, if you create a recurring 9 a.m. trigger, Apps Script chooses a time between 9 a.m. and 10 a.m"
If instead you create manually a ClockTriggerBuilder with the parameter nearMinute(minute), this "Specifies the minute at which the trigger runs (plus or minus 15 minutes)". In other words: You cannot expect from the Apps Script triggers to run precisely at the time you would like them to.
SpreadsheetApp.getActiveSpreadsheet() works if you have a bound script, rather than an alone-standing script. In any case, to avoid error sources, it is wise to use SpreadsheetApp.openById(id) instead of SpreadsheetApp.getActiveSpreadsheet()
You can supervise your triggers und executions and My triggers, however from your description it seems like the trigger is fired correctly, just not exactly at the time you would desire.

Time-Driven triggers not working properly

1 Answers