Google Sheets does not allow you to add buttons to the toolbar. In fact, there are no toolbar customization options available at all. But you can add buttons and sub-menus to the menu. You can easily link these with Apps Scripts and make them perform whatever actions you want.
If you need more flexibility, or more easily accessible buttons in your sheet, then there are several ways to do this on your sheet using drawings or checkboxes that act as buttons.
In this article, we’ll cover just how to create a new button, separator and sub-menu on the main menu and how to link these with Apps Script that simply pops up a message when you click them.
You add new menu items in Google Sheets entirely within Apps Script. First, let’s open up the Apps Script editor by using the ‘Extensions > Apps Script’ option on the main menu.
The Apps Script editor will open in a new window of your browser and should start with the code.gs file open and a new empty function called
In order to add menu items to your sheet, you need to create them when the sheet opens. We do this by creating a special function in our Apps Script called a simple trigger. There are several simple trigger functions available, but the one we want is called
onOpen. This is called every time you open a sheet.
Inside a new
onOpen function in code.gs is where we build our menu, and where we setup the functions that get called whenever we click on an item or sub-menu item on it.
Delete the default
myFunction code added by the Apps Script editor and replace it with this:
First, we add an onOpen function that creates a menu, adds an item to it, then a separator, and then creates and adds a sub-menu. Each time we add a menu item by calling
addItem we tell it the label of the menu item and also the name of an Apps Script function that will be called every time that menu item gets clicked.
The 2 functions that will be called,
doSomethingElse, each time we click their respective menu items just throw up a message to let you know they’ve worked.
Save your changes in the Apps Script editor by clicking the Save button on the toolbar or using Ctrl+s. Then go back to your Google Sheet and refresh the browser window. We need to refresh the window so that our
onOpen function will be called, otherwise our menu won’t get added.
NOTE: Make sure to Save your Apps Script project (Ctrl+s) and reload or re-open your sheet whenever you change your custom created menus, otherwise you won’t see them. They don’t refresh automatically when you change your Apps Script.