If you need to assign a script to a button in Google Sheets, you’ve probably created a drawing and need to know how to Assign a script to it that does something. You need to right click on the drawing and open the three dot menu, then select Assign a script from the drawing menu.
There are 3 types of buttons you can create in Google Sheets. Clickable drawings, menu items or checkboxes. You assign a script to a custom menu button using Apps Script.
The third option, using checkboxes for buttons, is one that we like to use and one that’s great when you need to assign buttons to every row, or put them in a cell. It’s also the option that works on mobile sheets. You don’t need to lose your functionality when you open your sheets on your phone.
How to assign a script to a button (drawing)
If your button floats above the cells of your sheet, then you must have created it as a drawing. Most likely, by adding a text box, and altering the fill-color, text alignment and text color in the drawing editor to make it look like a button.
You don’t assign scripts to buttons inside the drawing editor. So make sure you’ve closed it but hitting Save and Close. Then, when your drawing is visible on your sheet, you need to select it. If a drawing already has a script assigned, when you click it, it will run the script. To select the button right-click on it.
When you select your button, it will show the blue resizing border with handles. It will also show you a small three dot menu icon in the top right corner. Click these three dots to open the drawing menu and select ‘Assign a script‘.
This will open the script assignment window that allows you to enter the name of a function in your Apps Script that gets executed every time you click the button.
NOTE: Just enter the name of your function without parenthesis (), i.e. myButtonDoesThis.
Hit OK. And now, every time you click your button, your script should run and you will see a notification of it running.
How to assign a script to a menu item
If you don’t like your buttons floating over the top of cells, you may decide you want to create new menu items to run your functions instead. If you do this, then you assign your script functions to the button when you create them.
Look at this sample code that creates a custom menu with a single button that just shows an alert when clicked.
function onOpen() {
var ui = SpreadsheetApp.getUi();
var myMenu = ui.createMenu('My menu!');
myMenu.addItem('Do something!', 'doSomething');
myMenu.addToUi();
}
function doSomething() {
SpreadsheetApp.getUi().alert('You clicked Do Something!');
}
Code language: JavaScript (javascript)You can see that when we call addItem on the menu, we pass in the label of the menu item and the name of the function that will be called.
doSomething