How Do I Create a Button to Clear Data in Google Sheets?

Google Sheets is a great tool to use for creating forms, checklists, calculators, and more. Setting up a button to clear data and reset your sheet is a great feature to add.


So, how do I create a button to clear data in Google Sheets? You will need to add a function to clear cells in the script editor. In the script, you will indicate the sheet name and cell range that you want to clear.


Here are step-by-step instructions for how to set up this function to clear cells in Script Editor.


Step 1: Open the Script Editor


In your Google Sheet, go to Tools > Script Editor.



Next, give your script a name.



Step 2: Add the Script


Now you're ready to add the script to the Script Editor.


You can delete what shows in the script editor by default and replace it with the following script:


function ClearCells() {
 var sheet = SpreadsheetApp.getActive().getSheetByName('YOUR SHEET NAME');
 sheet.getRange('C4:C6').clearContent();}

Don't forget to change the Sheet name and range to match your spreadsheet.


Note: The sheet name is the name that appears on the tab of your spreadsheet. Not the file name of your spreadsheet.



Step 3: Manually Run the Script


Click on the Save button in the top tool bar and then click Run.


You'll be prompted to click Review Permissions.



You'll be prompted to log into your Google account again. If you see this warning, it's okay. It's only because you are adding a custom app script to the sheet that Google doesn't recognize. You can click on Advanced and then click on the link at the bottom to continue (Go to Clear Cells Button (unsafe) as seen below).




Once you've finished the on screen prompts to give permission to this script to be used on your Google Account, you're ready to manually run the script.


Click on the Run button in the top tool bar. This will run the script and let you know if there are any issues with what you have input. You will see red errors in the Execution log if something goes wrong.



When this is done, you can close the script editor and we'll finish up on the spreadsheet it's self.


Step 4: Create the Button


In order to make a button that you can click on the spreadsheet to run the clear cells script, you'll need to use a Drawing.


Click on Insert on your spreadsheet, and then select Drawing.



From the Drawing screen, you'll add a shape for the button with a text box over the shape to put "clear", "reset", or whatever you'd like the button to say.


You can also customize the color of the button and the font/color/size of the wording.


When you're done designing your button, click the green Save and Close button in the upper right.



After clicking Save and Close, the button will apear on your spreadsheet where you can then click,drag, and resize it to fit where ever you would like it.



Step 5: Attach Script to Button


Now that the button is in place, you need to attach the script so that it will clear the cells everytime you click the button.


To do this, click on the button so that it's highlighted again. Next, click on the three dots on the right of the button. From here, you'll see a dropdown of options. Click on Assign Script.



You'll see a popup box where you'll put the name of the function. The function name is Clear Cells. This is what appears in the first line of the code after Function.


When you're done, click OK.



All done! You can now test your button and see that it clears the cells in the range that you indicated in the script.


Additional Notes


If you want to edit the button, right click on the button so that you can see the three dots again.


To edit the design of the button, simply click on Edit. If you made a mistake with the name of the script, you can click on Assign Script again.


If you need to edit the script again, simply repeat the steps from Step 1.

137 views0 comments

Topics

Recent Posts