How to Create a Button in Google Sheets (3 ways)

There are 3 ways to make a button that performs an action in Google Sheets. You can create a drawing that looks like a button, and runs Apps Script when clicked, or you can use a tick-box that will run a script whenever it gets ticked, and then un-tick itself to appear to act like a button. The 3rd way is to add a custom option to the menu.

No solution is perfect. They are each suited for different situations. All options will require you to write a little Apps Script that ‘does something’ when your button, checkbox or menu item gets clicked. Examples of this are below…

Drawings are easy to style, you have a lot of design options, and they are simple to understand and they show you on the sheet when they are running. But they can be very tedious to create and maintain if you need to make a lot of them. They also won’t work on mobile sheets, so you can’t use them on your phone. They are best suited to sheets that only need 1 or 2 buttons to perform actions. And, because they float above data, sheets that don’t change their design a lot, so you don’t have to keep moving them around to see what’s underneath.

Using a checkbox or tick-box as a button is our preferred method. This option will work on your phone and your desktop. You can easily create a button on every row of a sheet. The Apps Script to react to them is only slightly more complex than using a drawing. The only downsides to using checkboxes is that you need a column or 2 in your sheet to put them in, and they don’t show a message on the sheet when they are running.

Of course, there is also the 3rd option, but it isn’t really a button IN a sheet. If you need buttons that don’t sit next to, or over your cells, you can always add custom buttons to the menu.

For examples of how to make things happen with buttons checkout our tutorial on how to make a clear contents button.

Now, let’s look at how to create a drawing and a checkbox button IN your sheet and make them perform a simple action using Apps Script.

How to make a drawing button in google sheets

Here’s how to create a drawing that acts like a button in Google Sheets:

1. Use the menu option Insert > Drawing to create a drawing for your sheet
2. Select text-box from the drawing toolbar and draw a small box on your drawing canvas
3. Type in some text for your button to display
4. Click on fill color and set a color for your button
5. Click on more options to open the text options
6. Set a text color and text alignment (centre) for your button text to distinguish it as a button
7. Click Save and Close
8. Right-click your button and click the three dot menu that appears in the top right of it
9. Select Assign a script from the menu
10. Enter the name of the function (without parenthesis) in your sheet Apps script you want to execute whenever this button gets clicked, i.e. clearSheet and not clearSheet()

Now every time you want to execute that Apps Script function, all you need to do is click the drawing! Here are some screenshots of those steps, in case you get stuck.

For an example of how to write Apps Scripts that empties a sheet range, read ‘How to make a clear contents button‘.

If you need to adjust the position of the button, or the script assigned to it, just right click on it to select it, rather than click it.

Now, every time you click that drawing, your script will execute. But be aware…

Drawing buttons won’t work on mobile

If you create a drawing in Google Sheets and assign a script to it, this will only work on your desktop. If you open your file in the mobile version of Google Sheets, clicking the button will just select it and no Apps Script will trigger. So it will do nothing. We recommend using tick boxes to simulate buttons instead if you need them to work on both your phone AND your desktop.

How to make a checkbox button in google sheets

To create a tick box button in Google Sheets, first select the cell or cells that you want to contain tick boxes and then use the Insert > Tick box menu item to add tick boxes to those cells.

So now you have cells which contain tick-boxes. How do you make something happen when you click on one?

We need to add some Apps Script for this. So open the Apps Script editor using the Extensions > Apps Script option on the menu.

Once the editor is open, paste the following code into it. I’ll explain what it does in a moment.

//onEdit is called automatically every time a cell is edited
function onEdit(e) {

  //check which sheet was edited
  if(e.range.getSheet().getName() == 'Sheet1') {

    //get the location that was edited in A1 notation so it's easy to read
    var editRange = e.range.getA1Notation();

    //check if our tick box cell was ticked
    if (editRange == "C1" && e.value) {

      //DO SOMETHING HERE!!
      
      e.range.uncheck(); //uncheck the tick box to provide feedback
    } 
  }
}Code language: JavaScript (javascript)

If you add a function defined as function onEdit(e) to your Apps Script, it will trigger every time a cell gets edited. You don’t have to do anything to make this work, just have a valid function in your Apps Script. It’s called a simple trigger.

This function has a parameter called e that tells us which cell or range of cells you edited. Because this function runs every time you edit any cell on a sheet, you need to make sure you handle it quickly and efficiently. The first thing we do is check if it is our sheet “Sheet1” that was edited, then if it was our tick box cell that was ticked (“C1” in the example).

If both are a match, then we need to do something. You can replace //DO SOMETHING HERE!! with your code, for example, you may want to clear a specific row on your sheet.

Hopefully, you can understand this code and see the parts we’ve highlighted in bold that you’d need to change for different button locations and sheet names.

How to create a row button for each row

Creating a button for each row in a spreadsheet is possible using tick box buttons. You can select a column and then use the Insert > Insert Tick box option from the menu to create a tick box column. You then need to use Apps Script and create a function that listens to cell edits on the sheet, checks which row got edited, and react appropriately.

We explain how to create a checkbox button above. With a basic knowledge of Apps Script, you should be able to expand that example to check which row and column a user ticked. To get the row and column being checked, we’d use the following inside onEdit:

var editedColumn = e.range.getColumn();
var editedRow = e.range.getRow();Code language: JavaScript (javascript)

How to create a link button

You can style a Google Sheets drawing to look like a hyperlink but just using underlined text of any color you choose. You can also assign Apps Script to the drawing so that every time you click it, something happens. However, to create links to other sheets or documents in your Google Drive, you need to use the HYPERLINK formula.