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:

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

Note

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.

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!