Build time-based triggers in Apps Script projects to automate programs

Ever find yourself needing to repeat an action in your Google Sheets?

For example, on a recent client project I wanted to cycle through my spreadsheet data, compare it to another dataset and apply a matching algorithm. To do this and stay within the 6 minute execution limit for Apps Script, I split my data into “blocks” of 10 rows, counted how many blocks I had and then ran the matching algorithm once for each block until they’d all been processed.

By adding time-based triggers, I setup my Google sheet to do this automatically, rather than having me click a button each time.

Using Apps Script (read my getting started guide here), you can add these time-based triggers programmatically, so they can run and stop automatically.

In this post I’m going to show a skeleton example of this time-based architecture, to which you can add your own functionality.

The entire code file is here on GitHub and the spreadsheet is available here (feel free to make a copy: File > Make a copy… and then you can open the script editor to modify the code).

Time-based triggers with Google Apps Script

In this simple example, I’m going to output a random number in a new line of my spreadsheet every minute until I’ve looped through a set number of times (2 in this case). It’s deliberately basic as the focus is on the trigger/timing architecture.

Here’s a screencast of this simple program in action (speeded up):

Auto trigger loop

Behind the scenes, everything is running on autopilot once I’ve clicked that first Run button.

Adding the custom menu

To run the program, I added a custom menu option:

Custom menu

The code for this goes in your script file, along with all the code for this example:

This runs the runAuto() function once, as shown by the yellow popup boxes in this GIF:

Run function in apps script

The workflow control function

The runAuto() function:

This does 3 things:

  1. from line 4, it runs refreshUserProps() which resets the counter to 0
  2. from line 7, it runs clearData() which clears any previous data in the column
  3. from line 10, it creates the time-based trigger by running the function createTrigger().

The time-based trigger will then run my process function a set number of times until some condition is satisfied.

Let’s look at each of these three steps in turn.

Saving the counter variable with the Properties Service

Here I set a variable called loopCounter to 0 and store that in my Properties Service, which allows my script to store key-value pairs for future reference.

Clear any data in my spreadsheet

Nothing too complicated going on here. I find the number of the last row in my spreadsheet that contains data and then clear out everything in the first column except the header in row 1.

This is fine for my example because my process function simply writes a number into this column. If your output involves other columns then you’d want to modify this function to clear out those as well.

Create a time-based trigger to automatically run the process function

This function creates a trigger programmatically that will run the process function every minute, ad infinitum unless we stop it, so we’ll need to delete the trigger (see below).

Word of caution: be careful when you’re setting up auto-triggers initially, lest you forget or fail to delete your trigger and you to hit Google’s trigger quotas. I woke up to 38 emails from Google notifying me that my script was failing repeatedly…

Apps Script error

Showing the detail:

Apps script error detail

So let’s ensure we’ve added a function to delete any triggers in this script:

Delete all triggers in the project

This simple function gets all the triggers associated with this project, loops over them and deletes each one in turn.

The action function to run repeatedly

The final piece of the puzzle is the addNumber() function which is the engine of our system. This is the script which will be run every minute by our trigger.

Before we look at the code, the salient points are:

  1. It gets run by the Trigger once every minute (or whatever you set your trigger to be).
  2. It retrieves the loop number from the Property store, and if it is less than our limit performs the action that we want to repeat.
  3. It adds 1 to the loop counter and saves it back to the Property store, so that we know a loop has been finished.
  4. Once our loop counter variable is larger than our limit number, we execute a different part of the script that deletes the triggers, thereby stopping the program.

Here’s the code:

So a quick rundown of what’s happening:

  1. On lines 7 and 8 we retrieve the current value of the loop counter and convert it to a number (it’s stored as a text string in Properties).
  2. Line 13 contains the limit for the number of times you want to repeat the action, i.e. the number of loops. This could be something you calculate, for example, based on the size of your dataset or from a user input message box.
  3. On line 16 I check if the loop counter is less than this limit, and if it is proceed with the action block (points 4 and 5 below).
  4. Lines 22 and 23 are the code that is the action I want to repeat, in this case simply adding a random number to my sheet on the next blank row. This is where you can call another function that does something more complex.
  5. In lines 26 and 27, I add 1 to the loop counter and save it back to the Properties store.
  6. If the loop counter is no longer less than the limit, then I’ve repeated my action the correct number of times and I enter the else block of code, line 35.
  7. Line 37 prints out “Finished” to the next blank row of my spreadsheet.
  8. Line 42 runs the function to delete the triggers, thereby ending the program.

And that’s it. First time you run, you’ll be asked for permission in the usual way, but then it should work as normal.

Here’s a sped up GIF showing the looping:

Auto trigger loop

In real life, there’s a minute between each of the loops.

Again, here’s the full code here on GitHub and the spreadsheet here (feel free to make a copy: File > Make a copy… and then you can open the script editor to modify the code).

Let me know what exciting projects you implement Apps Script triggers in.

Leave a Reply

Your email address will not be published. Required fields are marked *