Creating custom menus in Google Sheets - Mechanics & Internals

Overview - Creating custom menus

What is it?

Creating custom menus in Google Sheets means adding your own menu items to the spreadsheet's menu bar. These menus can run special scripts or functions you write to automate tasks or add new features. This lets you tailor Google Sheets to your specific needs beyond the built-in options. You interact with these menus just like the default ones, but they do exactly what you want.

Why it matters

Without custom menus, you must manually run scripts or use complicated steps to automate tasks. Custom menus make your tools easy to access with a simple click, saving time and reducing errors. They help teams work faster and more consistently by putting important actions right where users expect them. This improves productivity and makes spreadsheets more powerful and user-friendly.

Where it fits

Before learning custom menus, you should know basic Google Sheets usage and have a simple understanding of Google Apps Script. After mastering custom menus, you can explore creating dialogs, sidebars, and more advanced automation with Apps Script to build interactive spreadsheet apps.

Mental Model

Core Idea

Custom menus are like adding your own buttons to the spreadsheet’s menu bar that run your special commands when clicked.

Think of it like...

Imagine your kitchen has a standard set of tools, but you add a special drawer with your favorite gadgets so you can cook faster and easier. Custom menus are that special drawer in your spreadsheet.

┌─────────────────────────────┐
│ File  Edit  View  Custom ▼  │
│                         ┌─────────────┐
│                         │ My Script 1 │
│                         │ My Script 2 │
│                         └─────────────┘
└─────────────────────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding Google Apps Script Basics

Concept: Learn what Google Apps Script is and how it connects to Google Sheets.

Google Apps Script is a simple coding language based on JavaScript that lets you add new features to Google Sheets. You write small programs called functions that can read or change your spreadsheet. These scripts run inside your Google Sheets file and can be triggered by menus, buttons, or events.

Result

You can open the script editor and write your first function that can do simple tasks like showing a message.

Understanding Apps Script is essential because custom menus rely on scripts to perform actions when clicked.

2

Foundation

Accessing the Script Editor

Concept: Learn how to open and use the script editor inside Google Sheets.

In your Google Sheet, click Extensions > Apps Script. This opens a new tab where you can write and save your scripts. The editor has a place to write code, save it, and run it. This is where you will create the functions your custom menu will call.

Result

You can write and save scripts linked to your spreadsheet.

Knowing where and how to write scripts is the first step to customizing your spreadsheet.

3

Intermediate

Writing a Simple Function for Menu

🤔Before reading on: Do you think a menu item can run any script function you write? Commit to yes or no.

Concept: Create a basic function that does something simple, like showing a popup message.

Write a function like this: function showMessage() { SpreadsheetApp.getUi().alert('Hello from custom menu!'); } This function shows a popup alert when called.

Result

When this function runs, a popup message appears in the spreadsheet.

Knowing how to write a function that interacts with the spreadsheet UI is key to making useful menu actions.

4

Intermediate

Adding a Custom Menu on Open

🤔Before reading on: Do you think the custom menu appears automatically or needs a special trigger? Commit to your answer.

Concept: Use the special onOpen() function to add your menu when the spreadsheet opens.

Write this code: function onOpen() { var ui = SpreadsheetApp.getUi(); ui.createMenu('Custom Menu') .addItem('Show Message', 'showMessage') .addToUi(); } This code adds a new menu called 'Custom Menu' with one item that runs showMessage().

Result

When you reload the spreadsheet, a new menu appears with your item.

Using onOpen() ensures your menu is always available when users open the sheet.

5

Intermediate

Linking Multiple Functions to Menu Items

🤔Before reading on: Can one custom menu have multiple items each running different functions? Guess yes or no.

Concept: Add several items to your custom menu, each calling a different function.

Extend onOpen() like this: function onOpen() { var ui = SpreadsheetApp.getUi(); ui.createMenu('Custom Menu') .addItem('Show Message', 'showMessage') .addItem('Clear Sheet', 'clearSheet') .addToUi(); } function clearSheet() { var sheet = SpreadsheetApp.getActiveSheet(); sheet.clear(); } Now your menu has two items doing different tasks.

Result

The menu shows two options; clicking each runs its function.

Menus can organize multiple useful commands, making your spreadsheet more powerful.

6

Advanced

Using Menus with Submenus and Separators

🤔Before reading on: Do you think custom menus can have submenus or separators? Guess yes or no.

Concept: Learn to organize menu items with submenus and separators for clarity.

You can add submenus and separators like this: function onOpen() { var ui = SpreadsheetApp.getUi(); var submenu = ui.createMenu('Submenu') .addItem('Sub Item 1', 'subItem1') .addItem('Sub Item 2', 'subItem2'); ui.createMenu('Custom Menu') .addItem('Show Message', 'showMessage') .addSeparator() .addSubMenu(submenu) .addToUi(); } function subItem1() { SpreadsheetApp.getUi().alert('Sub Item 1 clicked'); } function subItem2() { SpreadsheetApp.getUi().alert('Sub Item 2 clicked'); } This creates a submenu inside your custom menu.

Result

Your menu has a separator and a submenu with two items.

Organizing menus improves user experience, especially with many commands.

7

Expert

Dynamic Menus and Permissions Handling

🤔Before reading on: Can custom menus change based on sheet content or user permissions? Guess yes or no.

Concept: Create menus that change dynamically and handle permission prompts gracefully.

