Google Sheetsspreadsheet~15 mins

Creating custom menus in Google Sheets - Business Scenario Walkthrough

Choose your learning style9 modes available

Learn Why Deep Sheet Try Challenge Scenario Recall Dash

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.

Progress0 / 6 steps

Sample Data

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

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

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

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

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

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

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

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