DEV Community

Cover image for Create a Custom Menu in Google Sheets
nightwolfdev
nightwolfdev

Posted on • Edited on • Originally published at nightwolf.dev

Create a Custom Menu in Google Sheets

Google Sheets includes many built-in menus that provide a wide range of functionality within the spreadsheet. When these built-in menus aren’t enough for your needs, you can use Google Apps Script to create your own custom menus!

Custom Menu Example

Google Sheets Custom Menu Example

A custom menu could provide all kinds of functionality. It will really depend on what’s needed. In this example, selecting each of the custom menu items will display a pop up message.

By the way, if you’re not comfortable with writing Javascript or don’t have the time to learn, check out the add-on store for Google Sheets. Maybe someone has already written the custom functionality you’re looking for!

Creating a Custom Menu

Creating a Custom Menu

  1. Create or open an existing spreadsheet in Google Sheets.
  2. Navigate to Tools > Script Editor.
  3. The script editor will include a starting function. Remove all the code and replace it with the code below.
  4. Navigate to File > Save. Give the script project a name and select Ok.
// Run when the spreadsheet is opened.
function onOpen() {
  var ui = SpreadsheetApp.getUi();

  ui.createMenu('Custom Menu')
    .addItem('Item 1', 'itemOne')
    .addItem('Item 2', 'itemTwo')
    .addSeparator()
    .addItem('Help', 'help')
    .addToUi();
}

// Run when menu option "Item 1" is selected.
function itemOne() {
  Browser.msgBox('You selected Item 1');
}

// Run when menu option "Item 2" is selected.
function itemTwo() {
  Browser.msgBox('You selected Item 2');
}

// Run when menu option "Help" is selected.
function help() {
  Browser.msgBox('You selected Help');
}
Enter fullscreen mode Exit fullscreen mode

Reviewing the Code

The best time to create a custom menu is when the spreadsheet first opens. Use the onOpen trigger, which is executed when the spreadsheet is first opened.

function onOpen()
Enter fullscreen mode Exit fullscreen mode

Create a reference to the spreadsheet’s user interface by calling the getUi method. Store that in a variable called ui.

var ui = SpreadsheetApp.getUi();
Enter fullscreen mode Exit fullscreen mode

Create a custom menu by calling createMenu, passing in the text that will appear in the custom menu.

ui.createMenu('Custom Menu')
Enter fullscreen mode Exit fullscreen mode

Add items to the custom menu by calling addItem. The first argument is the text that will appear for the menu item. The second argument is the function to call when the menu item is selected.

.addItem('Item 1', 'itemOne')
Enter fullscreen mode Exit fullscreen mode

When all items have been added to the menu, add the custom menu to the spreadsheet’s user interface by calling addToUi.

.addToUi();
Enter fullscreen mode Exit fullscreen mode

Using a Custom Menu

Using a Custom Menu

In order to see the custom menu in your spreadsheet:

  1. Make sure you saved the code!
  2. Select the onOpen function from the drop down.
  3. Select the Run icon to run the function.
  4. Switch to the spreadsheet and see your custom menu!

Visit our website at https://nightwolf.dev and follow us on Facebook and Twitter!


Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read full post →

Top comments (0)

Image of Docusign

🛠️ Bring your solution into Docusign. Reach over 1.6M customers.

Docusign is now extensible. Overcome challenges with disconnected products and inaccessible data by bringing your solutions into Docusign and publishing to 1.6M customers in the App Center.

Learn more