The setNumberFormat function within Google Apps Script is not well documented in the API reference documentation. This function accepts a wide range of parameters in a single string that can be used to specify the format for a range of cells. Knowing the accepted formats will give you control over the data in your cells when using Google Apps Script for Google Sheets.


The following examples demonstrate how to use various string patterns to define the format of a particular range within your active spreadsheet. You can set the formatting of a single cell, a whole column starting from a specific cell or an entire spreadsheet. Additional resources can be found on the Google Sheet support page for number formats. It would be nice if there was a link to that support page from within the API reference.

Plain text for a single cell:

// Use these first two lines for all four examples
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];

// Single cell
var cell = sheet.getRange("B2");

// Plain text

Date for single column

// Single column, select every row from 2..n in column B
// To select a limited range put the ending row number (“B2:B10”)
var column = sheet.getRange("B2:B");

// Simple date format

Additional details about accepted date formats can be found here:

Currency for entire sheet:

// Select all rows and columns from a spreadsheet
var range = sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns());

// Money format

Additional currency and number formats can be found here: You can specify up to four parts for custom number formats; positive, negative, zero and text. You can even add color by including one of the compatible colors in the string format.

Custom number format with color

// Number format with color
var row = sheet.getRange("A1:D1");

// Sample values
var values = [
   [ "100.0", "-100.0", "0", "ABC" ],

Number format example with color.

This gives you a quick way to set conditional formatting without having to write custom logic. You can use this to quickly identify problematic data.