Simple triggers: onOpen, onEdit

What is a trigger?

A trigger runs a function automatically when something happens — the file opens, a cell is edited, a form is submitted, or a time arrives. Simple triggers are the easiest kind: you just name your function with a reserved name, and Google runs it. No setup needed.

You already used onOpen to build a menu.

onEdit and the event object

When onEdit runs, Google passes an event object e describing what changed:

function onEdit(e) {
  const range = e.range;            // the edited cell(s)
  const sheet = range.getSheet();
  console.log(sheet.getName(), range.getA1Notation(), 'old:', e.oldValue, 'new:', e.value);
}

Useful properties:

• e.range — the edited range
• e.value — new value (single-cell edits only)
• e.oldValue — previous value (single-cell edits only)
• e.source — the spreadsheet

Example 1: auto-timestamp

When the Status column (D) in the Orders tab changes, write the date and time in column E.

function onEdit(e) {
  const range = e.range;
  const sheet = range.getSheet();

  if (sheet.getName() !== 'Orders') return;   // only this tab
  if (range.getColumn() !== 4) return;        // only column D
  if (range.getRow() === 1) return;           // ignore header

  sheet.getRange(range.getRow(), 5).setValue(new Date());
}

The three return lines at the top are important: onEdit fires for every edit in the file, so exit quickly when the edit isn't the one you care about.

Example 2: force upper-case codes

function onEdit(e) {
  const r = e.range;
  if (r.getSheet().getName() === 'Stock' && r.getColumn() === 2 && typeof e.value === 'string') {
    r.setValue(e.value.toUpperCase());
  }
}

Only one onEdit per project

You can't have two functions both named onEdit. Combine them by calling helpers:

function onEdit(e) {
  stampStatus(e);
  upperCaseCodes(e);
}

Limits of simple triggers

• No services that need authorization: you cannot send email (GmailApp/MailApp) or access Drive or other files. Simple triggers run without asking the user for permission.
• Must finish within 30 seconds.
• onEdit runs only for edits a person makes — not for changes made by scripts, formulas recalculating, or form submissions.
• Pasting into many cells gives one onEdit call with a multi-cell e.range (and no e.value).
• Don't click Run on onEdit in the editor — e will be undefined and you'll get an error. Test by editing the sheet.

When you need email or Drive, use an installable trigger (next lesson).

Key points

• Name a function onOpen or onEdit and it just works.
• Use e.range to check where the edit happened and exit early otherwise.
• Simple triggers can't send emails or touch other files.