google-apps-scriptgoogle-sheetssetvalue

Google Apps Script - force setValue to text


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?


Solution

  • 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: enter image description here

    Example WITHOUT setting format: enter image description here]