Create a Custom Menu in Google Sheets
Tags: googleappsscript, googlesheets, javascript
Published: 2020-12-29 14:51:13

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](https://dev-to-uploads.s3.amazonaws.com/i/9m1xqfsxoui5skjzjo7h.png)

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](https://developers.google.com/apps-script/guides/sheets/functions#getting_a_custom_function_from_the_add-on_store). Maybe someone has already written the custom functionality you’re looking for!

## Creating a Custom Menu

![Creating a Custom Menu](https://dev-to-uploads.s3.amazonaws.com/i/9ohrpj8sakjaqspiwjgr.png)

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.

```javascript
// 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.

```javascript
function onOpen()
```

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

```javascript
var ui = SpreadsheetApp.getUi();
```

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

```javascript
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.

```javascript
.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**.

```javascript
.addToUi();
```

## Using a Custom Menu

![Using a Custom Menu](https://dev-to-uploads.s3.amazonaws.com/i/djfg9mx65pawqquiz5n2.png)

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](https://nightwolf.dev) and follow us on [Facebook](https://www.facebook.com/NightWolfDevelopment) and [Twitter](https://twitter.com/nightwolfdev)!
---