Format a Google Sheets cell in plaintext via Apps Script

Question

Using Google Apps Script, I want to set the format for a Google Sheets cell to plain text.

The reason I want to do this is because I want to show a date in the U.S. date format (MM/DD/YYYY). [We can assume the locale in the OP's Google account is set to use DD/MM/YYYY, a more common date format. –Ed.]

I create the string like this:

var thisDate = Utilities.formatDate (new Date (), SpreadsheetApp.getActiveSpreadsheet (). getSpreadsheetTimeZone (), "MM / d / yyyy");

... which returns the date in the U.S. format, 06/13/2012, which is what I want. However, when I set the value of a cell to that string:

sheet.getRange (1, n). setValue (thisDate);

... the date is formatted into the cell according to my locale, 13/06/2012, not the U.S. format.

The following operation also fails, because the date is returned in the standard format, not the U.S. format:

sheet.getRange (1, n). getValue () == "06/13/2012"

When the cell is formatted as plain text, and not a date, everything works fine.

So, my question is, how to format a cell using JavaScript.

Answer 1

The other answer, to set the format to 'plain text' in javascript, doesn't work. However, this does:

sheet.getRange(1,n).setNumberFormat('@STRING@');

So the magic value for formatting text programmatically is '@STRING@'!

Answer 2

A complement to Christian good answer : I have noticed that if your target cell has already been set to text format (manually), setting a value with range.setValue() that might not been interpreted as a text (like a date, or a number), will change automatically the number format. So the good solution, if you want definitely to keep a text format is

range.setValue("01293849847").numberFormat("@");

and not

range.numberFormat("@").setValue("01293849847");

Answer 3

Another way to force the value to be saved as plain text:

Prepend a quotation mark: '. This has no effect on displayed value and also by reading the value back the quatation mark is not included.

let dateStr = '07-07-20017';
sheet.getRange(x, y).setValue("'" + dateStr);

let backStr = sheet.getRange(x, y).getValue();
assert(dateStr === backStr);

Answer 4

Unfortunately the text of the plan did not fit. Document as soon as the cell gets something like a date, just format the cell. Found a way not to create date)

var thisDate1 = Utilities.formatDate (new Date (), SpreadsheetApp.getActiveSpreadsheet (). getSpreadsheetTimeZone (), "MM//d//yyyy");

Sorry so late reply. All hands did not reach.

Answer 5

I did it like below and it works as I expected. My B column show correct zero filled 3 digit number (e.g., 004) and my C column shows correct zero filled 10 digit number (e.g., 0060000001) as texts. I downloaded the file it converted to Excel as texts with the padded zeros. I think it would do the trick with the Date as a text as well.

winSS.getRange("US!B2:B").setNumberFormat("000").setNumberFormat("@"); winSS.getRange("US!C2:C").setNumberFormat("0000000000").setNumberFormat("@");