Triggers: onOpen, onEdit, time-driven (daily 9 AM report)

Concept

1. Two kinds of triggers

	Simple triggers	Installable triggers
Set up by	just naming the function `onOpen`, `onEdit`	the Triggers page or code
Can send email, access other files	No	Yes (runs with your permissions)
Time-based	No	Yes
Good for	menus, small edits	reports, alerts, schedules

2. onOpen — your own menu

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('ExcelWalaa Tools')
    .addItem('Calculate GST', 'fastGST')
    .addItem('Build reorder list', 'buildReorderList')
    .addSeparator()
    .addItem('Send stock alert', 'sendStockAlert')
    .addToUi();
}

Reload the sheet — a new menu appears next to Help. The second argument is the function name as text.

3. onEdit — automatic timestamp

When someone changes the Status (column D) of an order, write the date and time in column E:

function onEdit(e) {
  const range = e.range;
  const sheet = range.getSheet();
  if (sheet.getName() !== 'Orders') return;
  if (range.getColumn() !== 4 || range.getRow() < 2) return;

  const stamp = range.getValue() === '' ? '' : new Date();
  sheet.getRange(range.getRow(), 5).setValue(stamp);
}

e is the event object: e.range (edited cell), e.value (new value), e.oldValue.
Always exit early (return) when the edit isn't the one you care about — onEdit fires for every edit in the file.
onEdit doesn't fire for changes made by scripts. When many cells are pasted at once, e.range covers all of them, but this code stamps only the first row — loop over the rows if you need more.
Don't press Run on onEdit in the editor — there's no e, so it errors. Test by editing the sheet.

4. Time-driven trigger — daily 9 AM report

First write the job:

function dailyReport() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const data = ss.getSheetByName('Data').getDataRange().getValues();
  data.shift();
  const low = data.filter(r => Number(r[1]) < 5).length;
  ss.getSheetByName('Log').appendRow([new Date(), 'Items below reorder level', low]);
}

appendRow adds a row after the last filled row — perfect for logs.

Option A — Triggers page (easiest): left sidebar ⏰ Triggers → Add Trigger → function dailyReport → event source Time-driven → Day timer → 9am to 10am → Save.

Option B — in code (run once):

function createDailyTrigger() {
  ScriptApp.getProjectTriggers()
    .filter(t => t.getHandlerFunction() === 'dailyReport')
    .forEach(t => ScriptApp.deleteTrigger(t));          // avoid duplicates

  ScriptApp.newTrigger('dailyReport')
    .timeBased()
    .everyDays(1)
    .atHour(9)
    .create();
}

5. Timing details

Google runs daily triggers somewhere within the chosen hour (e.g. 9:00–10:00), not at an exact minute.
The hour uses the script's time zone: Project Settings (⚙️) → Time zone → set (GMT+05:30) India Standard Time.
Other schedules: .everyHours(1), .everyMinutes(15), .onWeekDay(ScriptApp.WeekDay.MONDAY).atHour(9).

6. When things go wrong

Failed trigger runs appear under Executions (left sidebar), and Google emails you a failure summary. Wrap the job in try { … } catch (err) { … } to log errors to a sheet.

Common mistakes

Putting email code in a simple onEdit (fails silently — use an installable "On edit" trigger instead). Creating the same time trigger many times by running the setup function repeatedly. Wrong script time zone, so "9 AM" runs at 3:30 AM IST.

What you'll learn