Why does my date column show a number like 46210 instead of the date after I call setNumberFormat?

The cell is storing a text string that looks like a date, not a date serial number. setNumberFormat only controls how a numeric value is displayed — it can't reinterpret text. Fix it by replacing the cell contents: parse the string with new Date() and write it back with setValue(), which Sheets will then store as a serial. After that, setNumberFormat('yyyy-mm-dd') works as expected.

How do I apply different formats to different columns in one function call?

Use setNumberFormats() (plural) on a multi-column range. It accepts a 2D array matching the shape of the range — one pattern string per cell. If all cells in a column share the same pattern, passing a single-column range to setNumberFormat() (singular) is simpler and faster.

Does the pattern '$#,##0.00' work for non-USD currencies?

Replace '$' with any literal currency symbol: '€#,##0.00', '£#,##0.00', '¥#,##0'. The symbol is treated as a literal character in the pattern. For locale-aware formatting that picks the symbol automatically, you can use the special pattern '[$$-409]#,##0.00' where 409 is the locale code, but for most cases a hard-coded symbol is clearer and more predictable.

My percentage cells are showing values 100x too large — what's wrong?

The '0%' pattern multiplies the stored value by 100 for display. If you store 17 and format it as '0%', Sheets shows '1700%'. Store the decimal form instead: 0.17 formatted as '0%' shows '17%', and 0.174 formatted as '0.0%' shows '17.4%'. This matches how Sheets handles percentage cells set by hand — the UI stores the decimal, not the whole number.

// Sheets · Apps Script

Set a cell's number, date, or currency format with a script.

Use Apps Script's setNumberFormat() to apply currency, date, percentage, and custom number patterns to any cell or range — without changing the underlying stored value.

“I want to format cells in Google Sheets from a script — apply currency symbols, date patterns, or decimal places — without having to click through the Format menu every time.”

The script

copy · paste · trigger

formatCells.gs

Apps Script

// Apply number, currency, and date formats to specific cells
function formatReportCells() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Report');

  // Currency: two decimal places, comma-separated thousands
  sheet.getRange('B2:B20').setNumberFormat('$#,##0.00');

  // Percentage with one decimal place
  sheet.getRange('C2:C20').setNumberFormat('0.0%');

  // ISO date — stored serial number displays as 2026-07-03
  sheet.getRange('A2:A20').setNumberFormat('yyyy-mm-dd');

  // Plain integer, no decimals, no comma
  sheet.getRange('D2:D20').setNumberFormat('0');
}

Generate yours

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

Walkthrough

What setNumberFormat actually does (and doesn't do)

setNumberFormat() writes a display mask onto the cell — the same pattern string the Format > Number > Custom number format dialog accepts. The stored value in the cell never changes. A cell holding 44000.5 formatted as '$#,##0.00' displays '$44,000.50'; the value Apps Script reads back with getValue() is still 44000.5.

That distinction matters most with dates. Sheets stores dates as serial numbers (days since December 30, 1899). A cell showing '2026-07-03' is actually storing 46210. If your date column shows the pattern but the cell never changes — say, it still shows the raw number or the pattern literally — the cell probably contains a text string, not a date serial. setNumberFormat can't fix that; you need to parse and rewrite the value first.

The pattern syntax follows the same rules as Excel custom formats: '0' is a required digit, '#' is optional, commas trigger thousand-separating, and percent patterns multiply the stored value by 100 before display. So store 0.174 and format '0.0%' to show '17.4%'.

Patterns worth keeping in a utils file

The first time I built a reporting sheet with setNumberFormat, I scattered pattern strings across five functions and ended up with '$#,##0.00' and '$#,##0.0' living in the same codebase. Now I keep a single FORMAT object at the top of a shared utils.gs file and reference it everywhere.

Common patterns: '$#,##0.00' for USD currency; '#,##0' for a large integer with thousand separators but no currency symbol; '0.0%' for a percentage (remember, the stored value must be the decimal form — 0.17, not 17); 'yyyy-mm-dd' for ISO dates; 'MMM d, yyyy' for something like 'Jul 3, 2026'; 'h:mm am/pm' for 12-hour time. For Euro amounts, '€#,##0.00' works, but note that the comma/period meaning is locale-flipped in some Sheets locales — the pattern itself doesn't change, the locale setting in File > Settings controls the separator character.

You can also pass an array of patterns to setNumberFormats() (plural) when columns need different formats in one call, which saves round-trips to the Sheets API.

Applying formats at write time, not as a separate step

A common mistake is writing values and then calling setNumberFormat in a second loop. Each getRange/setNumberFormat call is a Sheets API call, and if you're iterating rows you'll hit the 'Exceeded maximum execution time' wall fast. Batch the writes: setValues() for all data, then one setNumberFormat() on the whole column range. That's two API calls regardless of row count.

If you're generating a report programmatically, it often makes sense to format the column once when the sheet is first created — put it in a setupSheet() function you only run once — rather than re-applying format on every data refresh. The format persists across setValue calls; you don't need to restate it every time new data lands in the cell.

One edge case: if you paste values into a formatted range using 'Paste values only' from the Sheets UI, the format survives. If a script uses setValue() or setValues(), the format also survives. The format only disappears if something calls setNumberFormat('@') — '@' is the 'plain text' pattern — or if a script clears the cell with clearContent() followed by clearFormats().

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

Why does my date column show a number like 46210 instead of the date after I call setNumberFormat?: The cell is storing a text string that looks like a date, not a date serial number. setNumberFormat only controls how a numeric value is displayed — it can't reinterpret text. Fix it by replacing the cell contents: parse the string with new Date() and write it back with setValue(), which Sheets will then store as a serial. After that, setNumberFormat('yyyy-mm-dd') works as expected.
How do I apply different formats to different columns in one function call?: Use setNumberFormats() (plural) on a multi-column range. It accepts a 2D array matching the shape of the range — one pattern string per cell. If all cells in a column share the same pattern, passing a single-column range to setNumberFormat() (singular) is simpler and faster.
Does the pattern '$#,##0.00' work for non-USD currencies?: Replace '$' with any literal currency symbol: '€#,##0.00', '£#,##0.00', '¥#,##0'. The symbol is treated as a literal character in the pattern. For locale-aware formatting that picks the symbol automatically, you can use the special pattern '[$$-409]#,##0.00' where 409 is the locale code, but for most cases a hard-coded symbol is clearer and more predictable.
My percentage cells are showing values 100x too large — what's wrong?: The '0%' pattern multiplies the stored value by 100 for display. If you store 17 and format it as '0%', Sheets shows '1700%'. Store the decimal form instead: 0.17 formatted as '0%' shows '17%', and 0.174 formatted as '0.0%' shows '17.4%'. This matches how Sheets handles percentage cells set by hand — the UI stores the decimal, not the whole number.

// one good script a week