How to Assign a Script to a Button in Google Sheets

Dave | December 7, 2022 |

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.

The drawing editor with the fill color, font and alignment toolbar buttons highlighted
Making a drawing 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‘.

The drawing context menu opened with 'Assign a script' highlighted
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.

The assign a script window
Don’t enter parenthesis

Hit OK. And now, every time you click your button, your script should run and you will see a notification of it running.

The Google Sheets running script popup notification
Running script feedback

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.

A custom menu item
Our custom menu will call doSomething
Dave

I'm a Google Product Expert and mainly post on the subject of Google Sheets.

I've been a software engineer for over 20 years. The constant through all that time? Spreadheets.... Even though I can write programs I use them.... a lot. Sometimes there's just no better alternative!

Find out more about me here.

Leave a Comment