Create a Custom Menu in Google Sheets

February 20, 2020 • Google Apps Script, Google Sheets • Published By Josh • 3 minute read

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
Creating A Custom Menu
Reviewing the Code
Using a Custom Menu

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

NOTE: The script editor can now be found under Extensions > Apps Script.

Create or open an existing spreadsheet in Google Sheets.
Navigate to Tools > Script Editor.
The script editor will include a starting function. Remove all the code and replace it with the code below.
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.