In this tutorial I will show you how to clear a range in Google Sheets using Apps Script.
This tutorial assumes that you're familiar with the following concepts:
There are a couple of different methods to clear a range in Google Sheets using Apps Script. In each case, the first step is to reference the range that you want to clear. Then you need to use the right method depending on what you want cleared in the range.
Here are some of the methods that you can use:
To clear a range's contents, first reference the range and then use the clearContent() method.
function clearContentsOnly()
Before running the clearContentsOnly() function
After running the function
The range's contents have been cleared but its formatting has been preserved.
To clear a range's contents, first reference the range and then use the clearFormat() method.
function clearFormattingOnly()
Before running the clearFormat() function
After running the function
The range's contents have been preserved but its formatting has been cleared.
To clear the data validation rules in a range, first reference the range and then use the clearDataValidations() method.
function clearDataValidations()
Before running the clearDataValidations() function
After running the function
The range's contents and formatting have been preserved but its data validation rules have been cleared (which is why the checkboxes have disappeared).
Note: Checkboxes are implemented as data validation rules in Google Sheets.
To clear notes in a range, first reference the range and then use the clearNote() method.
function clearNotes()
Before running the clearNotes() function
After running the function
The notes in the range have been cleared.
To clear a range's contents, formatting and data validation rules, first reference the range and then use the clear() method.
function clearContentsFormatsValidations()
Before running the clearContentsFormatsValidations() function
After running the function
The contents, formatting and data validation rules in the range have been cleared.
To clear multiple things at the same time, you can pass an object that tells the clear() method what all to clear in the range. The code below clears a range's formatting and contents.
The options that you can configure are:
Note: The commentsOnly option does not work
While the Apps Script documentation suggests that you can also clear comments in a range by using the commentsOnly option, this doesn't seem to work as of Jan 2021.
Here is an example that demonstrates how to clear a range's formatting and contents by using the options object.
function clearContentsAndFormatting() < var range = SpreadsheetApp .getActive() .getSheetByName("Clear Range") .getRange(11,2,2,2); // Configure what to clear in the range var options = < formatOnly: true, contentsOnly: true >; // Pass the options object to the clear() method range.clear(options); >Before running the clearContentsAndFormatting() function
After running the function
The range's contents and formatting have been cleared.
When you specify multiple options, they seem to be applied one at a time. Therefore, if you want to undo these changes in your sheet using CTRL + Z (or ⌘ + Z on a Mac), you'll need to undo multiple times based on the number of options that you specified.
This behavior isn't covered in the documentation but I've observed it while using this feature.
Here is an example
Consider the range below that contains TRUE and FALSE values.
Suppose you clear the contents and formatting in this range by specifying the options object.
Now, when you undo the change, only the range's formatting will be restored.
You'll need to undo once again to restore the contents of the range.
In this tutorial I showed you how to clear a range in Google Sheets using Apps Script. There are multiple methods to clear a range. Here are the ones that we explored in this tutorial:
Thanks for reading!
Follow me via email to receive actionable tips and other exclusive content. I'll also send you notifications when I publish new content.
By signing up you agree to the Privacy Policy & Terms . Have feedback for me?I'd appreciate any feedback you can give me regarding this post.
Was it useful? Are there any errors or was something confusing? Would you like me to write a post about a related topic? Any other feedback is also welcome. Thank you so much!
Follow me via email to receive actionable tips and other exclusive content. I'll also send you notifications when I publish new content.
By signing up you agree to the Privacy Policy & Terms .Do you use spreadsheets a lot? Did you know that you can build simple applications using spreadsheets or even automate your work? If this sounds exciting, this tutorial is for you 🚀👉.
Learn Google Apps Script, learn how to use it to automate your tasks, and build simple user interfaces and applications in Google Sheets.
Learn how to send emails right from Google Sheets so you can create birthday reminders or automate sending those boring "Daily report" emails at work.
Learn how to build custom user interfaces like alerts, prompts, sidebars, etc. in Google Sheets using Google Apps Script.
In todays world, it can be useful to understand coding concepts even if you aren't a programmer. I'm writing a series of posts to try and teach some of these concepts using spreadsheets.
