// Sheets · Apps Script

Clear a range but keep formatting in Google Sheets.

Use clearContent() in Apps Script to wipe cell values from a Google Sheets range without touching borders, colors, number formats, or data validation rules.

“I need to reset the data in a range every week but I keep losing the formatting I spent time setting up.”

The script

copy · paste · trigger

clearRangeKeepFormatting.gs

Apps Script

// Clear values from a named range without touching formatting.
// Run this manually or attach it to a time-based trigger.
function clearInputRange() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Tracker');

  // clearContent() wipes values only — borders, colors,
  // number formats, and data validation stay intact.
  sheet.getRange('B2:F50').clearContent();

  // If you need to clear several non-contiguous columns at once,
  // getRangeList accepts an array of A1 notation strings.
  sheet.getRangeList(['H2:H50', 'J2:J50']).clearContent();
}

Generate yours

Need a variant? Gnaw writes a custom version from one sentence — fields, triggers, edge cases handled.

Walkthrough

clearContent() vs clear() — what each one actually deletes

The Range class exposes two methods that sound similar and destroy very different things. clear() removes everything attached to the cell: the value, any number or date format, background color, borders, notes, and data validation rules. Run it on a range you spent twenty minutes formatting and you are back to a blank grey grid.

clearContent() removes only the value — the string, number, formula, or boolean stored in the cell. Every visual property stays: your alternating row colors, the currency format on column D, the dropdown validation you set up so people can only enter 'Yes' or 'No'. That is the one you want when you are resetting a weekly input form.

There is a third option worth knowing: clear({contentsOnly: true}). It does the same thing as clearContent() but takes an options object, which lets you combine flags — for example, {contentsOnly: true, commentsOnly: true} clears values and notes but keeps format and validation. For the common case of values-only, clearContent() is simpler and the intent reads out loud correctly.

Two API calls, not a loop over cells

The first time I hit a slow-running reset script, the culprit was a for-loop calling range.getCell(r, c).setValue('') on each cell one by one. Each setValue() is a separate call to the Sheets backend. On a 50-row, 6-column range that is 300 round trips before the function finishes.

clearContent() on a range is one API call regardless of how many cells are inside it. The same applies to getRangeList(...).clearContent() — that batches multiple non-contiguous ranges into a single request. On large sheets the difference between 'runs in under a second' and 'times out at 30 seconds' usually comes down to whether you eliminated the per-cell loop.

The same logic applies to clearFormat() (formatting only) and clearDataValidations() (validation rules only). Each clears its specific layer in one call.

Attaching the function to a time-based trigger

The script above is usable as a standalone run-once function, but the common production use is a scheduled reset — clear the intake form every Monday morning before staff arrive. In Apps Script, open Extensions > Apps Script, then go to Triggers (the clock icon in the left sidebar) and create a new trigger pointing at clearInputRange with a time-based source.

Set the trigger to 'Week timer' and pick a day and hour window. The function runs under your account's authorization, so it needs the spreadsheet to be owned by or shared with the account that installed the trigger. If the trigger fires and the sheet name in getSheetByName('Tracker') does not match exactly — including capitalization — the method returns null and the next line throws a TypeError. A one-line null guard saves the confusion: if (!sheet) return; before the clearContent() call.

Execution logs for triggered runs live under Executions in the Apps Script sidebar. That is the first place to check if Monday comes and the range is not cleared.

Want a custom version?

Describe your sheet and the rule you want. Gnaw writes the Apps Script — fields, triggers, edge cases — in one shot.

Generate yours Browse BullPack

FAQ

4 questions

Does clearContent() remove formulas too?: Yes. Apps Script treats formulas as a cell value — clearContent() deletes them the same way it deletes a plain number. If you want to keep certain cells' formulas and only clear others, narrow your range to exclude those cells before calling clearContent().
I used clear() by mistake and lost my formatting. Is there an undo inside a script?: There is no programmatic undo in Apps Script. In the spreadsheet UI, Ctrl+Z (or Cmd+Z on Mac) will undo the last action if you catch it quickly — but if the script ran on a trigger overnight, the undo history is gone. The practical fix is to store your formatting in a second 'template' sheet and copy it back with copyFormatToRange() when needed.
What is the difference between Range.clearContent() and Sheet.clearContents()?: Sheet.clearContents() clears the entire sheet's values in one call — no range argument. Range.clearContent() scopes to whatever range you specify. Both leave formatting intact. Use the sheet-level method only when you genuinely want to wipe every cell on the tab.
Will clearContent() trigger onChange or onEdit events?: No. Programmatic changes from Apps Script do not fire the simple onEdit(e) trigger. They can fire an installable onChange trigger if you have one set up with the 'On change' event type, but that trigger does not receive cell-level edit information — it only knows a change occurred. If your sheet logic depends on onEdit catching the clear, you will need to call that logic explicitly from the same script.