Google Sheets Apps Script triggers explained

A trigger subscribes a function to an event: a file opening, a cell edit, a form submission, or a clock reaching a set time. Simple triggers use reserved names such as onOpen and onEdit. They need no setup, run without an authorisation prompt, and are restricted to a small set of services.

Installable triggers are created in the editor or with ScriptApp. They run with the authorisation of the person who created them, can call Gmail, UrlFetch, and other services, and can be time-based. That extra power comes with a dependency: the trigger acts as its creator, so the account must retain access.

Handlers receive an event object. For an edit, e.range tells you where the change happened, e.value is the new value, e.oldValue is the previous value, and e.source is the spreadsheet. Use those properties instead of assuming the active sheet.

Guard the handler before doing work. Check the sheet name and the column, then exit if the edit is outside the input area: if (e.range.getSheet().getName() !== 'Inputs') return;. Without the guard, a formatting change or a paste in a report tab can trigger a full recalculation.

onEdit does not fire when a formula recalculates, when a script writes a value, or when data arrives through the API. A multi-cell paste gives a range, and e.value may be undefined, so read the range. Use onChange for structural events such as inserting a sheet, not for cell values.

Time-driven triggers run hourly, daily, weekly, or at a set time. They use the script project's time zone, which is not always the spreadsheet's. Set it explicitly; a report scheduled for 06:00 in the wrong zone is a report for the previous day.

Make the job idempotent. A daily summary should key on the reporting date, check whether the output already exists, and overwrite or skip rather than append. If the run is retried, the result should be the same. Store the last successful run date in PropertiesService and backfill from there when a run is missed.

Use LockService when a run can overlap the next one. A job that usually takes two minutes but occasionally takes twenty will eventually collide with its own schedule, and two writes to the same sheet produce duplicated rows.

A trigger created by hand is invisible in the source and easy to forget. Create it in a setup function so the configuration is reproducible: ScriptApp.newTrigger('dailySummary').timeBased().everyDays(1).atHour(6).create(). Record the function name and schedule in the README.

Running setup twice creates duplicate triggers, and duplicate triggers are the usual cause of doubled emails and repeated rows. List the project triggers, match on the handler name, and delete existing ones before creating a new one. A small setup function that reconciles the desired state is safer than manual clicks.

Installable triggers have daily quotas and run as their creator. A trigger owned by a departing employee stops or fails. Transfer ownership, recreate the triggers, and test one run.

By default an unhandled error stops the function and leaves a red entry on the Executions page. Nobody watches that page. Wrap the body in try and catch, write a row to a Logs sheet with timestamp, job name, status, and message, and send an alert only when the same job fails repeatedly.

Log a row identifier and an error message, but not a full record that may contain personal data.

Test the failure path, not just the happy path. Revoke a permission, point the job at an empty range, and run it twice in a row. A trigger that works once and duplicates on retry is not ready.

Use this list when a job changes owner or starts behaving oddly.