Creating custom menus in Google Sheets - Complete Walkthrough

Introduction

Custom menus let you add your own menu options to Google Sheets. This helps you run special tasks or scripts easily without typing commands every time.

When you want to run a script with one click instead of opening the script editor.

When you share a sheet and want others to use your custom tools easily.

When you have repetitive tasks like formatting or data cleanup and want a quick button.

When you want to organize multiple scripts under one menu for easy access.

When you want to add special functions that Google Sheets does not have by default.

Steps

Step 1: Open

- Google Sheets file

Your spreadsheet is ready for editing

Step 2: Click

- Extensions menu > Apps Script

The Apps Script editor opens in a new tab

Step 3: Delete any code in the script editor and type

- Apps Script editor

You have a blank script to write your custom menu code

💡 Use the example code below to create a simple custom menu

Step 4: Type the following code

- Apps Script editor

Code defines a custom menu and a function to run

💡 This code adds a menu named 'My Menu' with one item 'Say Hello'

Step 5: Click

- Save icon in Apps Script editor

Your script is saved

Step 6: Close

- Apps Script editor tab

Return to your Google Sheets file

Step 7: Reload

- Google Sheets tab

The new custom menu appears in the menu bar

Step 8: Click

- New custom menu (e.g., 'My Menu') > menu item (e.g., 'Say Hello')

The script runs and shows a popup message

Before vs After

Before

Google Sheets menu bar shows only default menus like File, Edit, View

After

Google Sheets menu bar shows a new menu named 'My Menu' with a clickable item 'Say Hello'

Settings Reference

onOpen function

📍 Apps Script editor

Runs automatically when the spreadsheet opens to add your custom menu

Default: No custom menu

Menu name

📍 Code inside onOpen function

The name shown in the Google Sheets menu bar

Default: No menu

Menu item name

📍 Code inside onOpen function

The name of the clickable item inside your custom menu

Default: No menu item

Function to run

📍 Code inside onOpen function

The script function that runs when the menu item is clicked

Default: No function

Common Mistakes

Not naming the function 'onOpen' to create the menu

Google Sheets only runs the onOpen function automatically when the sheet opens

Always name your menu creation function 'onOpen' so it runs on file open

Forgetting to reload the spreadsheet after saving the script

The custom menu only appears after the sheet reloads and runs onOpen

Reload the Google Sheets tab after saving your script to see the menu

Using a menu item function that is not defined in the script

Clicking the menu item will cause an error if the function does not exist

Define all functions you link to menu items in the script

Summary

Custom menus let you add your own menu options to Google Sheets for easy script access.

You create custom menus by writing an onOpen function in the Apps Script editor.

Remember to reload your sheet after saving the script to see the new menu.

Practice

1. What is the main purpose of creating a custom menu in Google Sheets using Apps Script?

easy

A. To change the sheet's background color automatically

B. To add your own buttons for easy access to script functions

C. To protect cells from editing

D. To create charts from data automatically

Solution

1. Step 1: Understand what custom menus do

   Custom menus let you add buttons in the Google Sheets menu bar that run your scripts.

2. Step 2: Identify the main use

   They provide easy access to script functions without opening the script editor.

3. Final Answer:

   To add your own buttons for easy access to script functions -> Option B

4. Quick Check:

   Custom menus = easy script access [OK]

Hint: Custom menus add buttons to run scripts easily [OK]

Common Mistakes:

• Thinking custom menus change sheet colors automatically
• Confusing custom menus with cell protection
• Assuming custom menus create charts

2. Which of the following is the correct way to start the function that adds a custom menu when the Google Sheet opens?

easy

A. function onOpen() { ... }

B. function addMenu() { ... }

C. function onStart() { ... }

D. function openMenu() { ... }

Solution

1. Step 1: Recall the special trigger function name

   The function that runs automatically when the sheet opens must be named onOpen().

2. Step 2: Check the options

   Only onOpen() is the correct trigger name recognized by Google Sheets.

