copy

Sunday, 30 September 2018



Last updated: 05/10/2018, 3 min read (without the code)

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 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 actually 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 the column D of the active sheet and adds +1 to the previous value.

/*
  --------------------------------------------------------------------------------
  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:        xristos.samaras@gmail.com
  Site:          https://www.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);
}

onEdit example

OnEdit Event 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

OnOpen Event 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 a trigger



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.

Current Project Triggers

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

Add New Trigger
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!
Set An Open Trigger


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.

Run Function On GAS Editor


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.

Trigger Authorization

Select your Google account to continue.

Select Google Account

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

Allow Access To Google Account

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



Read also




Did you like this post? If yes, then share it with your friends. Thank you!



Mechanical Engineer (Ph.D. cand.), M.Sc. Cranfield University, Dipl.-Ing. Aristotle University, Thessaloniki - Greece.
Communication: e-mail, Facebook, Twitter, Google+ and Linkedin. More info