在工作表中插入字符串会导致插入数字 [英] Inserting string to sheet results in number inserted

问题描述

在我的Google Apps脚本中，我在电子表格中添加了一行.在要添加的这一行中，我尝试插入值'0102'的字符串，但是在插入时它会转换为数字102.有什么方法可以使用Google Apps脚本将值插入工作表中，而不会格式化这些类型的数字转换为整数，但将其保留为字符串?

In my Google Apps Script I an appending a row to my spreadsheet. In this row being appended, I am trying to insert a string of value '0102', however when inserted it converts to the number 102. Is there any way to insert a value into sheets with Google Apps Script that will not format these types of numbers into integers but leave them as strings?

推荐答案

您需要先将单元格设置为纯文本格式，然后再设置其值.在此答案中，(似乎未记录在案)技巧是setNumberFormat('@STRING@').例如

You need to format the cell as plain text before setting its value. From this answer, the (seemingly undocumented) trick is setNumberFormat('@STRING@'). For example,

sheet.getRange("A1").setNumberFormat('@STRING@').setValue('0102');

将单元格A1设置为纯文本格式，并设置值'0102'，该值将保留为字符串.

formats cell A1 as plain text, and sets the value '0102', which will remain a string.

不幸的是，appendRow是添加行并插入值的原子操作，没有留出格式化空间.因此，您将需要更多详细信息，例如

Unfortunately, appendRow, being an atomic operation of adding a row and inserting values, does not leave room for formatting. So you'll need something more verbose, for example

sheet.insertRowAfter(sheet.getLastRow());
var range = sheet.getRange(sheet.getLastRow() + 1, 1, 1, 3);
range.setNumberFormats([['', '', '@STRING@']]).setValues([['Boston', 'MA', '02201']]);  

此代码添加一行，并用Boston MA 02201填充其中的前三个单元格，并将邮政编码02201保留为字符串.

This code adds a row and fills the first three cells in it with Boston MA 02201, keeping the ZIP code 02201 as a string.

这篇关于在工作表中插入字符串会导致插入数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！