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):
Behind the scenes, everything is running on autopilot once I’ve clicked that first Run button.
Continue reading Build time-based triggers in Apps Script projects to automate programs
In the following image, I have two browser windows open. On the left, a Google Sheet containing a simple table of data. In the right browser window, a web page with a d3 chart that displays the data from the Google Sheet and can be refreshed dynamically when the data changes.
Continue reading Creating a d3 chart with data from Google Sheets
You’ve marked your students test scores and recorded it all in a beautiful Google Sheet.
You’ve dotted your i’s and crossed your t’s, checked your spelling and made sure all your scores are ready to go.
Now comes the tedious part, copying and pasting each student’s scores and feedback into an email or Slack message to send back to them.
Wait, there’s a better way!
With a little bit of upfront effort using Google Apps Script, you can build yourself a custom Google Sheets Slack/Email app that can automate that whole final step for you.
Besides being useful, it’s also pretty fun to post stuff from your Google Sheet direct to Slack!
Continue reading Save time with this custom Google Sheets, Slack & Email test scoring bot
What is Google Apps Script or G.A.S.?
It’s a cloud based scripting language for extending the functionality of Google Apps and building lightweight web-based applications.
What does this mean in practice: It’s a coding language where you can write small programs performing custom behaviors that go beyond the standard features of Google Apps. The code is stored and executed on Google’s servers.
It means you can do cool stuff like automating repetitive tasks, creating, modifying and emailing documents to people, and linking up your Google Sheets to other data sources. Heck, you can even build complex web forms, use a Google Sheet as your database, programatically create charts and publish it all to the web. In other words, you can build fully featured, lightweight web applications.
Learn Apps Script course
Apps Script Blastoff! is a FREE, introductory course teaching Google Apps Script from scratch. Get started here >>
Writing your first Google Apps Script
Continue reading Beginner guide to coding with Google Apps Script
In a different post, I looked at importing social media data into a Google Sheet. Since then, I’ve had a few readers contact me to ask if there’s a way to save the imported data, so that you have a record of the data at set time intervals (e.g. once a day or once a week).
The answer is: Absolutely!
It involves writing a short script but it’s pretty simple and we can even set up the whole shebang to run automatically in the background.
This functionality plays a big part in my online course when we save social media metrics for the digital marketing dashboard project. Check it out here:
Continue reading Saving data in Google Sheets with Google Apps Script