[GOOGLE-SHEETS] Buttons in Google Sheets

Did you know that you can create buttons in Google Sheets to run custom scripts? In this tutorial, I'll show you how to create a Send Quote button that will run a Google Apps Script function whenever the user clicks on it.

This can be very useful to build simple applications within the Google Sheets UI itself. For example, the spreadsheet below automates a simple expense report approval workflow. Clicking the Send Emails button in the top right corner will send out emails to let people know the approval status of their expense reports.

🛈 Prerequisites

If you've never worked with Apps Script before, I've written an article that explains how to create and run your first apps script. I've also written a series of articles to teach you how to code using Google Sheets and Apps Script.

How to create a button in Google Sheets?

There are two high-level approaches. You can insert an image that you want to use as a button or you can create the button yourself using a drawing. I prefer using a drawing since I can quickly create the right button for each use case.

🛈 Note

If you use an image instead of a drawing, please note that the image must be inserted over the cells and not in a cell.

First select Drawing from the Insert menu.

Then create the button by using shapes and text.

★ Tip

Using a border that has a different color will make the button stand out in your spreadsheet.

I created the drawing below. Once you're done, click Save and Close.

You should now see your shiny new button in your spreadsheet.

How to assign a script to a button in Google Sheets?

The next step is to assign a Google Apps Script function to the button. When users click the button, the function assigned to it will be run. You will need to create this function using Google Apps Script and then assign its name to the button.

🛈 Note

If you already have a function that you want to run when users click the button, please skip the step described in this note. If you do not have a function, please open the Apps Script editor by selecting Tools →Script editor. Then please replace the code in the editor with the code below.

function sendQuote() {
  SpreadsheetApp.getActive().toast("Sending quote.");
}

For the purpose of this tutorial, the function sendQuote() will only display a toast notification in the spreadsheet. Please modify the function's name and behavior based on your use case.

Select the button and select the three-dots menu (⠇). Select Assign script.

Type the name of the function you want to run and select OK. Enter only the name of the function. Do not include parentheses or parameters. At this time, there is no way to pass parameters to a function when it is run from a button click.

Now try clicking the button. When you click on the button, the function you assigned to it should be run. If you used the code provided earlier in this tutorial, you should see a notification displayed on the bottom right corner of the spreadsheet.

🛈 Note

When you run your script for the first time, you might be asked to authorize it.

That's it. You created a button and configured it to run a Google Apps Script function whenever a user clicks on it.

Run a macro in Google Sheets from a button

You can also use this approach to run macros that you've recorded in Google Sheets. Let's say you've recorded a macro to bold text in the selected cell. This macro is called boldText.

You can create a button that runs this macro by assigning the macro to it. To assign a macro to a button, click the three dots menu, select Assign script and enter the name of the macro for the script that should be run when the button is clicked.

Now you can test running the macro by clicking on the button.

Conclusion

In this tutorial, you learned how to create buttons in Google Sheets and configure them to run Google Apps Script functions when users click on them. This approach can also be used to run macros in Google Sheets whenever a button is clicked.

Thanks for reading.