When you’re processing data in Google Sheets, scripts and macros can come in quite handy. If you find yourself using certain scripts and macros quite frequently, you should consider adding buttons to your sheets.
In this tutorial, we will show you how to create and add a Google Sheets button.
We will also show you, with simple examples, how you can connect the button to a macro or script that will run whenever the button is clicked.
Table of Contents
Why Make a Google Sheets Button?
In Google Sheets, you can add colorful buttons that not only add to the aesthetics of your sheet, but also add a lot of functionality.
You can connect the button to a script or macro, which you can then run by simply clicking on the button. This saves a lot of time, especially if there are scripts or macros that you need to run quite often.
You can place the button anywhere on your sheet, so keeping your buttons next to relevant portions of your spreadsheet helps you accomplish tasks as and when needed.
How to Make a Button in Google Sheets
To add a button in Google Sheets, you need to follow these steps:
- Create the button
- Assign a macro or script to the button
- Click on the button to run the macro / script
Step 1: Create the Button
The first step is, of course, creating the button.
Two Ways to Insert a Button in Google Sheets
There are two ways to insert a button in Google Sheets. You can either draw the button yourself using the Drawing Tools that Google Sheets offers, or you can insert an image that can act as a button.
Let us see how to create a button in each of these cases:
Creating a Button in Google Sheets by Inserting an Image
If you don’t want to use a ready-made image as a button, then the process is quite simple. Here are the steps:
- Click on the Insert menu.
- Select Image->Insert image over cells.
- This opens the ‘Insert image’ box. You have the option to upload your image from your computer’s memory (by clicking on Browse), from the camera, URL, Photos, Google Drive, or from a Google Image Search. Select the option you need and get your required image.
- Once you’ve imported your required image, you can go ahead and resize or move your image as required.
Note: It is advised to insert your image ‘over cells’ instead of ‘in cell’, as it gives you more flexibility in movement, sizing, and placement.
For example, you could use an icon image as a button to send emails instantly:
<a href="https://www.flaticon.com/free-icons/email" title="email icons">Email icons created by Freepik - Flaticon</a>
Creating a Google Sheets Button by Drawing
If you want more flexibility and would like to design the button yourself, follow these steps:
- Click on the Insert menu.
- Select the Drawing option.
- This opens the Drawing window, which contains tools to help you design your button.
- Let’s make a simple rectangular button. Click on the Shape tool, and select the rounded rectangle shape (under the Shapes category), as shown below:
- Drag the cursor in the drawing area to make a rounded rectangle shape, as shown below:
- For effect, let’s give it a gradient fill by clicking on the Fill color tool and selecting the shade ‘light orange 2 radial gradient’ from the ‘Gradient’ tab.
- A contrasting border always makes your button stand out, so let us click on the Border color tool and select the color, black, as shown below:
- Finally let’s add a text box that says what the button is going to do. Let’s add a simple text box that says ‘Format sheet’. Click on the Text box tool and drag a text box inside the button. Then type your required text.
- At this point you can also change the color, font, size and other text settings if you like.
- Once you’re done designing your button, click on the Save and Close button:
- You should now see your designed button on your spreadsheet. Resize, drag and position your new button as you need to.
Note: You can also find an Image option in the Drawing window’s toolbox. This option also lets you import an image with the difference that you can also edit the imported image using the Drawing tools.
Step 2: Assign a Script to the Button in Google Sheets
The next step is to assign a Google Apps Script function or Macro to the button. When users click the button, the function assigned to it should run.
Assigning a Macro to the Button
Let us take a simple example to demonstrate how to assign a macro to a button. Let’s record a simple macro that formats the text in the current spreadsheet.
To record a macro, follow these steps:
- Navigate to Extensions->Macros->Record Macro.
- Select whether you want to record the macro with relative or absolute references.
- Perform all the actions that you want recorded in the macro. For example, you might want to set the font size and style for all the text in the worksheet and add gridlines to them.
- Once you’re done recording, click Save.
- Give your macro a name and click Save again.
Your macro is now ready to run.
To learn more about macros, refer to this article.
To assign your button to this macro, follow these steps:
- Click on the button.
- You should see three dots (also known as an ellipsis) on the top right corner of the button. Click on this ellipsis.
- Select ‘Assign script’ from the menu that appears.
- You will be asked ‘what script do you want to assign?’. Enter the name of your macro in the input box.
- Click OK.
Your button is now connected to your specified macro.
Assigning a Google App Script to the Button
You can also assign a Google App Script to a button so that the script runs whenever you click on the button.
Let us take another simple example to demonstrate how to assign a script to a button. Please note that you will need to know how to code in Google Apps Script for this.
Let’s say you want the script to simply send an email to addresses in your spreadsheet. We have explained how to do this in our article How to Automatically Send Emails from Google Sheets (Using Appscript). Click on the link if you want to learn more.
In the article, the code that we had used to send the emails was as follows:
function sendEmail() { var ss = SpreadsheetApp.getActiveSpreadsheet() var sheet1=ss.getSheetByName('Sheet1'); var sheet2=ss.getSheetByName('Sheet2'); var subject = sheet2.getRange(2,1).getValue();; var message = sheet2.getRange(2,2).getValue(); var n=sheet1.getLastRow(); for (var i = 2; i < n+1 ; i++ ) { var emailAddress = sheet1.getRange(i,1).getValue(); MailApp.sendEmail(emailAddress, subject, message); } }
Let’s say we want to assign this script to the following button:
The process is the same as assigning a macro:
- Click on the button.
- Click on this ellipsis that you can see on the top right corner of the button.
- Select ‘Assign script’.
- When the Assign script dialog box appears, enter the name of your function (that is defined in the script) in the input box. Type only the name of the function. There is no need to pass any parameters.
- Click OK.
That’s it! Your button is now ready.
Step 3: Click on the button to run the macro / script
The final step is quite simple. Click on the button to run the macro or script that you have assigned to it.
If you are running the code for the first time, you will be asked to authorize it. Go ahead and follow the instructions to authorize the code.
Click on the button as many times as you need to run your script / macro.
Note: If you’re interested in duplicating the sample sheets that we used in this tutorial, you can click on this link and download the sheet, along with the relevant codes.
FAQs
How do I create a button in Google Sheets?
To create a button in Google Sheets, simply, navigate to Insert->Image or Insert->Drawing. Design or import the image for the button you want, and then assign a script or macro to it. The steps have been explained in detail in this tutorial.
Can I add a button to Google Sheets?
Yes, In Google Sheets you can add a button that runs specific scripts or macros in the sheet.
Can you create a macro button in Google Sheets?
Yes, you can use a Google Sheets macro button. You can record a macro and assign the macro’s name to a button in the sheet.
How do I make a button go to a specific sheet in Google Sheets?
You can use the following script to go to a specific sheet.
Simply assign this script to your button, so that Google Sheets automatically takes you to your specific sheet when the button is clicked.
function GoToPage() { var ss = SpreadsheetApp.getActiveSpreadsheet() ss.setActiveSheet(ss.getSheetByName(‘sheetname’),true); };
In the above code, the word ‘sheetname’ is just a placeholder. You need to change it to the name of the sheet to which you want the button to take you.
Conclusion
In this tutorial, we showed you how to create a Google Sheets button and configure them to run a macro or script when clicked. For your convenience, we have also provided a sample spreadsheet that you can run to understand and apply our instructions.
We hope you find it useful!