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 Google Apps Script, 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):
Behind the scenes, everything is running on autopilot once I’ve clicked that first Run button.
To run the program, I added a custom menu option:
The code for this goes in your code.gs
script file, along with all the code for this example:
function onOpen() { var ui = SpreadsheetApp.getUi(); ui.createMenu("Auto Trigger") .addItem("Run","runAuto") .addToUi(); }
This runs the runAuto() function once, as shown by the yellow popup boxes in this GIF:
The workflow control function
The runAuto() function:
function runAuto() { // resets the loop counter if it's not 0 refreshUserProps(); // clear out the sheet clearData(); // create trigger to run program automatically createTrigger(); }
This does 3 things:
- from line 4, it runs refreshUserProps() which resets the counter to 0
- from line 7, it runs clearData() which clears any previous data in the column
- 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
function refreshUserProps() { var userProperties = PropertiesService.getUserProperties(); userProperties.setProperty('loopCounter', 0); }
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
function clearData() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName('Data'); // clear out the matches and output sheets var lastRow = sheet.getLastRow(); if (lastRow > 1) { sheet.getRange(2,1,lastRow-1,1).clearContent(); } }
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
function createTrigger() { // Trigger every 1 minute ScriptApp.newTrigger('addNumber') .timeBased() .everyMinutes(1) .create(); }
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…
Showing the detail:
So let’s ensure we’ve added a function to delete any triggers in this script:
Delete all triggers in the project
function deleteTrigger() { // Loop over all triggers and delete them var allTriggers = ScriptApp.getProjectTriggers(); for (var i = 0; i < allTriggers.length; i++) { ScriptApp.deleteTrigger(allTriggers[i]); } }
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 that will be run every minute by our trigger.
Before we look at the code, the salient points are:
- It gets run by the Trigger once every minute (or whatever you set your trigger to be).
- 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.
- It adds 1 to the loop counter and saves it back to the Property store, so that we know a loop has been finished.
- 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:
function addNumber() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName('Data'); // get the current loop counter var userProperties = PropertiesService.getUserProperties(); var loopCounter = Number(userProperties.getProperty('loopCounter')); // put some limit on the number of loops // could be based on a calculation or user input // using a static number in this example var limit = 3; // if loop counter < limit number, run the repeatable action if (loopCounter < limit) { // see what the counter value is at the start of the loop Logger.log(loopCounter); // do stuff var num = Math.ceil(Math.random()*100); sheet.getRange(sheet.getLastRow()+1,1).setValue(num); // increment the properties service counter for the loop loopCounter +=1; userProperties.setProperty('loopCounter', loopCounter); // see what the counter value is at the end of the loop Logger.log(loopCounter); } // if the loop counter is no longer smaller than the limit number // run this finishing code instead of the repeatable action block else { // Log message to confirm loop is finished sheet.getRange(sheet.getLastRow()+1,1).setValue("Finished"); Logger.log("Finished"); // delete trigger because we've reached the end of the loop // this will end the program deleteTrigger(); } }
So a quick rundown of what’s happening:
- 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).
- 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.
- 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).
- 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.
- In lines 26 and 27, I add 1 to the loop counter and save it back to the Properties store.
- 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. - Line 37 prints out “Finished” to the next blank row of my spreadsheet.
- Line 42 runs the function to delete the triggers, thereby ending the program.
And that’s it. The 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:
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.
Thank you very much! 🙂
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
Hey Richard,
I haven’t tried storing continuation tokens, so don’t have any direct experience to share. I’d be interested to see what you’ve created/what you end up with. You could try the apps script google+ community too: https://plus.google.com/u/0/communities/102471985047225101769
Thanks for sharing your curriculum mapping project. Sounds interesting so I’ll check it out when I get a second!
Cheers,
Ben
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.
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
Thank you so much!! I started using Apps Script today and had been looking for something like this for an hour before looking at this comment. Instantly solved my whole problem. I had assumed triggers were for events happening within Google Docs/other apps, not for weekly occurrences!
That’s great to hear! Keep up the good work. 🙂
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?
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
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,
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
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!
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');
}
}
}
}
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
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?
Thanks for this guide.
I’m trying to use getDirections() with this triggers to collect travel time data.
Hi,
Thank you
Can I create a trigger based on a new contact in G Suite?
Hi Ben!
Thanks a lot for the great and usefull information.
I need to set triggers to run from a time set from specific in the cell sheet
1. Create trigger according to change in the column
2. Run the trigger created
3. Then delete the specific trigger created ‘only’ for the specific cell
Thank you
Hello Ben,
I just wondering if it is possible to set a trigger that run a script every 15th & 30th day of the month?
Thank you in advance.
Michael,
I do not believe this is an option but can be accomplished in the function the trigger calls. I have a similar situation where I want to run every day except the 1st of the month. So I added a check in the function called by the trigger to just return if it is the 1st.
Regards,
John
Hi Ben – I am looking to create a script to enable a time-based trigger that after “x” second automatically displays the next sheet (tab)
Hi,
I want to know, is it possible to have a common Google sheet from where I can generate triggers for different sheets. I have a couple of sheets that I manage and would want to keep a control of their triggers from a central point.
Hi Rohit,
You can manage all your triggers from one place in the new Apps Script dashboard. More info: https://developers.google.com/apps-script/guides/dashboard
Cheers,
Ben
Hi I need a script to trigger a mail every Friday .But one condition if any Friday is holiday I need to send a mail on Thursday. Grateful for any advice
If I understand correctly, you still need to click the sub menu item to run the trigger, isn’t it?
Hi Bill, thanks for your blog, very helpful.
I’m trying to trigger programatically and automatically at specific time ever day. This is my code:
// Runs at approximately 18:30 in the timezone of the script
ScriptApp.newTrigger(“getData”)
.timeBased()
.atHour(18)
.nearMinute(30)
.everyDays(1)
.inTimezone(“America/Argentina/Buenos_Aires”)
.create();
As i see in googles’s guide, this .nearMinute specifies the minute at which the trigger runs (plus or minus 15 minutes). So is not really exact.
https://developers.google.com/apps-script/reference/script/clock-trigger-builder#nearMinute(Integer)
But appart from that, how is this triggered automatically without manual intervention? it doesn’t triggers alone…i have to put it inside a function which i have to trigger manually or use the buil-it triggers (that are no exact as they give you interval options)…redundant. I’m trying to avoid manual and intervals. So what i’m missing here?
Thanks.
I am looking for a script to autorefresh an API every minute.
Hey,
I’m trying to trigger my script daily and I’m wondering how to implement the functions you gave to the script I already have.
Could you help me see what I could do?
I’ve been trying to use the other trigger, but then it’s going to be connected to manual job such as me clicking on the saved function to run.
function createTrigger() {
// Trigger every day
ScriptApp.newTrigger(‘dailyLockRanges’)
.timeBased()
.atHour(18)
.everyDays(1)
.create();
}
/*This function finds the last row with content for some column */
function get_true_lastRow(sheet, col=’A’){
let col_A = sheet.getRange(`${col}1:${col}${sheet.getLastRow()}`).getValues().map( x=> x[0])
for(var i= col_A.length -1; i>=0; i–){
if(!col_A[col_A.length -1]){
col_A.pop()
}else{break}
}
return col_A.length
}
/*
Function to add the formulas to some data
*/
var addFormulas = function(csv, range){
let formulas = range.getFormulas()
csv = csv.map(function(row, i) {
return row.map(function(col, j) {
return formulas[i][j] || col;
}, formulas);
}, formulas);
return csv
}
/*
-> The function you want to run <-
*/
function dailyLockRanges(){
// Get the current spreadsheet
const ss = SpreadsheetApp.getActiveSpreadsheet()
// Name of the sheets
const FROM = "Sheet2"
const TO = "Copy of 1.2 AID (in stock)"
var $from = ss.getSheetByName(FROM)
var $to = ss.getSheetByName(TO)
// The cells with the data
const INTERNAL_NEWS = "I1"
const IN_STOCK = "J1"
const SHOTS = "H1"
// Get the numbers from the sheet
var withDate = $from.getRange(INTERNAL_NEWS).getValue()
var inStock = $from.getRange(IN_STOCK).getValue()
var shots = $from.getRange(SHOTS).getValue()
// Date of today
var today = new Date();
// Get the spreadsheet headers
var headers = $to.getRange(1, 1, 1, $to.getLastColumn()).getValues()[0]
// Get the last row with information in the A column + 1 (the next row)
var trueLastRow = get_true_lastRow($to)
var lastRow = $to.getRange(trueLastRow + 1, 1, 1, $to.getLastColumn())
// Place the values and formulas
var row = lastRow.getValues()[0]
row[headers.indexOf("Date")] = today
row[headers.indexOf("Backlog retouch")] = withDate
row[headers.indexOf("Backlog studio")] = inStock
row[headers.indexOf("Delayed products (total)")] = shots
var data = [row]
data = addFormulas(data, lastRow)
lastRow.setValues(data)
}
After adapting these and postings on other sites I ended up with a short trigger script that runs, in this example, every 5 minutes between two specific times (9:00 a.m. – 1:30 p.m.) Monday thru Friday of each week.
function startCustomTrigger()
{
ScriptApp.newTrigger(‘weekdayActions’).timeBased().everyMinutes(5).create();
}
function weekdayActions() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(‘Sheet1’);
var start = 09 * 60 + 00;
var end = 13 * 60 + 20;
var date = new Date();
var day = date.getDay();
var time = date.getHours() * 60 + date.getMinutes();
/* Monday thru Friday */
if ((day >= 1) && (day = start && time < end){
/* your code here */
}
}
}
/* deletes all triggers */
function deleteTriggers(){
// Deletes all triggers in the current project.
var triggers = ScriptApp.getProjectTriggers();
for (var i = 0; i < triggers.length; i++) {
ScriptApp.deleteTrigger(triggers[i]);
}
}