Creating custom menus in Google Sheets - Business Scenario Walkthrough

Scenario Mode

👤 Your Role: You are a Google Sheets user managing a team project tracker.

📋 Request: Your manager wants a quick way to add common project status updates using a custom menu in the sheet.

📊 Data: You have a sheet with project names in column A and their current status in column B.

🎯 Deliverable: Create a custom menu with options to set the status of selected projects to 'Not Started', 'In Progress', or 'Completed' with one click.

Sample Data

Project Name	Status
Website Redesign	Not Started
Mobile App	In Progress
Marketing Campaign	Completed
Customer Survey	Not Started
Product Launch	In Progress

1

Step 1: Open the Google Sheets script editor to write a script for the custom menu.

In the sheet, click Extensions > Apps Script to open the script editor.

Expected Result

A new script editor tab opens where you can write your code.

2

Step 2: Write a function to create the custom menu with three options: 'Set Not Started', 'Set In Progress', and 'Set Completed'.

function onOpen() { const ui = SpreadsheetApp.getUi(); ui.createMenu('Project Status') .addItem('Set Not Started', 'setNotStarted') .addItem('Set In Progress', 'setInProgress') .addItem('Set Completed', 'setCompleted') .addToUi(); }

Expected Result

When the sheet opens, a new menu named 'Project Status' appears with the three status options.

3

Step 3: Write a function to set the status of the selected rows in column B to 'Not Started'.

function setNotStarted() { const sheet = SpreadsheetApp.getActiveSheet(); const range = sheet.getActiveRange(); const numRows = range.getNumRows(); for (let i = 0; i < numRows; i++) { const row = range.getRow() + i; sheet.getRange(row, 2).setValue('Not Started'); } }

Expected Result

When 'Set Not Started' is clicked, the status cells in column B for the selected rows update to 'Not Started'.

4

Step 4: Write similar functions for 'In Progress' and 'Completed' statuses.

function setInProgress() { const sheet = SpreadsheetApp.getActiveSheet(); const range = sheet.getActiveRange(); const numRows = range.getNumRows(); for (let i = 0; i < numRows; i++) { const row = range.getRow() + i; sheet.getRange(row, 2).setValue('In Progress'); } } function setCompleted() { const sheet = SpreadsheetApp.getActiveSheet(); const range = sheet.getActiveRange(); const numRows = range.getNumRows(); for (let i = 0; i < numRows; i++) { const row = range.getRow() + i; sheet.getRange(row, 2).setValue('Completed'); } }

Expected Result

Clicking 'Set In Progress' or 'Set Completed' updates the status cells in column B for the selected rows accordingly.

5

Step 5: Save the script and reload the Google Sheet to see the custom menu.

Click the save icon in the script editor, then reload the Google Sheet tab.

Expected Result

The 'Project Status' menu appears in the menu bar with the three status options.

6

Step 6: Test the custom menu by selecting one or more project rows and choosing a status from the menu.

Select rows in the sheet, then click Project Status > Set Completed (or other options).

Expected Result

The status cells in column B for the selected rows update to the chosen status.

Final Result

Project Status Menu
-------------------
| Project Status |
|----------------|
| Set Not Started |
| Set In Progress |
| Set Completed   |
-------------------

Sheet Example:
+-------------------+--------------+
| Project Name      | Status       |
+-------------------+--------------+
| Website Redesign  | Not Started  |
| Mobile App       | Completed    |
| Marketing Campaign| Completed    |
| Customer Survey  | In Progress  |
| Product Launch   | Completed    |
+-------------------+--------------+

✓Custom menus let you quickly update multiple project statuses with one click.

✓This saves time compared to typing statuses manually.

✓You can select any rows and apply the status update easily.

Bonus Challenge

Add a menu option to clear the status cells for selected projects.

Show Hint

Create a new function that sets the status cells in column B to an empty string for the selected rows and add it to the custom 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