Part 5: How to Set Up Triggers in Google Sheets with Google App Scripts

As you work with Google Sheets, you may find that certain tasks or actions need to be performed repeatedly or at specific intervals. Instead of doing these tasks manually, you can use Google App Script’s triggers to automate these tasks and save time. In this blog post, we will explore how to set up triggers in Google Sheets using Google App Scripts and provide plenty of examples to follow along.

What are Triggers?

Triggers are events that automatically execute a specified function when certain conditions are met. In Google App Scripts, you can create time-based triggers that run at specific intervals or event-based triggers that run in response to actions in your Google Sheet.

Time-based Triggers

Time-based triggers are used to execute functions at specific intervals, such as every minute, hourly, or daily. You can create a time-based trigger using the Google App Script editor.

Creating a Time-based Trigger

To create a time-based trigger, follow these steps:

1. Open the script editor in your Google Sheet by clicking “Extensions” in the top menu, then click “Apps Script.”
2. In the script editor, click “Triggers” in the left sidebar, then click the “+ Add Trigger” button in the bottom-right corner.
3. In the “Add Trigger” window, select the function you want to run automatically.
4. In the “Choose an event source” dropdown, select “Time-driven.”
5. Choose the type of time-based trigger you want to create (e.g., “Minutes timer,” “Hour timer,” “Day timer,” etc.) and specify the interval or time when the function should run. For example, if you want to run it every 30 minutes, select the “Minutes timer.”
6. Optionally, set the failure notification settings to receive email notifications if the function fails.
7. Click “Save” to create your time-based trigger.

Example: Sending an Automated Email

In this example, we’ll create a custom function that sends an automated email and set up a daily time-based trigger to execute the function every day at a specific time.

First, let’s create a simple custom function to send an email:

function sendDailyEmail() {
  let recipient = 'you@example.com';
  let subject = 'Daily Reminder';
  let body = 'This is your daily reminder to check your Google Sheet!';

  MailApp.sendEmail(recipient, subject, body);
}

After creating the custom function, follow the steps above to set up a daily time-based trigger that runs the sendDailyEmail function every day at a specific time.

Event-based Triggers

Event-based triggers are used to execute functions in response to specific events in your Google Sheet, such as opening the sheet, editing a cell, or changing a cell’s value.

Simple Triggers

Simple triggers are event-based triggers that automatically run when an event occurs, without requiring any additional setup. Google App Script provides several built-in simple triggers:

• onOpen(): Runs when the Google Sheet is opened.
• onEdit(e): Runs when a cell is edited.
• onChange(e): Runs when a cell’s value changes, or when a sheet is added or removed.

To use a simple trigger, create a custom function with the same name as the trigger (e.g., onOpen, onEdit, or onChange) in the script editor.

Example: Formatting Cells on Edit

In this example, we’ll create a custom function that formats a cell’s background color based on its value, using the onEdit simple trigger.

function onEdit(e) {
  let range = e.range;
  let value = e.value;

  if (value >= 0 && value <= 100) { range.setBackgroundColor('green');
  } else if (value > 100 && value <= 500) {
    range.setBackgroundColor('yellow');
  } else {
    range.setBackgroundColor('red');
  }
}
  

This custom function checks the value of the edited cell and sets its background color to green if the value is between 0 and 100, yellow if it’s between 101 and 500, and red otherwise.

Installable Triggers

Installable triggers are event-based triggers that require additional setup in the script editor. These triggers offer more customization options and additional events compared to simple triggers.

To create an installable trigger, follow these steps:

1. Open the script editor in your Google Sheet by clicking “Extensions” in the top menu, then click “Apps Script.”
2. In the script editor, click “Triggers” in the left sidebar, then click the “+ Add Trigger” button in the bottom-right corner.
3. In the “Add Trigger” window, select the function you want to run automatically.
4. In the “Choose an event source” dropdown, select “From spreadsheet” or “From form.”
5. Choose the event type that should trigger the function (e.g., “On open,” “On edit,” “On change,” “On form submit,” etc.).
6. Optionally, set the failure notification settings to receive email notifications if the function fails.
7. Click “Save” to create your installable trigger.

Example: Send Email on Form Submit

In this example, we’ll create a custom function that sends an email when a Google Form is submitted, and we’ll set up an installable trigger to run the function when a form is submitted.

First, create a custom function to send an email:

function sendFormSubmitEmail(e) {
  let recipient = 'you@example.com';
  let subject = 'New Form Submission';
  let body = 'A new response has been submitted to your Google Form!';

  MailApp.sendEmail(recipient, subject, body);
}

After creating the custom function, follow the steps above to set up an installable trigger that runs the sendFormSubmitEmail function when a form is submitted.

Managing Triggers

To manage your triggers, open the script editor and click “Triggers” in the left sidebar. In the “Triggers” tab, you can view all your triggers, edit their settings, or delete them.

Conclusion

Triggers in Google Sheets with Google App Scripts are a powerful way to automate your workflow, saving you time and effort on repetitive tasks. With time-based triggers and event-based triggers, you can run custom functions at specific intervals or in response to certain events. By following the examples and instructions provided in this blog post, you can now set up triggers to automate tasks and improve your productivity in Google Sheets.

In our next blog post, we’ll explore how to integrate external APIs with Google Sheets using Google App Scripts to further expand the capabilities of your spreadsheets.