Google Apps Script: UI, Google Sheets [Updated 08 Apr 2022]
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.
- Write your function in Google Apps Script editor.
- Create an image or upload an image and add it to your Google Sheet.
- Right-click the image, select the ellipse and Assign script.
- Type in your function name.
- Click the button to test. You should be up and running.
Table of Contents
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
|
1 2 3 4 5 6 |
function button() { var ui = SpreadsheetApp.getUi(); var cell = SpreadsheetApp.getCurrentCell().getValue(); ui.alert("You clicked: "+cell+"!! \n Ya big legend!!!"); } |
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.
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.
Once selected, click and drag on the canvas to make a beveled rectangle.
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.
You can also change the fill color by selecting the bucket to the left. Don’t worry the colour will automatically grade.
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.
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.
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… .
A popup window will appear. Add your function, in our case, button.
Click OK and you are ready to use your button.
Let’s see how it looks.
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!
If you what to delete your button after it has been clicked, or just want to learn more about scripting with buttons in Google Apps Script Check out this tutorial:
https://yagisanatode.com/2022/03/28/how-to-remove-a-google-sheets-button-drawings-or-images-connected-to-a-google-apps-script-after-the-script-has-been-run/
Need help with Google Workspace development?
Go something to solve bigger than Chat GPT?
I can help you with all of your Google Workspace development needs, from custom app development to integrations and security. I have a proven track record of success in helping businesses of all sizes get the most out of Google Workspace.
Schedule a free consultation today to discuss your needs and get started or learn more about our services here.
~Yagi
Changelog
- 08 Apr 2022: Added link to a tutorial on deleting buttons in Apps Script.
This is really helpful! How do we change the text of the button using script? Thank you.
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.
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
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?
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,
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
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!
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
Alright, thank you!
Is it possible to add parameters to the function
eg. myFunction works for me, but not myFunction(1)
Thanks 🙂
Hi geekShadown,
Unfortunatley, no. But depending on what you are trying to do, you could gather your data from cells in the sheet (even hidden ones that change on the condition you set), use the Session Class to get the current Effective or Active user or grab the current time with the Date constructor. Lots of options.
~Yagi