Force cell to be interpreted as plain text?

Question

Following sample code gets interpreted as Date formatted cell which is not my intent. It is supposed to be plain text. Calling clearFormat() on the Range is unhelpful either.

function myFunction() {
  SpreadsheetApp.getActiveSheet().getRange(1, 1).setValue("11-2");
}

Answer 1

Based from this blog, you can set the formatting of a single cell, a whole column starting from a specific cell or an entire spreadsheet. You can find additional resources on the Google Sheet support page for number formats.

// 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
cell.setNumberFormat("@");

Also from this google forum, you can use range.setNumberFormat('@STRING@'); to format cells to plain text.

Check these links:

• Format a Google Sheets cell in plaintext via Apps Script

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

• Set format as plain text : Google SpreadSheet : Apps script

Hope this helps!

Answer 2

It looks like a number when the spreadsheet parser encounters it first so it is stored as a number inside it's system.

A standard way of forcing the parser to accept anything as being text is to pre-pend the input with an apostrophe '

So your script becomes:

function myFunction() {
   SpreadsheetApp.getActiveSheet().getRange(1, 1).setValue("'11-2"); 
}