3

I have been using Apps Script in Google Sheets with methods getValues and setValues. The returned values in the arrays are further manipulated, and they are meant to be JS Strings and set in cells as Text.

After using setValues the values inside cells are interpreted as (special format) numbers, if the string allows it, and inside the cells they are actually numbers even though the values inside the array are JS Strings.

Can we tell via script that these string values be set as Text in cells?

The following test code initializes an array of strings but when I inspect the ell values they are actually numbers:

    function setValuesTest() {
      var LN_DEST = 11;
      var ss = SpreadsheetApp.getActive();
      var currentSheet = ss.getActiveSheet();

      var valuesAr = [];
      var setAr = [];

      valuesAr[0] = ['18:20', '3', '27/05/19', '27/5/2019', '01970123456'];
      valuesAr[1] = ['3.5', '3,5', '27/05/19', '2019/5/20', '01970123456'];
      valuesAr[2] = ['$3.5', '£3.5', '£2,5', '2,000,000', '2.000.000'];


      for (var i = 0; i < valuesAr.length; i++) {
        setAr[0] = valuesAr[i];
        currentSheet.getRange(LN_DEST + i, 1, 1, setAr[0].length).setValues(setAr);
      }

      ss.toast('Complete!', 'END', -1);
    }

I would like to have those values in cells as text keeping the characters literally. So, if there is 27/05/19 in the array then have Text 27/10/19 rather than a number or have in cell text 01970123456 rather than number 1970123456. If possible, I would prefer to avoid forcing text preceding values with " ' " as I am not sure what effect would that have on further usages of getValues and SetValues methods on the cells. (Another disadvantage of this behaviour is that this data interpretation seems to depend on the locale system.)

Alfonso Tienda
  • 3,442
  • 1
  • 19
  • 34
user1938578
  • 393
  • 5
  • 20

1 Answers1

4
  • You want to put the values to the Spreadsheet as the string.
  • You don't want to put the values using the single quote ' at the top of value.

If my understanding is correct, how about using setNumberFormat()? Please think of this as just one of several answers.

Modified script:

Please modify your script as follows.

From: 
currentSheet.getRange(LN_DEST + i, 1, 1, setAr[0].length).setValues(setAr);
To: 
currentSheet.getRange(LN_DEST + i, 1, 1, setAr[0].length).setNumberFormat("@").setValues(setAr);

References:

If I misunderstood your question and this was not the direction you want, I apologize.

Tanaike
  • 181,128
  • 11
  • 97
  • 165