How to Add a Button to the Toolbar in Google Sheets

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 myFunction added.

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:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  var myMenu = ui.createMenu('My menu!');
  myMenu.addItem('Do something!', 'doSomething');
  myMenu.addSeparator();
  var subMenu = ui.createMenu('My sub-menu');
  subMenu.addItem('Do something else!', 'doSomethingElse');
  myMenu.addSubMenu(subMenu);
  myMenu.addToUi();
}

function doSomething() {
  SpreadsheetApp.getUi().alert('You clicked Do Something!');
}

function doSomethingElse() {
  SpreadsheetApp.getUi().alert('You clicked Do something else!');
}
Code language: JavaScript (javascript)

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, doSomething and 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.