Google Apps Script

Understanding Triggers

A trigger is a type of script resource that listens for a particular event and executes a function when that event fires.

Simple Triggers

Simple triggers are a set of specially-named functions that are built in to the Google Apps Script language. They are applicable to scripts bound to spreadsheets, documents, or forms. To use simple triggers, give your function a special name that denotes the type of event that runs the function. The list below shows the available simple triggers and the events they handle:

  • onInstall() triggers run when a script is installed from the add-on store or the script gallery.
  • onOpen() triggers run when a spreadsheet, document, or form is opened.
  • onEdit() triggers run when a spreadsheet is edited.

These simple triggers run in response to actions in their containing documents, which can be spreadsheets, documents, or forms, and they run as the active user. For example, if Bob opens the spreadsheet, then the onOpen function runs as Bob, irrespective of who added the script to the spreadsheet.  For this reason, the simple triggers are restricted in what they are permitted to do:

  • They cannot execute when the containing document is opened in read-only (view) mode.
  • They cannot determine the current user.
  • They cannot access any services that require authentication as that user. For example, the Google Translate service is anonymous and can be accessed by the simple triggers. Google Calendar, Gmail, and Sites are not anonymous and the simple triggers cannot access those services.
  • They can only modify the containing document. Access to other documents is forbidden.

For more information on event permissions, see the guide to authorization.

onOpen()

The onOpen() trigger runs automatically when a user opens a spreadsheet, document, or form. The onOpen() trigger is most commonly used to add custom menu items to the editor's menu bar.

// Add a custom menu to the active spreadsheet.
function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [];
  // When the user selects "addMenuExample" menu, and clicks "Menu Entry 1", the function function1 is executed.
  menuEntries.push({name: "Menu Entry 1", functionName: "function1"});
  menuEntries.push({name: "Menu Entry 2", functionName: "function2"});
  ss.addMenu("addMenuExample", menuEntries);
}
// Add a custom menu to the active document, including a separator and a sub-menu.
function onOpen() {
  DocumentApp.getUi()
      .createMenu('My Menu')
      .addItem('My Menu Item', 'myFunction')
      .addSeparator()
      .addSubMenu(DocumentApp.getUi().createMenu('My Submenu')
          .addItem('One Submenu Item', 'mySecondFunction')
          .addItem('Another Submenu Item', 'myThirdFunction'))
      .addToUi();
}

onEdit()

The onEdit() trigger runs automatically when any cell of the spreadsheet is edited. A very simple use case for onEdit() is to record the last modified time in a comment on the cell that was edited. The trigger function can accept an event object, which contains additional information about the edit that took place. See the Understanding Events page for more details about the available fields.

function onEdit(event)
{
  var ss = event.source.getActiveSheet();
  var r = event.source.getActiveRange();
  r.setComment("Last modified: " + (new Date()));
}

onInstall()

The onInstall() trigger is called when an add-on is installed from the add-on store or a script is installed from the (only in the older version of Google Sheets). The most common use case for onInstall() is the same as for onOpen(): setting up custom menus for the user. When an add-on or script is first installed, the onOpen() trigger cannot be called because the file is already open. If your script needs to make menus available or perform any other tasks immediately after being installed, the script can call onOpen() from onInstall(). For example:

function onInstall() {
  onOpen();
}

Installable Triggers

