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

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

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');
}

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()

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();

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

ui.createMenu('Custom Menu')

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')

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

.addToUi();

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!