How to clear contents in google sheets

In this tutorial I will show you how to clear a range in Google Sheets using Apps Script.

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:

• clearContent(): clears the range's contents.

• clearFormat(): clears the range's formatting.

• clearDataValidations(): clears data validation rules in the range.

• clearNote(): clears notes in the range.

• clear(): clears contents, formats and data validation rules in the range.

• You can also specify advanced options as a JavaScript object to the clear() method.

Clear a range's contents using Apps Script

To clear a range's contents, first reference the range and then use the clearContent() method.

function clearContentsOnly() { var range = SpreadsheetApp .getActive() .getSheetByName("Clear Range") .getRange(4,2,2,2); range.clearContent(); }

Before running the clearContentsOnly() function

After running the function

The range's contents have been cleared but its formatting has been preserved.

Clear a range's formatting using Apps Script

To clear a range's contents, first reference the range and then use the clearFormat() method.

function clearFormattingOnly() { var range = SpreadsheetApp .getActive() .getSheetByName("Clear Range") .getRange(4,2,2,2); range.clearFormat(); }

Before running the clearFormat() function

After running the function

The range's contents have been preserved but its formatting has been cleared.

Clear data validation rules in a range using Apps Script

To clear the data validation rules in a range, first reference the range and then use the clearDataValidations() method.

function clearDataValidations() { var range = SpreadsheetApp .getActive() .getSheetByName("Clear Range") .getRange(25,2,2,2); range.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).

Clear notes in a range using Apps Script

To clear notes in a range, first reference the range and then use the clearNote() method.

function clearNotes() { var range = SpreadsheetApp .getActive() .getSheetByName("Clear Range") .getRange(11,2,2,2); range.clearNote(); }

Before running the clearNotes() function

After running the function

The notes in the range have been cleared.

Clear the contents, formatting and data validation rules in a range using Apps Script

To clear a range's contents, formatting and data validation rules, first reference the range and then use the clear() method.

function clearContentsFormatsValidations() { var range = SpreadsheetApp .getActive() .getSheetByName("Clear Range") .getRange(4,2,2,2); range.clear(); }

Before running the clearContentsFormatsValidations() function

After running the function

The contents, formatting and data validation rules in the range have been cleared.

You can pass a set of advanced options to the clear() method to clear multiple things at the same time

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:

• contentsOnly: If set to true, clear contents in the range.

• formatOnly: If set to true, clear formatting in the range.

• validationsOnly: If set to true, clear data validations in the range.

• skipFilteredRows: if set to true, don't clear rows that aren't visible because they've been filtered.

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.

Conclusion

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:

• clearContent(): clears the range's contents.

• clearFormat(): clears the range's formatting.

• clearDataValidations(): clears data validation rules in the range.

• clearNote(): clears notes in the range.

• clear(): clears contents, formats and data validation rules in the range.

• You can also specify advanced options as a JavaScript object to the clear() method.

Thanks for reading!

Stay up to date

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!

How do you clear contents of a cell in Google Sheets?

How do I clear contents in Google Sheets without deleting the formula?

How do you delete contents in multiple cells in Google Sheets?

How do I clear the contents of a cell?