Clear a Range in Google Sheets using Apps Script

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:

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

Screenshot of a range in a Google Sheets spreadsheet.

After running the function

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

Screenshot of a range in a Google Sheets spreadsheet.

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

Screenshot of a range in a Google Sheets spreadsheet.

After running the function

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

Screenshot of a range in a Google Sheets spreadsheet.

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

Screenshot of a range in a Google Sheets spreadsheet.

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).

Screenshot of a range in a Google Sheets spreadsheet.

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

Screenshot of a range in a Google Sheets spreadsheet.

After running the function

The notes in the range have been cleared.

Screenshot of a range in a Google Sheets spreadsheet.

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

Screenshot of a range in a Google Sheets spreadsheet.

After running the function

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

Screenshot of a range in a Google Sheets spreadsheet.

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

Screenshot of a range in a Google Sheets spreadsheet.

After running the function

The range's contents and formatting have been cleared.

Screenshot of a range in a Google Sheets spreadsheet.

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:

Thanks for reading!


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!