App script time trigger not executing my script at all

Question

I have two functions that are responsible for starting and stopping the execution of my script from UI.

function runScript() {
  stopScript();
  ScriptApp.newTrigger("start").timeBased().everyHours(1).create();
}

and

function stopScript() {
  let triggers = ScriptApp.getProjectTriggers();

  for (var i = 0; i < triggers.length; i++) {
    ScriptApp.deleteTrigger(triggers[i]);
  }
}

They both are triggered by the UI for which I'm using this piece of code:

function onOpen(e) {
  let sheetUi = SpreadsheetApp.getUi();

  sheetUi.createMenu('Menu')
  .addItem('Run', 'runScript')
  .addItem('Stop', 'stopScript')
  .addToUi();
}

They work as expected: stopScript() remove the old time trigger and runScript() create a new one.

Once I run the function runScript() a new time trigger is created and I can see it in my dashboard. However, the time trigger never executes the script. For example, for the past 2 days, the time trigger which was set to execute the script every hour didn't execute it even once. I don't understand why this is happening.

The script can be used from any Google Sheet. I tried any solution I found on the internet and here but it keeps not running. I'm out of options.

Am I missing something?

Your trigger is expected to execute every hour a function called function start(). Do you have such a function and if yes - what is its content? — ziganotschka
Yes, I have a function start() which is working correctly. Unfortunately, I can't share many details due to NDA. I should probably mention that I don't receive any errors either. Edit: start is my entry function, but I don't think that's a problem. The time trigger doesn't fire the function at all — Dimitar Dzhurenov
If yo go to https://script.google.com/home/triggers, select the trigger of interest and click on the three vertical dots on its right side - you can see "Executions" and "Failed Executions". If you click on "Failed Executions", you can see the list of all failed executions and clicking on the word "Failed" will show you the related "Stackdriver logs" containing the error message / exception. — ziganotschka
I already check that several times and it's always empty. There aren't any errors. The time trigger is created and it should at least run at the appropriate time but it doesn't. Both parameters Last run & Error rate stays empty for my trigger at https://script.google.com/home/triggers for the past 2 days — Dimitar Dzhurenov
So before your trigger executed, but now not anymore? And you have not modified anything since then? What about sharing settings? Who owns the spreadsheet, script and trigger? Sounds like a permission issue. If you can provide a dummy sheet and after sensitive information - I can have a look. Otherwise provide at least the contents of the function start() (after removing sensitive information). — ziganotschka

Dimitar Dzhurenov Dimitar Dzhurenov · Accepted Answer · 2020-04-06T09:30:20

After 2 weeks in testing and reaching even to G Suite Support (who didn't manage to help either), I found the problem.

Every part of my script is correct and it's working. The problem comes when you need to test a time-trigger or even an installable trigger in general for your add-on (Test as add-on option from the script editor) - it's impossible and it will never work because that's the way Google created it. From Test an editor add-on:

Installable triggers aren't supported when testing. Functionality that depends on installable triggers is not testable.

So how you should test your time-triggers then?

In my case when I was testing the script through the editor the time-trigger was created and it was running periodically (every 1h). And that's the answer. If your time-trigger (or installable trigger in general) is running that way it means that it's working and it will work on a production as well.

App script time trigger not executing my script at all

1 Answers