ScriptdrillClear and format cells
range.clearContent() removes values but keeps formatting, range.clearFormat() does the reverse and range.clear() removes both. Formatting methods chain on a range: setBackground("#fff2cc"), setFontWeight("bold"), setNumberFormat("#,##0.00"), setHorizontalAlignment("center"). Apply them to whole ranges, not cell by cell.
Syntax
range.clear() | range.clearContent() | range.setBackground(color).setFontWeight("bold").setNumberFormat(pattern)| Part | What it is |
|---|---|
setBackground(color) | CSS color name or hex like #d9ead3; null resets |
setFontColor / setFontSize / setFontWeight | Text color, size in points, "bold" or "normal" |
setNumberFormat(pattern) | Sheets number pattern such as 0.00, #,##0, 0%, yyyy-mm-dd, @ for text |
setBorder(top, left, bottom, right, vertical, horizontal) | true, false or null for each side |
setWrap(true) / setHorizontalAlignment("center") | Wrapping and alignment |
Examples
const sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange("A2:F" + sheet.getLastRow()).clearContent();Result: Data rows emptied; header row and all formatting stay
sheet.getRange("A1:F1").setBackground("#1f4e79").setFontColor("#ffffff").setFontWeight("bold");
sheet.setFrozenRows(1);Result: Dark blue bold white header, frozen at the top
sheet.getRange("C2:C100").setNumberFormat("#,##0.00");
sheet.getRange("D2:D100").setNumberFormat("yyyy-mm-dd");
sheet.getRange("E2:E100").setNumberFormat("0.0%");Result: 1234.5 shows 1,234.50; dates show 2026-11-03; 0.153 shows 15.3%
sheet.getRange("A1:F20").setBorder(true, true, true, true, true, true);
sheet.autoResizeColumns(1, 6);
sheet.clearFormats();Result: First two lines draw a grid and fit the columns; the last line removes all formatting on the sheet
Common mistakes
- clear() wiped the header formatting: clear() removes everything; use clearContent() when you only want the values gone.
- Numbers stored as text keep showing left-aligned: setNumberFormat does not convert text; write real numbers with Number(value) and then format.
- Formatting one cell at a time in a loop is slow: build the range once (getRange(2, 3, n, 1)) and call the format method once.
- Dates show as 46,000: the cell has a number format like #,##0; set a date pattern such as dd/mm/yyyy on that range.
Practice Clear and format cells
These drills check your answer on live data, free, no account needed.
- Format moneyFormat `amount` as money in the given ISO currency code (like `"USD"` or `"JPY"`) and locale, the way `Intl.NumberFormat` does with `style: "currency"`. Each currency keeps its usual number of decimals, and each locale its own grouping.
- Align a text table`rows` is an array of rows, each an array of strings with the same number of cells. Return one line per row: pad every column to its widest cell, with the first column left-aligned and the other columns right-aligned, cells joined by two spaces, and no trailing spaces on any line.
Questions
How do I highlight rows that meet a condition?
Loop the values, collect the row numbers, then setBackground on each matching row range, or create a conditional format rule with sheet.setConditionalFormatRules so it stays live.
How do I reset a range to default formatting?
range.clearFormat() removes backgrounds, fonts, number formats and borders in one call; values stay.
Related
- setValue and setValuesWrite data to Google Sheets with Apps Script: setValue for one cell, setValues for a 2D array that must match the range size, and the dimension error fix.
- Delete rowsDelete rows in Google Sheets with Apps Script: deleteRow and deleteRows by position, delete rows that match a condition by looping from the bottom, blanks.
- Sort a rangeSort data in Google Sheets with Apps Script: range.sort by column number, descending order, multiple sort keys, sorting without the header, sorting an array.
- Protect ranges and sheetsProtect ranges and sheets with Google Apps Script: create a protection, remove everyone but yourself, add allowed editors, warning only, remove protections.