Set cell format with google apps script

Question

It is possible to set number format to a cell with google apps script like

cell.setNumberFormat("0.000");

I need to set cell format to a text, so that any date-like values were not automatically converted.
How can I do this?

Thread closed because 'This question already has answers here: ultimate short custom number formatting - K, M, B, T, etc., Q, D, Googol'. Am I missing something, thread started 2016 was closed because is ALREADY answered in a thread of 2021?.. — Mykhaylo Adamovych
– Mykhaylo Adamovych, Commented Nov 8, 2021 at 8:42

Aleister Tanek Javas Mraz · Accepted Answer · 2018-02-13 01:23:55Z

82

The setNumberFormat function in the Google Apps Script is not well documented. 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.

So here is the different example on how to set the setNumberFormat function.

As plain text for a single cell:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];

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

// Plain text
cell.setNumberFormat("@");

As date for single column:

// Single column
var column = sheet.getRange("B2:B");

// Simple date format
column.setNumberFormat("M/d/yy");

As currency for entire sheet:

// Entire sheet
var range = sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns());

// Money format
range.setNumberFormat("$#,##0.00;$(#,##0.00)");

Source: Cell Number Formatting with Google Apps Script

edited Feb 13, 2018 at 1:23

Aleister Tanek Javas Mraz

5373 gold badges11 silver badges30 bronze badges

answered Jul 21, 2016 at 13:32

KENdi

7,7942 gold badges18 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MC9000 Over a year ago

not sure why, but the entire sheet formatting (3rd) example you show doesn't seem to work anymore. It ignores all the formats after the first semicolon! The link in the example doesn't work, either. Go figure. Sheets bug.

Collectives™ on Stack Overflow

Set cell format with google apps script

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related