Intermediate · I2

Menus, dialogs and triggers

So far you have run everything from the editor. Real users never open the editor. In this chapter your scripts get a menu in the Sheet, talk to the user with dialogs, and run by themselves when a cell changes or at a time you choose.

A menu of your own

Add this to the Marks scripts project, next to styleHeader and highlightMarks from I1:

Code.gs

// onOpen is a "simple trigger": Google runs it every time the file opens.
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('🎓 Class tools')
    .addItem('Style the header', 'styleHeader')
    .addItem('Colour the marks', 'highlightMarks')
    .addSeparator()
    .addSubMenu(
      SpreadsheetApp.getUi()
        .createMenu('Reports')
        .addItem('Count students', 'countStudents')
    )
    .addToUi();
}

function countStudents() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Marks');
  const n = sheet.getLastRow() - 1;
  SpreadsheetApp.getUi().alert(`There are ${n} students on the Marks tab.`);
}

Save, then reload the Sheet's browser tab.
A new 🎓 Class tools menu appears after Help, a few seconds after the sheet loads.
Choose Reports → Count students. The first time, Google asks for permission, then the alert shows.

onOpen is a special name. Google calls a function with that exact name whenever someone opens the file. These special names are simple triggers: onOpen, onEdit, onSelectionChange, doGet and doPost.

Buttons too

You can also run a script from a drawing: Insert → Drawing, draw a button, place it, then click its ⋮ menu → Assign script and type the function name (no brackets).

Next: alerts and prompts →

Talking to the user

Method	Shows	Returns
`ui.alert(msg)`	A message with OK	The button pressed
`ui.alert(title, msg, ui.ButtonSet.YES_NO)`	A question	`ui.Button.YES` or `NO`
`ui.prompt(title, msg, buttons)`	A text box	A response: `getResponseText()`, `getSelectedButton()`
`ss.toast(msg, title, seconds)`	A small note in the corner	Nothing; it doesn't wait

Code.gs

function askAndAdd() {
  const ui = SpreadsheetApp.getUi();

  const res = ui.prompt('Add a student', 'Type the name:', ui.ButtonSet.OK_CANCEL);
  if (res.getSelectedButton() !== ui.Button.OK) return;   // user pressed Cancel
  const name = res.getResponseText().trim();
  if (!name) {
    ui.alert('No name typed, so nothing was added.');
    return;
  }

  const sure = ui.alert('Confirm', `Add "${name}" to the Marks tab?`, ui.ButtonSet.YES_NO);
  if (sure !== ui.Button.YES) return;

  SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Marks').appendRow([name, '', '']);
  SpreadsheetApp.getActiveSpreadsheet().toast(`${name} added`, 'Done', 3);
}

Alerts and prompts pause the script until the user answers. Always handle Cancel and an empty answer: users click the wrong button all the time.

Dialogs need a person

getUi() only works while someone has the file open. A script run by a time trigger has no screen to show a dialog on, and fails with "Cannot call SpreadsheetApp.getUi() from this context". Use a toast, a log row or an email instead.

Next: onEdit →

React when a cell changes

Google calls onEdit(e) after every edit a person makes, and passes an event object describing it:

Field	Holds
`e.range`	The edited range (use `getRow()`, `getColumn()`, `getSheet()`)
`e.value`	The new value, as text. Missing if several cells were pasted at once.
`e.oldValue`	The previous value (single-cell edits only)
`e.source`	The spreadsheet

Code.gs

// Runs by itself whenever a person edits a cell.
// e.range is the edited cell, e.value its new value, e.oldValue the old one.
function onEdit(e) {
  const sheet = e.range.getSheet();
  if (sheet.getName() !== 'Marks') return;     // only watch one tab
  if (e.range.getColumn() !== 3) return;       // only the Mark column
  if (e.range.getRow() === 1) return;          // not the header

  // Stamp the time of the change in column G of the same row.
  sheet.getRange(e.range.getRow(), 7).setValue(new Date());

  // Turn the mark red if it is outside 0–100.
  const v = Number(e.value);
  e.range.setFontColor(v < 0 || v > 100 ? '#c5221f' : null);
}

The first three lines are guard clauses: leave straight away unless this is an edit you care about. onEdit fires for every edit in every tab, so without guards it would stamp times all over the file.

Don't press Run on onEdit in the editor: there is no edit, so e is undefined and you get "Cannot read properties of undefined". Test it by editing the sheet.

A tick-box task list

Make a Tasks tab with a checkbox column A, Task in B, Owner in C and Done at in D. Then:

Code.gs

// When a task is ticked in column A, grey out the row and record who and when.
function onEdit(e) {
  const r = e.range;
  const sheet = r.getSheet();
  if (sheet.getName() !== 'Tasks' || r.getColumn() !== 1 || r.getRow() === 1) return;

  const row = sheet.getRange(r.getRow(), 1, 1, 4);
  if (e.value === 'TRUE') {
    row.setFontColor('#80868b');
    sheet.getRange(r.getRow(), 4).setValue(new Date());
  } else {
    row.setFontColor(null);
    sheet.getRange(r.getRow(), 4).clearContent();
  }
}

