Automation and triggers Updated November 2026

Dialogs, prompts and sidebars

SpreadsheetApp.getUi() gives alert(message) for a popup, prompt(title, message, buttons) for text input (read response.getResponseText() and getSelectedButton()), and showModalDialog or showSidebar for custom HTML built with HtmlService. In HTML, google.script.run.serverFunction(args) calls back into the script.

Syntax

const ui = SpreadsheetApp.getUi(); ui.alert(msg) | ui.prompt(title, msg, ui.ButtonSet.OK_CANCEL) | ui.showModalDialog(html, title) | ui.showSidebar(html)

Part	What it is
`ui.ButtonSet`	OK, OK_CANCEL, YES_NO, YES_NO_CANCEL
`response.getSelectedButton()`	ui.Button.OK, CANCEL, YES, NO or CLOSE
`response.getResponseText()`	Text typed into a prompt
`HtmlService.createHtmlOutput(html)`	HTML to show; setWidth and setHeight for dialogs

Examples

const ui = SpreadsheetApp.getUi();
const res = ui.alert("Delete all rows?", ui.ButtonSet.YES_NO);
if (res === ui.Button.YES) SpreadsheetApp.getActiveSheet().clearContents();

Result: A Yes No popup; the sheet is cleared only on Yes

const res = ui.prompt("Report", "Enter the month (YYYY-MM):", ui.ButtonSet.OK_CANCEL);
if (res.getSelectedButton() === ui.Button.OK) {
  Logger.log("Month: " + res.getResponseText());
}

Result: Month: 2026-11

// Code.gs
function showSidebar() {
  SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile("Sidebar").setTitle("Tools"));
}
function getRowCount() { return SpreadsheetApp.getActiveSheet().getLastRow(); }
// Sidebar.html
// <button onclick="google.script.run.withSuccessHandler(n => document.getElementById('out').textContent = n).getRowCount()">Count</button><div id="out"></div>

Result: Clicking Count in the sidebar shows the last row number

Common mistakes

Cannot call SpreadsheetApp.getUi() from this context: the function ran from a trigger or the editor without an open sheet; dialogs need a user with the sheet open.
The prompt returns an empty string on Cancel: check getSelectedButton before using the text.
google.script.run returns undefined: it is asynchronous; use withSuccessHandler to receive the value instead of assigning the call result.
The HTML file is not found: createHtmlOutputFromFile takes the file name without .html and the file must be in the same project.

Practice Dialogs, prompts and sidebars

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

Timeout wrapperReturn a Promise that settles like `promise` if it settles within `ms` milliseconds. Otherwise reject with `new Error("Timed out")` at `ms`. Clear your timer once the result is known, so nothing is left pending. Tests run on a fake clock: `setTimeout`, `clearTimeout` and `Date.now` are simulated, so waits are instant and exact.
DebounceReturn a debounced version of `fn`: calling it schedules `fn` to run `wait` ms later with the latest arguments, and every new call before then restarts the wait. So a burst of calls runs `fn` once, after the burst goes quiet. Tests run on a fake clock: `setTimeout`, `clearTimeout` and `Date.now` are simulated, so waits are instant and exact.

Questions

Can I show a dialog from a custom function or time trigger?

No. Only functions started by a user in the open spreadsheet (menu, button, editor) can show UI. Use toast for a brief non-blocking message or email for triggers.

How do I pass data from the sheet into the HTML?

Call a server function from the HTML with google.script.run, or build the HTML with HtmlService.createTemplateFromFile and set template variables before evaluate().

onOpen and custom menusAdd a custom menu to Google Sheets with Apps Script onOpen: createMenu, addItem, separators and submenus that run your functions, and why a menu may hide.
Deploy as a web appDeploy a Google Apps Script as a web app: write doGet and doPost, return HTML or JSON, read query and POST parameters, set who runs it and who can access it.
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.
try, catch and finallyHandle errors in Google Apps Script with try catch finally: catch exceptions from sheets, email and APIs, read err.message and err.stack, log and rethrow.