Google Apps Script - force setValue to text

Question

I am using setValue() to put values from an array into spreadsheet cells. Some array elements contain data that is text, but is being parsed as numeric, eg: "6E5", and setValue() converts them (in that case the cell gets the numeric value 600000). Is there any way to force the cell to be a text value?

Answer 1

I think you're looking to set the number format. Do this before you setValue. See example below. You could also create all of your array values to be a formula. Example ="6E5" see an answer I posted here related to forms.

Something like this....

/** @OnlyCurrentDoc*/

function setRangeToBeText() {
  var newValue = [["6E5","7d","99"]];

  //test on first cell
  var theRange = SpreadsheetApp.getActiveSheet().getRange(1,1,newValue.length,newValue[0].length);

  //sets format first
  theRange.setNumberFormat('@');
  theRange.setValues(newValue);
}

Example setting Format:

Example WITHOUT setting format: ]