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 code.gs 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.

13 thoughts on “Build time-based triggers in Apps Script projects to automate programs”

  1. Hi Ben, I’ve been playing with this framework and was wondering if you’ve been successful with using and storing continuation tokens in the script properties. I’m trying to batch list files from a folder in a Google Sheet, then resume where I left off in order to avoid exceeding the execution limit. I can do this when I interact with a sidebar, but I haven’t been able programmatically schedule a re-listing of files each night with consistent results. Happy to share with you what I’ve crafted/modified so far. You might also be interested in a Curriculum Mapping Project with G Suite project that is gaining traction in schools. Cheers! https://plus.google.com/u/0/communities/116182501278550215014

  2. Hi,

    This might be a stupid question but i’v been looking around for a while now for an answer and i can’t seeem to find quite the answer. So here’s the deal. I’m starting to look into google script for a small Sheet that i use for a game buisness(not a real one) and what i want to do is pretty simple, i think, but can’t find how to do. I want to, every week, take a “snapshot” of some cell to keep the result on week production into control. Basically it’s a cab society in a game, everyone put how many run they do and the sheet actually keep an idea of how much money we’ve got and how much we’ve spent.

    However, i can’t find any way to have a Weekly trigger that would then start, make a copy of those information, maybe clean up the drivers sheet(why not) then restart the whole stuff for a new week withouf much of human interaction.

    Now, the first question would be… is it possible? The second, what is the trigger that i must use to run every week. pretty sure i could find every other answer but this one i seem unable to find myself. So if you can help me i’d me pleased.

    thanks you anyway for your work.

    1. Hi,

      You can set a weekly trigger in the script editor window to run a particular function (to save data) on a weekly basis:

      Cheers,
      Ben

  3. Ben, I’m interested in creating a time-based trigger that will run a specific script at a specific time on Thursday every 14 days. (Trigger soon to be changed to run script every Friday morning at 01:00.)

    Any ideas as to how I can do this?

    1. Hi Bud,

      Seems you have two options:

      1) Use two month timers, one at the start, one half way through the month, but they won’t always be a thursday:

      2) Run a weekly timer on a thursday, but have a flag to record every other week. You could set it to true initially and then flip it to false, back to true, back to false, every week the function runs. You’d need to store the result in the Properties service each week and then run the body of your function if it’s true, ignore it if it’s false.

      Hope that helps!

      Ben

  4. Hi Ben, I am also interested in learning how to adapt this script to trigger at a specific time each day, the built-in Google triggers only give you a “between 12:00 and 13:00” type option.

    Cheers,

    1. Hi Frank,

      You’re correct, the out the box triggers only run in hour slots.

      Perhaps you could try do something like this: have the trigger fire every minute and then have an IF conditional in your code that only executes the body of your function when the current hour and the minute match the a hard-coded time in your script file you want to run at.

      Not particularly pretty and obviously lot of wasted calls!

      Ben

  5. Hi Ben,

    This is great! I’ve been looking for a script like this for half a day.

    I am trying to create a timed trigger to paste (=HTTPResponse(C2)) in column D, for the first 50 rows, wait a minute then in the next 50 rows then next 50 rows, etc. I could have up to 1000 rows. The reason is if I ran all the rows at once it would lock me out of Google.

    What would I need to put in the do stuff area?

    Thanks!

  6. Hi Ben,
    I have the following script that is to Conditionally Format Borders if there is contents within the cell. Currently, it is only able to set to the minimum trigger interval of 1 min for this script to be run.

    However, I would like to set this script to be triggered whenever there is any updates made to the spreadsheet. May I check if there is a way to improvise this script to run whenever there is any updates? Does this involve using this function “onEdit(e)”? Can you guide me on what is to be revised for the script to enable this?

    // custom menu function
    function onOpen() {
    var ui = SpreadsheetApp.getUi();
    ui.createMenu(‘Custom Menu’)
    .addItem(‘Format Daily DMS email template’,’checkRange’)
    .addToUi();
    }

    function checkRange() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var range = ss.getSheetByName(“Daily DMS Email template”).getRange(‘C6:J50’);
    range.setBorder(false, false, false, false, false, false);
    var values = range.getValues();
    for (var i = 0; i < values.length; i++) {
    for (var j = 0; j < values[i].length; j++) {
    if (values[i][j] !== "") {
    range.getCell(i + 1, j + 1).setBorder(true, true, true, true, true, true)
    .setBackground('white');
    }
    }
    }
    }

  7. We use a new Google Sheets Actions Add-on from Factivate that simplified all of these scripts for us. We’re able to set up automated emails, sms, export to pdf, take screenshots of my spreadsheets, and more. It’s super easy and very WYSIWYG

  8. Hi Ben,

    Good tutorial! Do the trigger functions keep getting triggered if the spreadsheet itself is closed (i.e. I don’t have it open in a browser). If so, is the script able to get the correct sheet in the code “SpreadsheetApp.getActiveSpreadsheet()” so the spreadsheet context is maintained even with the sheet closed?

Leave a Reply

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