How To Setup Triggers on Google Sheets

Ever had a google sheets macro that you wanted to run multiple times, but had to always kick off manually? Well, we’ve got some good news! Google Script allows you to set triggers to kick off macros automatically for it run on your google sheets even if you are not there. There are multiple types of triggers that google script supports.

To access the project triggers, you need to go to Script Editor. In the Menu, hover over Edit, and select “Current Project Triggers” to open the triggers page. From there, just click on the “Add Triggers” button on the bottom to add various triggers that we explain below.

Types of Triggers:

1. Spreadsheet-Driven
2. Time-Driven
3. Calendar-Driven

All three of these triggers can be assigned to a macro to run. We will explain each in detail.

Spreadsheet Driven Trigger

The spreadsheet trigger is specifically controlled by the google sheets that the macro is assigned to. This trigger has multiple events that can cause it to kick off your macro. The first one is the “On Open” trigger. This means that whenever the spreadsheet is opened, the macro will automatically run. The next trigger is the “On Edit” trigger, which means that whenever the spreadsheet is edited, the macro will run. Example: Deleting text from a cell or putting a value into a cell. The third event type is the “On Change” trigger. “On Change” is similar to the “On Edit“, but focuses on the spreadsheet workbook structure rather than what is in the sheet cells. Example: Creating a new sheet would trigger this event type. The last event type for the spreadsheet-driven trigger is called the “On Form Submit“. If you have a form built into the spreadsheet, once a user submits the form, the macro assigned to the event type will kick off and run. Consider creating only one of these triggers at a time as you might bog down the spreadsheet when setting multiple event types.

All these event types can be reliable depending on how the user interacts with the google sheet.

Time-Driven Trigger

The Time-Driven trigger is probably the most commonly used trigger in google sheets. Most of the macros that we create need to run hourly, daily, weekly, or at specific dates and times.

The Google Script time trigger allows you to schedule at these times and you can go further and schedule your macro every minute if you want to. Just be aware that google script has specific intervals in place when selecting the times.

Calendar Trigger

The very last trigger that Google Sheets/Google Script supports is calendar based. You just need to provide your GMAIL, so once your google calendar updates, the macro will kick off. This is great as you can set up your macro to run based on your schedule.

The spreadsheet, time, and calendar triggers are very useful when needing to automate processes, especially Digital Finance related processes. If you need a macro run when a spreadsheet opens, then you just have to use the spreadsheet trigger. If you have a process that needs to be kicked off very early every morning, then the time-driven trigger will best suit your process. If you want to revolve your process around the meetings you have on your calendar, you will need to use the calendar trigger. The type of trigger to use really depends on the “when or how” of your macro process. We are moving towards a future that is cloud-based so having these macros run on Google servers saves computer resources and time as its all scheduled in the background.