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.
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.
