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

 

 

Leave a Reply