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.

Tech stuff and SwiftUI

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store