Number and Date Formats

Range.setNumberFormat() lets you apply any Google Sheets number format pattern to a range — currency, percentages, dates, custom patterns, and more. This keeps your sheet data readable and consistent without manual formatting.

Formatting the Amount Column as Currency

function formatAmountAsCurrency() {
  var sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName("Sales Tracker");

  var amountRange = sheet.getRange(2, 4, sheet.getLastRow() - 1, 1); // Column D
  amountRange.setNumberFormat("$#,##0.00");

  Logger.log("Amount column formatted as currency.");
}

The $299 in the sample data displays as $299.00, and $999 as $999.00.

Formatting the Date Column

function formatDateColumn() {
  var sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName("Sales Tracker");

  var dateRange = sheet.getRange(2, 8, sheet.getLastRow() - 1, 1); // Column H
  dateRange.setNumberFormat("MMM dd, yyyy"); // e.g. Mar 15, 2024

  Logger.log("Date column formatted.");
}

Applying Multiple Formats in One Pass

function applyAllColumnFormats() {
  var sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName("Sales Tracker");

  var lastRow = sheet.getLastRow();

  // Amount (Column D) → Currency
  sheet.getRange(2, 4, lastRow - 1, 1).setNumberFormat("$#,##0");

  // Date (Column H) → Readable date
  sheet.getRange(2, 8, lastRow - 1, 1).setNumberFormat("MMM dd, yyyy");

  // Customer Name (Column A) → Plain text, left aligned
  sheet.getRange(2, 1, lastRow - 1, 1).setHorizontalAlignment("left");

  // Region column (E) → centered
  sheet.getRange(2, 5, lastRow - 1, 1).setHorizontalAlignment("center");

  Logger.log("All column formats applied.");
}

Percentage Format for a Conversion Rate Column

If you add a Conversion Rate column to the tracker:

function formatConversionRate() {
  var sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName("Sales Tracker");

  var rateRange = sheet.getRange("I2:I100");
  rateRange.setNumberFormat("0.0%");

  Logger.log("Conversion rate column formatted as percentage.");
}

Writing Formatted Values from a Script

When writing values to a sheet via a script, set the format at the same time:

function writeFormattedSummary() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Dashboard") || ss.insertSheet("Dashboard");

  var data = ss.getSheetByName("Sales Tracker")
    .getRange(2, 1, ss.getSheetByName("Sales Tracker").getLastRow() - 1, 8)
    .getValues();

  var totalRevenue = data
    .filter(function(r) { return r[6] === "Closed"; })
    .reduce(function(sum, r) { return sum + r[3]; }, 0);

  var closedCount = data.filter(function(r) { return r[6] === "Closed"; }).length;

  sheet.getRange("A1").setValue("Total MRR").setFontWeight("bold");
  sheet.getRange("B1").setValue(totalRevenue).setNumberFormat("$#,##0");

  sheet.getRange("A2").setValue("Closed Deals").setFontWeight("bold");
  sheet.getRange("B2").setValue(closedCount).setNumberFormat("#,##0");

  sheet.getRange("A3").setValue("Last Updated").setFontWeight("bold");
  sheet.getRange("B3").setValue(new Date()).setNumberFormat("MMM dd, yyyy h:mm am/pm");

  Logger.log("Dashboard summary written.");
}

Common Format Pattern Reference

Pattern	Example Output
`$#,##0`	$1,299
`$#,##0.00`	$1,299.00
`0.0%`	42.5%
`#,##0`	1,299
`MMM dd, yyyy`	Mar 15, 2024
`yyyy-MM-dd`	2024-03-15
`dddd, MMMM dd`	Friday, March 15
`h:mm am/pm`	9 am
`@`	Plain text