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().

Does 1,234 mean one thousand or a decimal?

A display format cannot repair an amount that was interpreted incorrectly during import. Paste the original amount column and choose its decimal and grouping convention. This free browser check flags ambiguous separators, unsupported formats and long digit strings with source line numbers. It does not convert values, infer your locale or change a Sheet.

Review number interpretations

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