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.

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.

First select Drawing from the Insert menu.

Then create the button by using shapes and text.

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.

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

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.

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.