Google Apps Script: How to Connect a Button to a Function in Google Sheets

Google Apps Script: UI, Google Sheets

Ever wanted to use a button in Google Sheets to execute a function in Google Apps Script? Well, guess what? The process is super easy.

  1. Write your function in Google Apps Script editor.
  2. Create an image or upload an image and add it to your Google Sheet.
  3. Right-click the image, select the ellipse and Assign script.
  4. Type in your function name.
  5. Click the button to test. You should be up and running.

The Example

Let’s look at a super easy example of connecting a button to a function.

Imagine we want to have a button that, when clicked, reads the selected cell and displays it as an alert.

We’ll start with the code first.

The Code

Line 1, sets the function, button(). Our first variable, ui, gets the User Interface class (getUi()). The second variable, cell, then calls the SpreadsheetApp service again all for the value of the currently selected cell.

Now that our variables are set, we call the ui class and send an alert on our screen. Inside the alert (Line 5) we add some text plus our cell value.

When the function is run, you will need to accept permissions for the first time. Then, you will see that whatever cell you clicked, the function will read that cell value and display it as an alert on your screen.

With the function complete, we’ll move over to create the button.

Creating the Button

To create the button, we are going to make use of Google’s in-built drawing tool. You can access it by selecting Insert>>Drawing… from the menu bar. Before selecting drawing, make sure that you have selected the cell you want your button places in or around. All images in Google Sheets reference a cell location.

Google sheets insert drawing

In the Drawing menu bar, click the menu with the circle over the square, then select shape and pick the bevel shape. This one looks most like a traditional button and colour grades accordingly.

Google Sheets bevel shape

Once selected, click and drag on the canvas to make a beveled rectangle.

Google Sheets bevel shape 2

When you first draw the bevel shape for the first time, it looks a bit yuck. This is because there is a border on the shape. We can get rid of this by selecting the pen and applying transparent to it.

Google Sheets bevel shape 3

You can also change the fill color by selecting the bucket to the left. Don’t worry the colour will automatically grade.

Google Sheets bevel shape 4 button

Next, add some text. Go and select the textbox menu. Drag the textbox over the button and type in your desired text. You can change the font weight, colour and size.

Google Sheets bevel shape 5 button

Once you are happy with your button, click save and close (Top right, can’t miss it)

Your button will appear on whatever cell you had selected before creating the drawing.

Reposition and size for your needs.

Google Sheets button 3

Connect your Button to your Function

To connect your button to your function, right-click the button. You will see the ellipse appear on the top right of the button. Click them and a dropdown menu will appear. Select Assign script… .

Google Sheets button assign script

A popup window will appear. Add your function, in our case, button.

Google Sheets Buttons assign script

Click OK and you are ready to use your button.

Let’s see how it looks.

 

Connect Button to Function Google Apps Script

 

As you can see, buttons are super easy to create. How will use your new found button making skills. I would love to hear how you are using them in your own projects. Comment below!

 

~Yagi

 

 

9 thoughts on “Google Apps Script: How to Connect a Button to a Function in Google Sheets”

    1. Hi Dahl Doctor,

      The Class OverGridImage would be what you are looking for. You would actually need to change the whole button image. However, that’s a whole post on its own. I’ll add it to my list.

      Thanks for the inspiration.

  1. Hi Yagi

    Is it possible to add a trigger to a button rather than a script. I have set up a form in Google Sheets (as forms doesn’t allow me to add tables), however, the script doesn’t appear to run for staff who are submitting the form, I’m assuming something to do with authorisation. I was wondering if it would be possible to set a trigger instead – have you ever come across anything like this?

    Thanks as always

  2. Thanks, Yagi! This is super helpful. Curious how I would disconnect the function from the button if I wanted to make more edits to the script or move the button to a different location?

    1. Hi Mark,

      To unassign the button, right-click, select the ellipses, select Assign script and rename it to something that is not a function in your project, “off”.
      Cheers,

  3. Hi Yagi,
    While doing the same I am getting

    There was a problem

    Script function Button could not be found

    please suggest on this.

    thanks

  4. Hello Yagi!

    Thank you so much, this article was of great help. But I just had a query, is there a way to make that button run a add-on. Like I can assign scripts to the button. But how can I assign the button to run a add-on. Thank you!

    1. Hi Neel,

      If you mean an addon from Google Marketplace, then, no. If you mean a sidebar that you have created, then yes. Just reference the link to the function for generating the SpreadsheetApp.getUi().showSidebar(your html output)

      ~Yagi

Leave a Reply