Triggers in Google Sheets

Triggers are a feature in Google Apps Script and they enable you to automate your tasks and workflows in Google Sheets.

For example, if you want a metrics report emailed to your boss every morning, you can use a Time-driven trigger along with some Apps Script code to automate sending this report.

In this article, you'll learn more about triggers, how they work and how to create them.

How do triggers in Google Sheets work?

The best way to understand how triggers work is to look at an example.

Suppose you're like me and you tend to forget the birthdays of your friends and relatives. You make a new year resolution to remember birthdays and you decide to use your spreadsheet skills to help you keep your resolution.

You enter all the birthdays you want to remember into a spreadsheet and then use a trigger to check it every morning at 6AM and send you an email if it is someone's birthday.

From a technical standpoint what is happening is that the trigger causes a Google Apps Script function to run every morning and this function reads the birthdays from the spreadsheet and sends you an email if that day happens to be someone's birthday.

A diagram showing a trigger that invokes the isBirthdayToday function which in turn calls the sendEmailReminder function if it is someone'birthday on that day.

Types of triggers in Google Sheets

There are two types of triggers in Google Sheets. They are: Time-driven triggers and Spreadsheet triggers.

Time-driven triggers

Use Time-driven triggers to run a function in your script periodically OR at a specific date and time in the future. Here are some examples of use cases where you might use a Time-based trigger:

  • Automatically sending a report to your team every morning.

  • Sending yourself an email every month to remind yourself of your goals or resolutions.

There are six types of Time-based triggers

Specific date and time

Use this option to run a function in your script at a specific date and time in the future. You must specify the date and time in the YYYY-MM-DD HH:MM format and in the GMT-07:00 timezone (which is the Pacific Daylight Time or PDT). Also, the hours and minutes must be specified using the 24-hour clock.

Minutes timer

Use this option to run a function in your script every minute or once every N minutes (N can be 5, 10, 15 or 30).

Hour timer

Use this option to run a function in your script every hour or once every N hours (N can be 2, 4, 6, 8 or 12).

Day timer

Use this option to run a function in your script daily at a specific time interval. You can't choose the exact time but you can select time intervals that are an hour long each. Google will then randomly pick a time within this interval and the function will be run daily at this time. So if you pick "5am to 6am" as the time interval, Google might pick 5:23am as the time when the function will be run every day.

Week timer

Use this option to select the day of the week and the time interval on that day when a function in your script should be run. For example, you can make it run every Monday between 8am and 9am.

Month timer

Use this option to select the day of the month and the time interval on that day when a function in your script should be run. For example, you can make it run on the 5th of every month between midnight and 1am.

Spreadsheet triggers

Use Spreadsheet triggers to run a function in your script whenever something changes in your spreadsheet. Here are some examples of use cases where you might use a Spreadsheet trigger:

  • Automatically sending you an email whenever a new row is added to your spreadsheet.

  • Automatically sending you an email when a cell's value meets a condition.

  • Automatically validating the contents of a spreadsheet when its structure is modified (i.e., when a row or column is added or deleted).

  • Creating a menu in the spreadsheet when it is opened.

  • Sending yourself an email whenever a form is submitted.

Simple triggers and Installable triggers

There are two kinds of Spreadsheet triggers: Simple triggers and Installable triggers.

Simple triggers do not need user authorization but installable triggers must be authorized (i.e., installed) by the user.

Since simple triggers are run without user authorization, Google restricts what scripts can do when they are run by these triggers. Some of these restrictions are:

  • Scripts run by a Simple trigger cannot access any user data.

  • They cannot perform any action that requires user authorization. For example, they cannot make a request to an external URL or send emails on behalf of the user.

  • They will only run if the spreadsheet is opened in "Edit" mode. They won't work if the spreadsheet is opened in a View-only or Comment-only mode.

Installable triggers can access user data but they must be authorized by the user.

There are four types of events that you can use in a Spreadsheet trigger

Trigger event Can it be used as a simple trigger? Can it be used as an installable trigger?
Open Yes Yes
Edit Yes Yes
Change No Yes
Form Submit No Yes
Open

Use an open trigger to run a function in your script when the spreadsheet is opened. While Google Sheets supports an installable version of the Open trigger, the simple version is more commonly used.

A common use case for Open triggers is adding a custom menu in a Google Sheets spreadsheet.

A screenshot of a Google Sheets spreadsheet with a custom menu called "Admin Settings".  The menu is open and has three menu items: Import data, Create charts and Email report.
Edit

Use an Edit trigger to run a function in your script whenever one or more cells in your spreadsheet are edited.