3. Final Answer:

   function onOpen() { ... } -> Option A

4. Quick Check:

   Trigger function = onOpen() [OK]

Hint: Use onOpen() to run code when sheet opens [OK]

Common Mistakes:

• Using wrong function names like onStart or openMenu
• Not using the exact name onOpen
• Thinking addMenu runs automatically

3. Given this Apps Script code snippet inside onOpen():

SpreadsheetApp.getUi()
  .createMenu('My Menu')
  .addItem('Say Hello', 'sayHello')
  .addToUi();

What happens when the Google Sheet is opened?

medium

A. A new menu named 'My Menu' appears with an item 'Say Hello' that runs the sayHello function

B. The sheet background changes to say 'Hello'

C. A popup appears saying 'Hello' automatically

D. Nothing happens until you run the script manually

Solution

1. Step 1: Understand the code's purpose

   The code creates a new menu called 'My Menu' in the Google Sheets UI.

2. Step 2: Analyze the menu item

   The menu has one item labeled 'Say Hello' that runs the function named 'sayHello' when clicked.

3. Final Answer:

   A new menu named 'My Menu' appears with an item 'Say Hello' that runs the sayHello function -> Option A

4. Quick Check:

   createMenu + addItem = new menu item [OK]

Hint: createMenu + addItem adds menu and function link [OK]

Common Mistakes:

• Thinking the script changes sheet colors automatically
• Expecting a popup without clicking menu
• Assuming nothing happens without manual run

4. You wrote this onOpen() function but the custom menu does not appear:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Tools')
    .addItem('Run Task', 'runTask')
}

What is the error preventing the menu from showing?

medium

A. addItem should be addMenu

B. Function name should be onStart()

C. Menu name 'Tools' is invalid

D. Missing call to addToUi() at the end

Solution

1. Step 1: Check the menu creation chain

   The code creates a menu and adds an item but does not call addToUi().

2. Step 2: Understand addToUi() role

   This method is required to actually add the menu to the Google Sheets UI.

3. Final Answer:

   Missing call to addToUi() at the end -> Option D

4. Quick Check:

   addToUi() adds menu to sheet [OK]

Hint: Always end menu chain with addToUi() [OK]

Common Mistakes:

• Using wrong function name instead of onOpen
• Thinking menu name is restricted
• Confusing addItem with addMenu

5. You want to create a custom menu with two items: 'Start Process' running startProcess and 'Stop Process' running stopProcess. Which code snippet correctly creates this menu inside onOpen()?

hard

A. function onOpen() {
SpreadsheetApp.getUi()
.createMenu('Process Control')
.addItem('Start Process')
.addItem('Stop Process')
.addToUi();
}

B. function onOpen() {
SpreadsheetApp.getUi()
.createMenu('Process Control')
.addItem('Start Process', 'startProcess')
.addToUi()
.addItem('Stop Process', 'stopProcess');
}

C. function onOpen() {
SpreadsheetApp.getUi()
.createMenu('Process Control')
.addItem('Start Process', 'startProcess')
.addItem('Stop Process', 'stopProcess')
.addToUi();
}

D. function onOpen() {
SpreadsheetApp.getUi()
.createMenu('Process Control')
.addItem('Start Process', startProcess)
.addItem('Stop Process', stopProcess)
.addToUi();
}

Solution

1. Step 1: Check method chaining order

   The menu is created, then two items are added with correct labels and function names as strings.

2. Step 2: Verify addToUi() is last

   The chain ends with addToUi() to add the menu to the UI.

3. Step 3: Confirm function names are strings

   Function names must be strings, not bare identifiers.

4. Final Answer:

   Code snippet D correctly creates the menu with two items and adds it to the UI -> Option C

5. Quick Check:

   Chain addItem with function names as strings, end with addToUi() [OK]

Hint: Chain addItem calls, end with addToUi() [OK]

Common Mistakes:

• Calling addToUi() before all addItem calls
• Passing function names without quotes
• Missing function names in addItem