Triggers & Events In Google Sheets

Introduction

According to the documentation, triggers let Google Apps Script run a function automatically when a certain event, like opening a document, occurs. Apps Script supports two types of triggers, simple and installable. Simple triggers are a set of reserved functions built into Apps Script, like the function onOpen(e), which is executed when a user opens a Google Docs, Sheets, Slides, or Forms file. Installable triggers, on the other hand, offer more capabilities than simple triggers but must be activated before use. They can call services that require an authorization, they offer several additional types of events including time-driven (clock) triggers, and they can be controlled programmatically.

Both types of triggers let Apps Script run a function automatically if a certain event occurs. When a trigger fires, Apps Script passes in the function an event object as an argument, typically called “e”. The event object contains information about the context that caused the trigger to fire. For example, the simple onEdit(e) trigger for a Google Sheets script that we will see below, uses the event object to determine which cell was edited.

Unlike VBA, in Google Apps Script there are only a few events that can be used to detect changes within an application. In this post, we will analyze two of the most useful ones, the onEdit and the onOpen. Examples will be given on Google Sheets application. We will also learn how to install a trigger, both manually and programmatically.

GAS code

In the code below there are seven GAS functions:

• onEdit: A simple trigger that fires when the contents of a cell change. In the particular case, the range that is checked for changes is the “A1:A10” in the active sheet.
• onOpen: A simple trigger that is a logger, which runs every time that someone opens the spreadsheet. It fills the current date/time in the last row of column B in the active sheet.
• createSpreadsheetOpenTrigger: It creates an open trigger programmatically.
• createTimeDrivenTrigger: It creates a time-driven trigger programmatically that runs every 5 minutes.
• rangeIntersect: A helping function that returns true when two ranges intersect. This is the GAS implementation of the Application.Intersect method that exists in VBA/Excel.
• greeting: A function that displays a message box in the browser.
• myCounter: A function that finds the last row in column D of the active sheet and adds +1 to the previous value.

/* Start of the GAS code. */
 
/*
  --------------------------------------------------------------------------------
  The next set of functions show how to use triggers and events in Google Sheets.
  
  Written By:    Christos Samaras
  Date:          29/09/2018
  Last Updated:  05/10/2018
  E-mail:        [email protected]
  Site:          https://myengineeringworld.net
  --------------------------------------------------------------------------------
*/
  
/*
  ----------------
  Simple Triggers
  ----------------
*/
function onEdit(e)
{  
  /*
  -----------------------------------------------------------------------------------------
  A simple trigger that fires after a cell change in the range A1:A10 of the active sheet.
  -----------------------------------------------------------------------------------------
  */
  
  // Get the active sheet.
  var sheet = SpreadsheetApp.getActiveSheet();
  
  // Set the range that has to be chekced for changes.
  var selectedRange = sheet.getRange("A1:A10");
  
  // Get the active cell from the event obejct.
  var activeCell = e.range;
  
  // Check if the active cell belongs to the range of interest. 
  // If yes, show a message box in the browser.
  if(rangeIntersect(activeCell, selectedRange)) 
    Browser.msgBox("Triggered after the cell " + activeCell.getA1Notation() + " changed!");   
}
 
function onOpen(e)
{
  /*
  -------------------------------------------------------------------
  A simple trigger that acts as a logger, filling the date/time that
  the spreadsheet was openned in the last row of the column B.
  -------------------------------------------------------------------
  */
  
  // Get the active sheet.
  var sheet = SpreadsheetApp.getActiveSheet();
  
  // Get the last row in column B that contains values.
  var lastRow = sheet.getRange("B1:B").getValues().filter(String).length;
  
  // Set the current date/time in the next available row in column B.
  sheet.getRange(lastRow + 1, 2).setValue((new Date()).toLocaleString());  
}
 
 
/*
  ---------------------
  Installable Triggers
  ---------------------
*/
function createSpreadsheetOpenTrigger() 
{
  /*
  --------------------------------------------------------------------------
  Creates a trigger programmatically that fires when the spreadsheet opens.
  --------------------------------------------------------------------------
  */
  
  // Get the active spreadsheet (workbook in Excel terminology).
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // Create a new trigger that will run when the spreadsheet opens.
  // The trigger will fire the greeting function.
  ScriptApp.newTrigger('greeting').forSpreadsheet(ss).onOpen().create();
}
 