A project can have only one function called onEdit. If you want both behaviours, put both checks inside one onEdit and call helper functions.

Next: time triggers →

Run on a schedule

A time-driven trigger runs a function every minute, hour, day or week, on Google's servers. Your computer can be off. Create one in code (below) or by clicking ⏰ Triggers in the left rail → Add Trigger.

Code.gs

// Run this ONCE by hand to create the trigger.
function createDailyTrigger() {
  // Remove old copies first, so running this twice doesn't make two triggers.
  ScriptApp.getProjectTriggers()
    .filter((t) => t.getHandlerFunction() === 'dailyReport')
    .forEach((t) => ScriptApp.deleteTrigger(t));

  ScriptApp.newTrigger('dailyReport')
    .timeBased()
    .everyDays(1)
    .atHour(7)          // some time between 07:00 and 08:00
    .create();
}

// Google runs this every morning, even when your computer is off.
function dailyReport() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Marks');
  const marks = sheet.getRange(2, 3, sheet.getLastRow() - 1, 1).getValues().flat();
  const avg = marks.reduce((s, m) => s + m, 0) / marks.length;
  const log = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Log');
  log.appendRow([new Date(), `Average mark ${avg.toFixed(1)}`]);
}

Make a tab called Log.
Select createDailyTrigger and run it once. Authorize.
Open ⏰ Triggers in the left rail: dailyReport is listed, "Time-based, Day timer".
To test without waiting until tomorrow, run dailyReport by hand and check the Log tab.

Builder	Runs
`.everyMinutes(5)`	Every 1, 5, 10, 15 or 30 minutes
`.everyHours(2)`	Every n hours
`.everyDays(1).atHour(7)`	Daily, within the hour starting 07:00
`.onWeekDay(ScriptApp.WeekDay.MONDAY).atHour(8)`	Weekly
`.at(date)`	Once, at a date and time

If a triggered run fails, Google emails you a summary. The Executions page shows every run and its log.

Next: simple vs installable →

Two kinds of trigger

	Simple trigger	Installable trigger
How you make it	Name a function `onOpen` / `onEdit`	In code with `ScriptApp.newTrigger`, or the Triggers page
Runs as	Whoever is using the file	You, the person who created it
Can send email, use Drive…	No: nothing that needs permission	Yes
Time limit	30 seconds	6 minutes, like a normal run
Events	Open, edit, selection change	Open, edit, change, form submit, time, calendar

So if your onEdit needs to email someone when a box is ticked, rename it (say handleEdit) and add an installable "On edit" trigger for it. Keep the special name for simple, quick jobs.

Next: practice →

Practice

1. My tools menu Easy

A ⭐ My tools menu with two items: "Say hello" (an alert with your email) and "Insert today" (today's date in the selected cell).

Show a solution

Code.gs

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('⭐ My tools')
    .addItem('Say hello', 'sayHello')
    .addItem('Insert today', 'insertToday')
    .addToUi();
}

function sayHello() {
  SpreadsheetApp.getUi().alert(`Hello, ${Session.getActiveUser().getEmail()}`);
}

function insertToday() {
  SpreadsheetApp.getActiveRange().setValue(new Date());
}

2. Force capitals Medium

On a tab called Codes, anything typed in column A becomes UPPER CASE as soon as it is entered.

Show a solution

Code.gs

// Anything typed in column A of the Codes tab becomes UPPER CASE.
function onEdit(e) {
  const r = e.range;
  if (r.getSheet().getName() !== 'Codes' || r.getColumn() !== 1) return;
  if (typeof e.value === 'string' && e.value !== e.value.toUpperCase()) {
    r.setValue(e.value.toUpperCase());
  }
}

3. Friday backup Medium

Every Friday at 4 pm, copy the Marks tab to a new tab named "Backup 2026-11-06" (with that day's date).

Show a solution

Code.gs

function createWeeklyTrigger() {
  ScriptApp.newTrigger('weeklyBackup')
    .timeBased()
    .onWeekDay(ScriptApp.WeekDay.FRIDAY)
    .atHour(16)
    .create();
}

function weeklyBackup() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const stamp = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd');
  ss.getSheetByName('Marks').copyTo(ss).setName(`Backup ${stamp}`);
}

Next: quiz →

Check yourself

1. Your onOpen menu does not appear. What is the first thing to try?

2. In addItem('Count students', 'countStudents'), what is the second argument?

3. Which object tells onEdit which cell was changed?

4. onEdit runs when…

5. You need to send an email every Monday at 8 am. What do you use?

6. Why does createDailyTrigger delete old triggers first?