Apps Script Course: Triggers

Triggers allow the execution of instructions when an event occurs.

The two main triggers are onEdit (after a cell value is modified) and onOpen (when the document is opened).

onEdit

Using this trigger is quite simple; you just need to name a function onEdit and add the instructions to execute. This function will then be executed every time a cell's value is modified.

Here's an example with a cell that must contain a value in uppercase (even if the user enters it in lowercase).

Start by entering the function and adding an argument e that will contain information related to the event that triggered the function:

function onEdit(e) {

}

In this case, the value to be converted to uppercase is in cell B2, so we will check if the cell modified by the user is B2:

function onEdit(e) {

  const modifiedCell = e.range;

  // If cell B2
  if (modifiedCell.getA1Notation() == 'B2') {

  }
}

And convert it to uppercase if it is:

function onEdit(e) {

  const modifiedCell = e.range;

  // If cell B2
  if (modifiedCell.getA1Notation() == 'B2') {

    // Convert to uppercase
    modifiedCell.setValue(modifiedCell.getValue().toUpperCase());
  }
}

To test the function in this example, start by saving the changes and then test by modifying cell B2 directly. Do not try to do it from the editor as it will not work (the argument e is not specified in the case of execution from the editor).

onOpen

Using this trigger follows the same principle; name a function onOpen and add the instructions to execute. This function will then be executed when the document is opened.

This trigger is very useful, especially for adding a custom menu upon document opening. But since we haven't yet covered menus, here's a different example where the goal is to create a counter in cell C1 of Sheet1 that increments by 1 every time the document is opened:

function onOpen(e) {

  const cellC1 = e.source.getSheetByName('Sheet1').getRange('C1');

  // Increase the value of C1 by 1
  cellC1.setValue(cellC1.getValue() + 1);
}

Or another version without using the argument e (which can be tested from the editor):

function onOpen() {

  const cellC1 = SpreadsheetApp.getActive().getSheetByName('Sheet1').getRange('C1');

  // Increase the value of C1 by 1
  cellC1.setValue(cellC1.getValue() + 1);
}