Custom "Better Sheets tools" menu
onOpen adds menu items like "Refresh dashboard" and "Email weekly summary" that call named functions when clicked.
What is the onOpen trigger in Google Sheets?
The onOpen trigger runs an Apps Script function automatically when someone opens the spreadsheet. It is the standard way to add custom menus, show instructions, or refresh lightweight cache without asking the user to run a macro first. Name the function onOpen or bind it as an installable open trigger.
When to use it
Use onOpen to register a custom menu, set up sidebar launchers, warm a small config range, or show a one-time welcome note for editors. It sets the stage before anyone clicks Run.
When to skip it
Skip onOpen for heavy imports, bulk API syncs, or anything that must run while the file is closed. Users notice slow opens. Use time-driven triggers or a manual menu item for long jobs.
How it works
-
1
Create a function named onOpen in Apps Script (capital O is required for the simple trigger).
-
2
Inside onOpen, call SpreadsheetApp.getUi().createMenu() to add items that call other functions.
-
3
Save the project and reload the spreadsheet to test; the menu appears after the sheet finishes opening.
-
4
{:"Keep onOpen fast"=>"defer real work to functions the user chooses from the menu."}
-
5
For stricter permissions, add an installable onOpen trigger from the Triggers page in Apps Script.
-
6
Document menu labels so teammates know which action is safe on production data.
Examples in Google Sheets
Show setup checklist once
onOpen reads a hidden Settings cell; if blank, sets a friendly note in A1 reminding editors to pick their region from a dropdown.
Sidebar launcher
A menu item calls showSidebar() which opens HtmlService UI for a form that writes to the sheet.
Build this without starting from a blank cell
Use a Better Sheets tool for onOpen trigger, then watch a walkthrough when you want the full pattern.
Better Sheets resources
Common mistakes
- Naming the function onopen with lowercase o, so the simple trigger never fires.
- Running a five-minute sync inside onOpen, which makes every open feel broken.
- Assuming onOpen runs for viewers; viewers often cannot run scripts that need edit access.
- Forgetting to authorize the script once as an editor before expecting menus for the whole team.
- Creating duplicate menus on every open without checking if the menu already exists (rare but confusing in tests).
Frequently asked questions
- Does onOpen run for viewers?
- View-only users may not get custom menus or full script behavior. Test with the same role your audience has.
- onOpen vs installable open trigger?
- Simple onOpen is easy but limited. Installable open triggers can run with broader authorization when configured.
- Can onOpen modify data automatically?
- It can, but automatic writes on every open surprise users. Prefer menus for destructive actions.
- Why is my menu missing?
- Check the function name, script errors in Executions, and whether you are in a copy that lost the bound script.
- Does onOpen run on mobile?
- Mobile Sheets has limited Apps Script UI support. Do not rely on custom menus for mobile-only workflows.
- Can onOpen open a sidebar automatically?
- You can, but auto sidebars annoy power users. A menu item is usually better UX.
- How do I debug onOpen?
- Add Logger.log at the top, reload the file, then view Executions in Apps Script.
- Is onOpen the same as opening a web app?
- No. onOpen is tied to the spreadsheet UI. Web apps use doGet and their own URL.
Related Tutorials
Watch how onOpen trigger works
Activate A Certain Sheet When Opening a Spreadsheet
What Can You Automate in Google Sheets? Every single trigger available to Google Sheet users
Spreadsheet Automation 101 Lesson 1: onOpen() Trigger - Custom Menu
OnInstall to onOpen
Add-on menu on open
Related blog posts
Guides that explain onOpen trigger in more depth.
What Can You Automate in Google Sheets? Every single trigger available to Google Sheet users
Make your data tasks simpler & efficient. Discover the power of automation in Google Sheets with a comprehensive guide that covers everything from triggers to scripts.
Read post →Explore Coding in Google Sheets
Did you know you can actually code in Google Sheets?
Read post →Automate Row Deletion in Google Sheets with Apps Script
In this post, we'll cover how to remove rows automatically in Google Sheets using Apps Script, ensuring you save time and streamline your spreadsheet management. You're probably already familiar with the manual way of right-clicking and deleting rows, but there's a more efficient way to handle this—automation! Manual versus Semi-Automatic Deletion You may know that you can manually delete rows in Google Sheets by right-clicking and selecting "Delete row." You can even select multiple rows...
Read post →Related glossary terms
onEdit trigger
The onEdit trigger runs a script automatically when someone changes a cell in your spreadsheet. It is one of the simplest ways to make Sheets react to user input without clicking a custom menu. You define the function in Apps Script and bind it to the onEdit event for that file.
Read guide →Apps Script triggers
Triggers tell Apps Script when to run a function: on edit, on open, on a schedule, or when a form is submitted. Simple triggers use reserved names like onEdit. Installable triggers are created in the Apps Script Triggers page and can do more, including running as a specific user or on a timer.
Read guide →Google Sheets macros
A macro records your clicks and keystrokes in Google Sheets, then saves them as an Apps Script function you can run again from the Extensions menu or a keyboard shortcut. Macros are the fastest on-ramp to automation when your task is a fixed sequence of formatting or edits.
Read guide →Script Editor
The Script Editor is the Apps Script workspace inside your spreadsheet where you write JavaScript that talks to Sheets, Gmail, and other Google services. You open it from Extensions > Apps Script. Every function you save can be run manually, bound to a trigger, or linked from a custom menu.
Read guide →Done reading about onOpen trigger?
Membership unlocks 636+ tutorials, unlimited generators, and every template. Practical lessons. Zero fluff.
Need this once
Jump to a free tool or a single tutorial for this topic.
Learning Sheets for real
Unlock the full library, generators, and templates with membership.