Installable triggers are set from the Resources menu within the Script Editor or by adding them programmatically using ScriptApp.newTrigger(). Use installable triggers to run scripts in any of the following scenarios:

  • At a specific time. You can define the time as a particular year, month, day of a month, day of a week, hour, minute, or second.
  • On a recurring basis: weekly, daily, hourly, or on a minute basis.
  • When a form response is submitted (either to the form's internal response storage or to a spreadsheet).
  • When a spreadsheet is edited. Unlike the simple trigger onEdit, the installable trigger can act as the user who installed the trigger.
  • When a spreadsheet or the form editor is opened. (Note that this trigger does not activate when a user opens a form to respond, but rather when an editor opens the form to modify it.) Unlike the simple trigger onOpen, the installable trigger can act as the user who installed the trigger.

The installable triggers have some similarities to simple triggers, but also these differences:

  • They run as the user who installed the trigger, not as the user triggering the event.
  • They can potentially access all services available to the user who installed the trigger.
  • They are fully-capable scripts with none of the access limitations of simple event triggers.
  • They may not be able to determine which user triggered the event being handled. For clock tick events, this is not important, but in a spreadsheet edit event, the user changing the spreadsheet would not necessarily be identifiable.
  • The spreadsheet or form containing the script does not have to be open for the event to be triggered and the script to run.

You can connect triggers to one or more functions in a script. Any function can have multiple triggers attached.

When writing the function for an installable trigger, don't use the the special names reserved for simple triggers (onOpen and onEdit). Doing so will cause the function to run twice: once as a simple trigger and once as the installed trigger. Instead, use custom function names, such as myOnEdit.

When a script runs because of a trigger, the script runs using the identity of the person who installed the trigger, not the identity of the user whose action triggered the event. This is for security reasons. For example, if you install a trigger in your script, but your coworker performs the action that triggers the event, the script runs under your identity, not your coworker's identity. For complete information on this, see Understanding Permissions and Script Execution.

Using Time-Driven Triggers

You might want to run a script daily, hourly, or on the same day of the week each week. Here are instructions for setting a trigger to run the script.

To run a script at a time or times you designate:

  1. From the Script Editor, choose Resources > Current project's triggers. You see a panel with the message No triggers set up. Click here to add one now.
  2. Click the link that says No triggers set up. Click here to add one now.
  3. Under Run, select the function you want executed on schedule.
  4. Under Events, select Time-driven.
  5. On the first drop-down list that appears, select Week timer, Day timer, Hour timer, or Minutes timer, or Specific date and time. Depending on which you select, you see one or more additional lists or a text box. To test the trigger and your function, you might want to pick a short duration so that you can see the execution without having to wait hours or days.
    • If you picked Week timer, select a day of the week and time of day.
    • If you picked Day timer, select an hour.
    • If you picked Hour timer, select an interval of hours.
    • If you picked Minutes timer, select an interval of minutes.
    • If you picked Specific date and time, enter a date in YYYY-MM-DD HH:MM format.
  6. Click Save.
  7. To ensure that the script runs at the correct time for a particular time zone, click File > Properties, select a time zone, and click Save.

Using Container-Specific Installable Triggers

Three actions can trigger a script that is bound to a spreadsheet:

  • Opening a spreadsheet
  • Editing a spreadsheet
  • Submitting a form

Two actions can trigger a script that is bound to a form:

  • Opening the form editor (note that this trigger does not activate when a user opens a form to respond, but rather when an editor opens the form to modify it)
  • Submitting a form

For instance, you might want to analyze or update existing data in a spreadsheet each time new data are added by a form submission.

To run a script when a particular action is performed:

  1. Open or a create a new spreadsheet.
  2. Click the Unsaved spreadsheet dialog box and change the name.
  3. Choose Tools > Script Editor and write the function you want to run.
  4. Choose Resources > Current project's triggers. You see a panel with the message No triggers set up. Click here to add one now.
  5. Click the link.
  6. Under Run, select the function you want executed by the trigger.
  7. Under Events, select From Spreadsheet.
  8. From the next drop-down list, select On open, On edit, or On form submit.
  9. Click Save.

Errors in Triggers

When a trigger fails, you do not see an error as you would when you run a script from the Script Editor. Google Apps Script sends you an email notification informing you of the trigger failure. In most cases, the email contains the error that resulted from the script failure. The email you receive also contains a link that enables you to edit or delete the trigger that failed.

By default, this email is sent once per day at midnight and contains information about each failure of the particular script. You can change the default time of day that the notification is sent, the account to which the notification is sent, and the frequency with which the notification is sent. You can set up the notification so that the email is sent immediately upon failure.

To change the notification defaults:

  1. Open the Script Editor.
  2. On the Current Project's Triggers dialog box, click the Notifications link.
  3. On the drop-down lists, choose the desired email account, frequency, and time of day.
  4. Click OK.

If you're in the process of testing a script, you might want to see error messages resulting from trigger failure before the automated email is sent or without changing the frequency so that the notification is sent immediately. To determine what went wrong in the script, you can surround the script with a try-catch code block. This is specialized code that will capture information about an error and send you an email notification containing the error message:

function myFunction(){
  try {
    // script code here
  } catch (e) {
    MailApp.sendEmail("youremail@example.com", "Error report", e.message);
  }
}

Authentication required

You need to be signed in with Google+ to do that.

Signing you in...

Google Developers needs your permission to do that.