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.
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@'!