Google Sheets may not be designed for “forms,” but you can create them for interesting and useful purposes. However, a Google Sheet form would be much more useful if it had a “reset” button to clear the values in your user input cells each time it’s used. While buttons aren’t a built-in feature of Google Sheets, I’ll show you a clever trick in this article to create one.
Case Study: Lead Scoring Calculator
Let’s say you have a mail-order golf ball business and you decide to create a lead scoring calculator using Google Sheets. To do this, you can export data from your past leads from your CRM and import it into a spreadsheet. Then, you can use a pivot table to summarize the data and determine the most relevant data points regarding a lead, such as income and postal code. Finally, you can create a form, which is a series of cells where you can enter information about a lead, and it will return a lead score from 1 to 5 based on the analyzed data.
Clearing Cells with a Script
While the calculation may work perfectly, it can be tedious to manually clear the cells each time the form is used. You can use a script function to clear the form, and we’ll link this script to a button in a moment.
To create a new script, go to Tools > Script Editor. A new browser tab will open with the script editor. Now, you need to write a function to clear the cells. You can use this code, but make sure to replace the sheet name and cell range with your own:
function ClearCells() {
var sheet = SpreadsheetApp.getActive().getSheetByName('Calculator');
sheet.getRange('C4:C6').clearContent();
}Now, save it by going to File > Save. It will lead you through an authorization process to give permission to your script. Do that and grant permission to your script. You’ll also need to manually run the script once for it to be recognized by your sheet, so go to Run > Run function > Clear Cells. Now, you can close the script editor tab.
Adding a Button
You can’t add a real button to a Google Sheets spreadsheet, so the trick is to insert a drawing of a button and link it to your script. First, go to Insert > Drawing. Now, you can create a button however you like. I did it by creating a text box frame, setting the background color to blue, and adding the text “Reset”.
Place your button where you want it, then click on it. A menu will appear. Select “Assign script”. Now, you can add your “Clear Cells” script. Once this is done, clicking the button will trigger the script!
Note: If you can’t link your script, it means you haven’t given it permission or haven’t run it once to save it, so repeat these steps and try again.
Resources
- “Reset Button for Google Sheets?”
- How to add user interface to cells in Google Sheets using an Apps Script?
For more tips and tricks on Google Sheets, visit Crawlan.com.
