Google Scripts Guide to Clearing Data

Clear ranges with Google Scripts.

This came up in a recent project of mine from a client, specifically they needed to clear a range, while keeping the formulas and formatting. The goal is to just clear values within the range. While solving their issue below lets look at how we got there. To begin with we need to get a range to play with.

1. Get a range

let range = mySheet.getRange("A1:B8")

2. Clear

Clear removes everything from the range. Including formatting, and formulas which we want to keep.

range.clear()

3. Clear Content

More applicable to this project as it keeps formatting, but formulas are still removed.

range.clearContent()

4. Clear Format

Incase you need it I decided to include this one. Clear format will keep the formulas but doesn’t clear the content like we want.

range.clearFormat()

5. Keep formulas but not values

clearContent was the closest clear function for what we wanted. So to keep formulas let’s use the oldest trick in the book, copy paste. By copying just the formulas we can insert them after clearing content.

let formulas = range.getFormulas()
range.clearContent()
range.setFormulas(formulas)

There we have it, how to clear a range, while keeping formulas and formatting. If you didn’t want to keep formatting all you need to do is just replace clearContent with just clear.