Teacher Tech blog with Alice Keeler

Paperless Is Not a Pedagogy

Alice Keeler

Trigger An Action from Google Sheets

Automate tasks you do in Google Sheets, Docs, Slides, Forms, etc... Setting a trigger is the easy part!
Trigger An Action from Google Sheets
YouTube video

We all need things to happen automatically! It isn’t just teachers who wish we had a magic wand and that things would just be done for us. While I do not have a magic wand, triggers can help automate some actions. Google Sheets is one of the easiest ways to accomplish this since you can create Macros in Sheets (record your steps and it automates your code for you) and then turn on a trigger to make it happen on a schedule.

For example: I run detention every morning. I fill into a Google Form the students ID number. This goes from the Form to the Spreadsheet (that is a regular function of Google Forms). I have a vlookup formula to look up the students name. I use the Add-on Copy Down to automatically set the students name as part of the Form results in the spreadsheet. 
Then I have a trigger that automatically every day emails the entire school with the list of students who served in detention. 

So every day my full effort is simply typing in the student ID number. The rest automatically happens every day.

Want More Help with This? Become a Premium Member

Coding Google Docs, Sheets, Slides, Forms

You can code Google Docs, Sheets, Slides, Forms, etc… with Google Apps Script. This does require some knowledge in coding with JavaScript. EXCEPT Google Sheets. Google Sheets allows you to no code create a macro.

Create a Macro

Using Google Sheets, what steps do you do regularly that you would like to have happen automatically? Use the Tools menu and select “Record Macro.” In the video above I have given an example of recording a Macro. Ignore the example, the example is irrelevant. I am simply showing that I might want to do the same thing each day. Notice that as I insert a row and insert a formula that it is recorded in the Macro recorder.

Macros save your steps as Google Apps Script, but you did not have to code any of it! It is done for you automatically.

Script Editor

Docs, Sheets, Slides, Forms, and more have a Script Editor. In this script editor you can add Google Apps Script code. Note that while the video example shows Google Sheets, you can actually set a trigger in any of these products.

Google Apps Script from a macro in the script editor. Click on triggers icon, looks like a clock, on the left hand side.

Set a Trigger

In Docs, Sheets, Slides, Forms, etc.. go to the Script Editor. To set automation for your Google Apps Script functions (or Macros) look for the clock icon on the left hand side. This allows you to create a new trigger.

Double check the function when creating a new trigger. Change the event to Time Driven.

The first option is which function you want to automatically run. Double check that the function (or Macro) that you want to automate is what it is showing.

Select Event Source

Under “Select event source” choose “Time-driven.” In the case of a Google Sheets spreadsheet that came from a Google Form, you can select “On Form Submit” For Time-driven triggers, select how often you want the set of steps to run automatically. If once a day choose the “Day” option and select the hour that you want it to automatically run.

Save! That is it. You can have things happen automatically every day by using a Trigger.

© 2024 All Rights Reserved.