In Google Sheets, you can automate simple tasks by recording macros or using Google Apps Script to create more flexible and versatile scripts. Regardless of the method you use to create them, finding and executing them can become tedious if you use the scripts frequently. Macros and Google Apps Script can both be accessed from the menus in Google Sheets, but it takes a few clicks to get there and run the script. Fortunately, Google Sheets makes it very easy to create buttons and assign scripts to them so you can run your scripts directly from your spreadsheet.
In this guide, you will learn two ways to make buttons in Google Sheets: inserting an image or creating a drawing. You will also learn how to assign a script to a button. These scripts can be ones you created by recording a macro or using Google Apps Script. You also have examples showing how to record a simple macro and how to write a simple function using Google Apps Script.
How to Make a Button in Google Sheets?
In this section, you have step-by-step instructions on two ways of making buttons in Google Sheets: creating a drawing or inserting an image. If you want to edit the image first, you can insert it via the drawing tool.
Option 1. Create Drawing
Using the drawing tool, you can create custom buttons easily. Follow the steps below to create a simple button.
- 1. Go to Insert > Drawing to create the button.
- 2. Select the shape you want to use and resize it as you wish.
- 3. You can add text to the button from the toolbar, as shown below. Once you’re happy with the button’s appearance, click ‘Save and Close’.
- 4. As you can see, the button has been added to the spreadsheet. In the next section, you will learn how to assign a script to the button.
When sharing a Google Sheets spreadsheet Google usually tries to share the entire document. Here’s how to share only one tab instead.READ MORE
Option 2. Insert Image
- 1. Go to Insert > Image and choose whether to place the image over the cells or within the selected cell.
When making a button, it’s better to place it over the cells so you can resize the image and place it wherever you want on your spreadsheet.
- 2. Select the image you want for the button and click ‘Insert’.
As you can see below, the trash can icon has been added to the spreadsheet.
- 3. If you want to edit the image using the drawing tools before inserting it in your spreadsheet, go to Insert > Drawing.
- 4. Click on the image icon in the toolbar, as shown below.
- 5. Select the image you want to use for the button.
You can edit the image however you want using the drawing tool.
Below, you can see that the edited image has been added to the spreadsheet.
How to Create and Assign a Script to a Button in Google Sheets?
Once you have created the button, you can choose whether to assign the script of a macro or a Google Apps Script function. For this example, I will use a simple script that clears the values in a table so that users can add new data. This table is connected to a funnel chart, which will be updated automatically once new values are added.
1. Create Macro or Google Apps Script
Below, you will learn how to record a simple macro to clear the values. You will then learn how to open it in Google Apps Script for editing.
Google Sheets Macros
You can record a macro to carry out a series of tasks in Google Sheets, then assign it to execute those actions when clicked. To learn more about creating macros in Google Sheets, check out our guide on Google Sheets Macros: Enable, Create & Use Them.
- 1. Go to Extensions > Macros > Record macro.
- 2. Once the macro starts recording, choose whether you want to use absolute or relative referencing. Carry out the tasks you want to record and click ‘Save’.
- 3. Name your macro and save it.
If you work with important data in Google Sheets, you probably want an extra layer of protection. Here's how you can password protect a Google SheetREAD MORE
Google Apps Script
While macros are useful, Google Apps Script allows you to create more complex and flexible scripts. Using Apps Script, you can access and use the libraries for Google products and services. You can create a new script or edit a recorded macro, like the one created in the previous section. To learn more about Google Apps Script, check out the guide on Google Apps Script Tutorial With Google Sheets.
- 1. Go to Extensions > Macros > Manage macros.
- 2. Select the macro you want to edit and click the vertical ellipsis to the right of it. Choose ‘Edit script’.
- 3. Google Apps Script will open the script, where you can edit as you wish.
2. Select Button & Assign Script
Follow the steps below to assign a script to a button
- 1. Select the button and click the vertical ellipsis in the top-right corner. Choose ‘Assign script’.
- 2. In the text box, type the name of the macro or Google Apps Script you want to assign and click ‘OK’.
- 3. When you click the button, the script will run automatically.
You now know two ways to create buttons in Google Sheets. You can add an image through the ‘Insert’ menu or use the drawing tool to create a drawing or edit an image. Once you have your button, you can easily assign a macro or Google Apps script to it. Once you grant the script access to the spreadsheet, you can run the script whenever you want by clicking the button.
You now know how to create a drawing to use as a button, as well as how to insert and edit an image instead. You have step-by-step instructions on how to use the drawing tool to create a button with text and how to import and edit an image, as well as how to import the image directly via the ‘Insert’ menu. You also know how to record a macro and edit the script using Google Apps Script, including step-by-step instructions on recording a simple macro that clears the values in a funnel chart template so that you can update the chart with new values. Finally, you know how to assign the script to the button with a few clicks.
Check out our guides to learn more about macros and Google Apps Script.