Depending on your use case, you must pick either a Simple Edit trigger or an Installable one. Here are a few guidelines to help you pick the right type for your use case:

  • Use a Simple Edit trigger if your script will not access user data AND will not be taking any external actions (such as sending emails or accessing URLs) on behalf of the user.

  • Example use case: Performing some custom validation after every edit and alerting the user via an Alert dialog if validation fails.

    A screenshot of a alert dialog. It tells you that a value you entered in Column E is not present in Column B.

  • Use an Installable Edit trigger if your script needs access to user data OR will be taking some external action such as sending emails.

  • Example use case: Sending you an email whenever the value in Column J changes from "Pending" to "Approved".

Change

Use a Change trigger to run a function in your script whenever the structure of your spreadsheet changes. Here are some examples of structural changes that will result in this trigger being fired:

  • INSERT_ROW: A new row is added to the spreadsheet.

  • INSERT_COLUMN: A new column is added to the spreadsheet.

  • REMOVE_ROW: A row is removed from the spreadsheet.

  • REMOVE_COLUMN: A column is removed from the spreadsheet.

  • INSERT_GRID: A new sheet is added to the spreadsheet.

  • REMOVE_GRID: A sheet is deleted from the spreadsheet.

  • FORMAT: A range's formatting is changed.

  • EDIT: A range in the spreadsheet is edited.

  • OTHER: Some other change is made such as:

  • Protecting a sheet or a range within a sheet..

  • Hiding a sheet or making a previously hidden sheet visible.

  • Sorting the data in a sheet.

  • etc.

Here is an example use case where a Change trigger can be used: Suppose your team maintains an important financial model in a spreadsheet. You want to be notified whenever any structural changes are made to that spreadsheet so you can validate that the model still works correctly. You can set up a Change trigger to run an Apps Script function whenever structural changes are made to the spreadsheet and this function will then send you an email notification.

Form submit

Use a Form submit trigger to run a function in your script whenever a user submits a Form that is linked to your spreadsheet.

A very common use case for a Form submit trigger is sending yourself or your team an email with the contents of the Form submission.

How to create a trigger?

A simple trigger can only be created using code whereas an installable trigger can be created using code or via a UI.

Creating a simple trigger (Open or Edit)

To create a simple trigger, just create a function called onOpen or onEdit (depending on the type of trigger you want) in your script. The onOpen function will be automatically run whenever your spreadsheet is opened and the onEdit function will be automatically run whenever your spreadsheet is edited.

Here is an example of how to create a Simple Open trigger that creates a custom menu called "Admin Settings" whenever the spreadsheet is opened.

A screenshot of a Google Sheets spreadsheet with a custom menu called "Admin Settings".  The menu is open and has three menu items: Import data, Create charts and Email report.
function onOpen() {
  SpreadsheetApp.getUi().createMenu("Admin Settings")
  .addItem("Import Data", "importData")
  .addItem("Create Charts", "createCharts")
  .addItem("Email Report ", "emailReport")
  .addToUi();
}

//A function that imports data into the spreadsheet.
function importData () {

}

//A function that creates charts in the spreadsheet.
function createChart () {

}

//A function that creates a report using the charts and emails it to the team.
function emailReport () {

}

In the code above, importData, createCharts and emailReport are functions to be run when the user selects the corresponding menu item from the Admin Settings menu. Please note that the code for these functions will need to be written to make the functionality work, i.e., for something to happen when the user selects an item from the menu (right now these functions are just placeholders).

Creating installable triggers

You can create installable triggers using code or via a UI.

Creating Installable triggers via the UI

Step 1 — Click the Clock icon to open the UI for creating triggers.
A screenshot of the toolbar menu in Apps Script. The run button (play icon) is highlighted and the sendEmailReport function has been selected from a drop down menu. When you click the run button, the sendEmailReport function in your script will be run.
Step 2 — Click the + Add Trigger button
A screenshot of an empty Triggers page. The page has an "Add Trigger" button that you can click to begin setting up a trigger.
Step 3 — Configure the trigger and click Save to create it
A screenshot of a popup dialog where you can configure your trigger.

To configure your trigger, you need to select a few options:

  • The function the trigger should run. The dropdown will list all the functions in your script. Pick the one that should be run when the trigger fires.

  • The deployment that should be run. Unless you've created and published a "version" of your script, you should always select Head.

  • The event source (i.e., the type of trigger). Pick Time-driven or Spreadsheet. (There is also a Calendar source but this article doesn't cover it yet.)

  • Select the event type. For example, pick "On edit" if you want the trigger to fire whenever the Spreadsheet is edited or pick a date and time if you're setting up a Time-driven trigger that should fire at some specific time in the future.

  • Choose how often you want to be notified if your trigger fails to run.

Step 4 — That's it! Your shiny new trigger will be listed on the Triggers page
A screenshot of the Triggers page that lists the Edit trigger you just created.

In the above screenshot, I've configured an On edit trigger that will run the sendEmailReport function whenever the spreadsheet is edited.

You can navigate to this page from your script by clicking the clock icon (see Step 1).

Creating Installable triggers using code

To create an installable trigger using code, you need to write a function that will create your trigger and then run in manually from the script editor.

For example, to create an Edit trigger that will run the sendEmailReport function whenever your spreadsheet is edited, you need to: 1) Create a function that will set up this trigger and 2) Run this function manually from the script editor by clicking on the play icon.

