Automation and triggers Updated November 2026

onOpen and custom menus

A function named onOpen runs when the spreadsheet opens; inside it, SpreadsheetApp.getUi().createMenu("Tools").addItem("Clean data", "cleanData").addToUi() adds a menu that calls cleanData when clicked. The second argument of addItem is the function name as a string. Reload the sheet to see the menu.

Syntax

SpreadsheetApp.getUi().createMenu(title).addItem(label, functionName).addSeparator().addSubMenu(menu).addToUi()

Part	What it is
`createMenu(title)`	Top-level menu name shown after Extensions
`addItem(label, functionName)`	Menu entry; functionName must be a global function in the project
`addSubMenu(menu)`	Nest another createMenu result
`addSeparator()`	Horizontal line between items

Examples

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("Reports")
    .addItem("Refresh totals", "refreshTotals")
    .addItem("Email summary", "emailSummary")
    .addToUi();
}

Result: A Reports menu with two entries appears in the sheet menu bar

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu("Admin")
    .addSubMenu(ui.createMenu("Export").addItem("To PDF", "exportPdf").addItem("To CSV", "exportCsv"))
    .addSeparator()
    .addItem("Settings", "openSettings")
    .addToUi();
}

Result: Admin menu with an Export submenu and a Settings item

function onOpen(e) {
  SpreadsheetApp.getUi().createAddonMenu().addItem("Run", "run").addToUi();
  SpreadsheetApp.getActiveSpreadsheet().toast("Menu ready");
}

Result: Entry under Extensions plus a small toast in the corner

Common mistakes

Menu does not appear: onOpen is not spelled exactly, or the file was not reloaded; refresh the browser tab after saving.
Script function not found: refreshTotals when clicking: the item's function name is misspelled or the function is not global (it sits inside another function).
The menu shows for you but not for a viewer: simple triggers like onOpen run only for users with edit access.
onOpen cannot call GmailApp or UrlFetchApp: simple triggers have limited authorization; keep onOpen for UI and put the work in the menu functions.

Practice onOpen and custom menus

These drills check your answer on live data, free, no account needed.

Run only onceReturn a function that calls `fn` the first time it is called, with that call's arguments, and returns `fn`'s result. Every later call returns that same first result without calling `fn` again, even when the result was `undefined` or `0`.
Pipe functions togetherReturn a function that passes its input through `fns` from left to right: `pipe(f, g, h)(x)` is `h(g(f(x)))`. The first function may take several arguments; the others get one. With no functions, the result returns its first argument unchanged.

Questions

Can a menu item take arguments?

No, menu items call a function with no arguments. Read what you need from the sheet, a prompt (ui.prompt) or Script Properties inside the function.

How do I add a button instead of a menu?

Insert, Drawing, draw a shape, then right-click it, Assign script, and type the function name. Menus are simpler to maintain.

onEdit triggerUse the onEdit trigger in Google Apps Script: the event object with range, value and oldValue, limiting it to one sheet or column, timestamps, installable.
Dialogs, prompts and sidebarsShow dialogs in Google Sheets with Apps Script: ui.alert for messages, ui.prompt for text input, Yes No buttons, and HtmlService dialogs and sidebars.
Installable triggersInstallable triggers in Google Apps Script: how they differ from simple triggers, creating them from the UI or ScriptApp.newTrigger, onChange, onFormSubmit.
Your first Apps ScriptWrite and run your first Google Apps Script in Sheets: open the editor, write a function that reads and writes cells, run it, authorize it and read the log.