function createTimeDrivenTrigger() 
{    
  /*
  ---------------------------------------------------------------
  Creates a trigger programmatically that fires every 5 minutes.
  ---------------------------------------------------------------
  */
  
  // Create a new trigger that will run every 5 minutes.
  // The trigger will run the myCounter function.
  ScriptApp.newTrigger('myCounter').timeBased().everyMinutes(5).create();
}
 
 
/*
  ------------------
  Helping Functions
  ------------------
*/
function rangeIntersect (rng1, rng2) 
{
  /*
  ------------------------------------------
  Returns true if the two ranges intersect.
  ------------------------------------------
  */
  
  // Check for empty objects.
  if(rng1 == null || rng2 == null)
    return false;
  
  // Compare the rows and columns of the two ranges.
  return (rng1.getLastRow() >= rng2.getRow()) && (rng2.getLastRow() >= rng1.getRow()) 
          && (rng1.getLastColumn() >= rng2.getColumn()) && (rng2.getLastColumn() >= rng1.getColumn());
}
 
function greeting()
{
  /*
  ---------------------------------
  It displays a hello message box.
  ---------------------------------
  */ 
  
  // Display hello in the browser.
  Browser.msgBox("Hello!");
}
 
function myCounter()
{
  /*
  ----------------------------------------------------------------
  It adds +1 in the last row of the column D of the active sheet.
  ----------------------------------------------------------------
  */
  
  // Get the active sheet.
  var sheet = SpreadsheetApp.getActiveSheet();
  
  // Get the last row in column D that contains values.  
  var lastRow = sheet.getRange("D1:D").getValues().filter(String).length;
 
  // Check if the last row is 0. If yes, fill the number 1 in the cell D1.
  if(lastRow == 0)
  {
    sheet.getRange(lastRow + 1, 4).setValue(1);
    return;
  }
  
  // Add +1 in the next available row in column D.
  sheet.getRange(lastRow + 1, 4).setValue(sheet.getRange(lastRow, 4).getValue() + 1);
}
 
/* End of the GAS code. */

onEdit example

The above image shows what happens when the onEdit trigger runs. If the user changes the contents of the cell A6, a message box will pop-up in the browser. Of course, this simple event was written only for demonstration purposes.

onOpen example

When the user double-clicks to open the selected spreadsheet, the current date/time will be filled in the last row of column B of the active sheet. This event can be very useful for logging purposes.

How to install triggers

The manual way

To manually install a trigger, you will have to go to the Script editor. There, from the menu, select Edit and then Current’s project triggers.

In the window that will pop up, click on the No triggers set up. Click here to add one now. hyperlink.

In the form that pop-ups, select the function you want to run and the appropriate event. In this example, the greeting function was selected from the dropdown list, along with the From spreadsheet and On open event. To save the trigger, click on the Save button. That’s it!

The programmatic way

The programmatic way is probably as easy as the manual one. You just have to use the createSpreadsheetOpenTrigger function and adjust it to your needs (e.g. change the function that will be executed when the spreadsheet opens).

To set the trigger, simply click the Run option on the menu of Script editor and select the createSpreadsheetOpenTrigger function from the sub-menu. The function will run and the trigger will be set.

Authorization procedure

Note: the first time that you will try to install a trigger, either manually or programmatically, you will have to set authorization permissions. So, a message box will pop-up asking you to authorize the trigger. Just click the Review Permissions button.

Select your Google account to continue.

Finally, in the next form, click on the Allow button.

Congratulations! You have successfully installed a trigger! Read here about the restrictions that installable triggers have.

Read also

How To Create & Use A Custom Function In Google Sheets

Page last updated: 17/06/2019