脚本"setValues"方法将字符串解释为单元格中的数字 [英] Script 'setValues' method interprets strings as numbers in cells

问题描述

我一直在Google表格中使用方法 getValues 和 setValues 的Apps脚本.数组中返回的值会被进一步处理，它们应为JS字符串，并在单元格中设置为文本.

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.

使用 setValues 后，如果字符串允许，则将单元格内的值解释为(特殊格式)数字，即使数组内的值为JS字符串，单元格内的值实际上也是数字

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?

以下测试代码初始化了一个字符串数组，但是当我检查ell值时，它们实际上是数字:

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);
    }

我想在单元格中将这些值作为文本保留字面上的字符.因此，如果数组中有27/05/19，则文本27/10/19而不是数字，或者单元格文本01970123456而不是数字1970123456.如果可能的话，我宁愿避免使用""，因为我不确定对单元格上的 getValues 和 SetValues 方法的进一步使用会产生什么影响.(此行为的另一个缺点是，这种数据解释似乎取决于语言环境系统.)

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.)

推荐答案

• 您要将这些值作为字符串放入电子表格.
• 您不想在值的顶部使用单引号'放置值.

如果我的理解正确，那么如何使用 setNumberFormat()?请认为这只是几个答案之一.

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

请按如下所示修改脚本.

Please modify your script as follows.

currentSheet.getRange(LN_DEST + i, 1, 1, setAr[0].length).setValues(setAr);

至:

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

参考文献:

• setNumberFormat()
• 数字格式令牌

如果我误解了你的问题，而这不是你想要的方向，我深表歉意.

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

这篇关于脚本"setValues"方法将字符串解释为单元格中的数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！