Step 1 — Create a function to set up an installable Edit trigger
function createOnEditTrigger() {
  ScriptApp.newTrigger("sendEmailReport") // Run the sendEmailReport function.
    .forSpreadsheet(SpreadsheetApp.getActive()) // Create the trigger in this spreadsheet.
    .onEdit() // We want to set up an Edit trigger.
    .create(); // Create it!
}
Step 2 — Run this function manually to create the trigger

Select the createOnEditTrigger function from the dropdown menu and click on the play icon to create the trigger.

Sample code for creating the various types of installable triggers

Below are links to the official documentation where Google has provided code samples that show you how to create the various types of installable triggers.

The Event object in a trigger

When a trigger fires, it will pass some information to the function it runs. This information can be very useful in certain situations. For example, the Edit trigger will pass information about the range in the spreadsheet that was edited. The function can then use this information to decide if it should take some action.

Perhaps you only care about edits to column A your spreadsheet. In this example, your function can use the range information passed to it via the event object to check which columns were edited.

// Create an Edit trigger only if it hasn't already been created
function createOnEditTrigger() {
  var triggers = ScriptApp.getProjectTriggers();
  var shouldCreateTrigger = true;
  triggers.forEach(function (trigger) {
    if(trigger.getEventType() === ScriptApp.EventType.ON_EDIT && trigger.getHandlerFunction() === "sendEmailReport") {
      shouldCreateTrigger = false; 
    }
  });
  
  if(shouldCreateTrigger) {
    ScriptApp.newTrigger("sendEmailReport")
      .forSpreadsheet(SpreadsheetApp.getActive())
      .onEdit()
      .create();
  }
}

// The sendEmailReport function is run whenever the Edit trigger fires
function sendEmailReport(e) {
  // Check which columns were edited
  var range = e.range;
  // Check if Column A is edited in Sheet1 (GridId or gid is 0)
  if(range.getColumn() === 1.0 && range.getGridId() === 0) {
    //Column A was edited so send the email report.
    //<write some code to send the email>
  }
}

The properties of the event object will vary depending on the type of trigger that fired. You can learn more about these properties in the official Apps Script documentation:

Restrictions that you should be aware of when creating triggers

For security reasons, Google restricts what scripts can and cannot do when run by a trigger. These restrictions are different for Simple and Installable triggers.

Restrictions that apply to both Simple and Installable triggers

  • Triggers do not run if the spreadsheet is opened in read-only (view or comment) mode.

  • Script executions do not cause triggers to run. For example, edits you make to the spreadsheet using a script will not cause the Edit trigger to fire.

  • They are subject to quota limitations in Apps Script.

Additional restrictions that only apply to Simple Triggers

  • The script must be bound to a Google Sheet. It cannot be a standalone script.

  • The script cannot perform actions that require user authorization and it also cannot access user data.

  • For example, the script cannot send emails since this requires user authorization. The script can only edit the spreadsheet it is bound to — it cannot access or edit other documents in Google Drive.

  • They can only determine the identity of the user using the spreadsheet if:

  • The user and the developer of the script belong to the same G Suite domain OR

  • The user is the developer of the script.

  • They cannot run for longer than 30 seconds.

  • For more information on restrictions that apply to Simple Triggers, please refer to the documentation.

Additional restrictions that only apply to Installable Triggers

  • Installable triggers run as the user that created them. For example, if your installable trigger runs a script that sends emails, these emails will be sent from your email address.

  • You can create installable triggers on behalf of other users provided they authorize this action. This means that you cannot create Installable Triggers on behalf of other users in the background without their knowledge.

  • Your script can run any Installable Trigger that is associated with your spreadsheet but it cannot edit or modify triggers that you did not create.

  • For example, if you have set things up so that each user creates an installable trigger that runs as their account, then you will not be able to edit or delete these triggers. Each user will have to edit/delete the trigger they created. You will, however, be able to run any trigger associated with the spreadsheet.

  • For more information on restrictions that apply to Installable Triggers, please refer to the documentation.

Summary

This article introduced you to Triggers in Google Sheets. Here is a summary of

Thanks for reading!


Have feedback for me?

I'd appreciate any feedback you can give me regarding this post.

Was it useful? Are there any errors or was something confusing? Would you like me to write a post about a related topic? Any other feedback is also welcome. Thank you so much!