You can create a button in Google Sheets to run Apps Script from its user interface. The button is essential when you design Google Sheets for others who do not know App Scripts.
This post will show you how you can create a button in Google Sheets and attach the Google Apps Script function to it. Users can use this button to run the Apps Script without going to the Apps Script editor. This is very useful to build custom applications within the Google Sheets user interface itself.
You may also read, How to create a Data Entry form in Google Sheets.
Table of Contents
How to create the button
At the time of writing this post, there is no button option in Google Sheets.
But, you can draw an image of a button using the Drawing tool.
Follow the below steps to create the button. If you do not like reading, watch the demo video below, which demonstrates the same.
- Go to Insert > Drawing.
- In the Drawing dialog box, you can either draw a button using the Shape tools, or you can use the image tool. In this case, I will use the Imag tool.
Go to Image and then to the Search tab.
- Then search for a button using the “Button” keyword.
- Then click on a button image and click Select.
- Resize the button to make it easy to edit.
- Draw a text box on top of the button using the Text box tool.
- Then type the name of the button inside the text box.
- Resize the button/ text box and select font colors to match your requirements.
- Click the Save and Close button at the Top Right Corner of the Drawing tool.
- Now the button is inserted into the Google Sheet and resize and move to the required place.
Assign the Apps Script function to the button
In this case, I assume that you have a basic understanding of Google Apps Script. If not, I recommend you to read the Google Apps Script guide from this link.
For this example, I will assign a script to the button that can remove duplicate rows from Google Sheets. So, once you click the button, the duplicate rows are deleted from the Google Sheet. This code sample is available in Google Apps Script tutorials and can be accessed from this link.
Follow the below steps to assign the Apps Script to the button we created above. If you do not like reading, watch the demo video below, which demonstrates the same.
- Click on the button to edit it.
- Click the menu ( ⋮ ) icon at the top right corner.
- Select “Assign Script“
- Go to Script Editor and copy the name of the function you need to run. In this example, it is “removeDuplicates“.
- Go back to Google Sheets and paste the function name inside the text box of the “Assign script” dialog box.
- Click Ok.
- Now, click on the button to run the code. If this is the first time you run the code, it will require you to grant permission to run the code inside your Google Account.
If you want to edit the button again, Right-click on the button first. It will run the code otherwise.
You can make a copy of the Google Sheet with the sample code from the following link.
There is no direct option to insert a button in Google Sheets. However, you can draw and insert a button image using the Google Sheets Drawing option. Then you can assign your Apps Script to this button. That way you can let users easily run the Apps Script without going to Script Editor.