You can build menus that check conditions before adding items: function onOpen() { var ui = SpreadsheetApp.getUi(); var menu = ui.createMenu('Dynamic Menu'); var sheet = SpreadsheetApp.getActiveSheet(); if(sheet.getName() === 'Data') { menu.addItem('Process Data', 'processData'); } else { menu.addItem('Show Info', 'showInfo'); } menu.addToUi(); } function processData() { // Code that may require permissions } function showInfo() { SpreadsheetApp.getUi().alert('Not on Data sheet'); } Also, scripts that access user data may ask for permissions the first time they run. Design menus to avoid confusing users by only showing relevant options.

Result

Menu items appear or disappear based on sheet state; permission prompts happen only when needed.

Dynamic menus and thoughtful permission handling create professional, user-friendly spreadsheet tools.

Under the Hood

When a Google Sheet opens, it looks for a special function named onOpen() in its linked Apps Script. This function runs automatically and uses the SpreadsheetApp.getUi() service to add new menu items to the spreadsheet's menu bar. Each menu item is linked to a function name as a string. When a user clicks a menu item, Google Sheets calls the linked function in the script. The script runs on Google's servers and can interact with the spreadsheet, show dialogs, or perform other tasks.

Why designed this way?

Google designed custom menus to let users extend Sheets without changing the core app. Using onOpen() as a trigger ensures menus load fresh each time the sheet opens, keeping menus in sync with the script. Linking menu items by function names as strings keeps the interface simple and flexible. This design balances power and safety, as scripts run in a controlled environment and require user permission for sensitive actions.

┌───────────────┐
│ Google Sheet  │
│   Opens       │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ onOpen() runs │
│ in Apps Script│
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Add custom    │
│ menu to UI    │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ User clicks   │
│ menu item     │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Linked function│
│ runs on server │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does adding a custom menu automatically run your script functions? Commit yes or no.

Common Belief: Adding a custom menu runs all the linked functions immediately.

Reality: Custom menu functions only run when the user clicks the menu item, not when the menu is created.

Why it matters: Thinking scripts run on menu creation can cause confusion and errors when expecting immediate results.

Quick: Can you add custom menus that appear only for certain users without extra code? Commit yes or no.

Common Belief: Custom menus can automatically show different items for different users without scripting.

Reality: Menus are the same for all users unless you write code to detect user identity and adjust menus dynamically.

Why it matters: Assuming menus vary by user without code can lead to security or usability issues.

Quick: Do custom menus work instantly after saving scripts without reloading? Commit yes or no.

Common Belief: After saving your script, the custom menu updates immediately without reloading the sheet.

Reality: You must reload or reopen the spreadsheet to see changes to custom menus because onOpen() runs only at open.

Why it matters: Not reloading leads to confusion when menus don't appear or update as expected.

Quick: Can custom menus run functions that require authorization without user permission? Commit yes or no.

Common Belief: Custom menu functions run without asking for user permissions.

Reality: Functions that access sensitive data or services require user authorization before running.

Why it matters: Ignoring permission prompts can cause scripts to fail and frustrate users.

Expert Zone

1

Custom menus can be dynamically built based on spreadsheet content or user roles by checking conditions inside onOpen().

2

Menus added via onOpen() do not persist if the user reloads the sheet without re-running onOpen(), so always design for reloads.

3

Scripts linked to menu items run with the user's permissions, so understanding authorization scopes is critical for security.

When NOT to use

Avoid custom menus when you need real-time interaction or complex user input; instead, use dialogs or sidebars which provide richer interfaces. Also, if your automation runs without user interaction, consider time-driven triggers instead of menus.

Production Patterns

In professional sheets, custom menus often group related automation tasks like data import, cleanup, or report generation. Teams use them to standardize workflows, reduce errors, and provide easy access to scripts without exposing the script editor.

Connections

Event-driven programming

Custom menus rely on the onOpen() event trigger to run code when the spreadsheet opens.

Understanding event-driven programming helps grasp how scripts respond to user actions or sheet events automatically.

User interface design

Custom menus are a simple form of UI customization to improve user experience in spreadsheets.

Knowing UI design principles helps create menus that are intuitive, organized, and easy to use.

Command pattern (software design)

Each menu item acts like a command that triggers a specific function, similar to the command pattern in programming.

Recognizing this pattern clarifies how menus decouple user actions from code execution, enabling flexible and maintainable scripts.

Common Pitfalls

#1 Menu does not appear after adding code.

Wrong approach:

function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('My Menu')
.addItem('Say Hi', 'sayHi');
// Missing addToUi()
}
function sayHi() {
SpreadsheetApp.getUi().alert('Hi!');
}

Correct approach:

function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('My Menu')
.addItem('Say Hi', 'sayHi')
.addToUi();
}
function sayHi() {
SpreadsheetApp.getUi().alert('Hi!');
}

Root cause:

Forgetting to call addToUi() means the menu is created but never added to the spreadsheet UI.

#2 Expecting menu changes to show without reload.

Wrong approach:

// Edit onOpen() to add new menu items
// Save script
// Switch back to sheet and try to see new menu without reload

Correct approach:

// After saving script changes
// Reload or close and reopen the spreadsheet to trigger onOpen() and update menus

Root cause:

onOpen() runs only when the spreadsheet opens or reloads, so menu updates require a reload.

#3 Calling a non-existent function from menu item.

Wrong approach:

function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Menu')
.addItem('Do Task', 'nonExistentFunction')
.addToUi();
}

Correct approach:

function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Menu')
.addItem('Do Task', 'existingFunction')
.addToUi();
}
function existingFunction() {
SpreadsheetApp.getUi().alert('Task done');
}

Root cause:

Menu items must link to functions that exist and are spelled exactly; otherwise, clicking causes errors.

Key Takeaways

Custom menus let you add your own commands to Google Sheets’ menu bar for easy access to scripts.

You create menus by writing an onOpen() function that builds the menu and links items to script functions.

Menus only appear after reloading the spreadsheet because onOpen() runs at open time.

Each menu item runs a function only when clicked, not automatically when the menu is created.

Design menus thoughtfully to improve user experience and handle permissions smoothly.