Change queue time of Google spreadsheet app script trigger

Question

When I create a daily time-based trigger for the Google app script associated with my Google spreadsheet, I am prompted to select an execution time that is within an hour-long window, and it appears that a cron wrapper randomly assigns an exact execution time within that hour-long interval.

Because my application's specific use case has several data dependencies which may not be completed early in the hour, I was forced to divide my application into several stages, with separate triggers each delayed by an hour, to insure that the required data would be available.

For example, the trigger time that was initially assigned for my script was 6:03AM, but the data which usually arrived at 5:57AM, occasionally did not arrive until 6:10AM and the script had nothing to process for that day. As a blunt force solution, I deleted the 6-7AM trigger and re-created it to execute in the 7-8AM time slot to insure the required data was available. This required that the second stage of the script had to be moved to 8-9AM, resulting in script results which could be delayed by as much as 2-3 hours.

To improve this situation, I am contemplating integrating the two script processing stages and creating a more accurate script execution trigger time, say 6:30AM to be safe. Does anyone know if:

Is it possible, other than by observing daily processing, to discover the exact trigger execution time that has been assigned, and
If randomly assigned, can script triggers be created and deleted until an acceptably precise execution time is obtained?

Thanks in advance for any guidance provided.

I believe the closest you can get is within (+/- 15 min). I would set the two triggers at 6:30 and 7, or combine them as you mentioned. If you really needed the to run the process before that 15 minute window, you could have a trigger at 6am create a minute trigger to check if the data arrived and if it did run your processes then have the minute trigger self-delete. — random-parts

TheAddonDepot TheAddonDepot · Accepted Answer · 2017-09-03T18:49:13

If accuracy is paramount, you can forgo using apps script triggers altogether and leverage a 3rd party tool instead.

I'd recommend using cron-job.org. This service can create cron jobs that make POST requests to a url endpoint you specify, and you can schedule times accurate to a minute. To use it with Apps Script implement a doPost() to handle post requests and deploy your script as a Web APP. You then create a cron job using the service and pass it the web app's URL as an endpoint.

The cron job will fire at the scheduled time and you can perform any requisite operations inside the doPost() in response to the incoming POST request.

Change queue time of Google spreadsheet app script trigger

2 Answers