如何使用Google Apps脚本获取单元格的格式值 [英] How do I get the Formatted Value of a cell using Google Apps Script

问题描述

我想使用Google Apps脚本通过将Google电子表格中选定的单元格中的值进行串联来创建字符串.问题是我不知道单元格是否包含数字，日期或文本.当值是数字或日期时，我想获取格式化的值，即它在电子表格中的显示方式.

I want to use Google Apps Script to create a string by concatenating values from a selection of cells within a Google Spreadsheet. The problem is that I won't know whether the cells contain numbers, dates, or text. When the value is a number or date, I want to get the formatted value, the way it is displayed in the spreadsheet.

例如，这是一个函数，它将返回命名范围的值.

For example, here is a function that will return the value for a named range.

function getValueByName() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var theRange = ss.getRangeByName("theNamedRange");
    return theRange.getValue();
}

如果命名范围包含日期，格式为11/6/2013，则此函数返回的值为"Wed Nov 06 2013 01:00:00 GMT-0700(MST)".

If the named range contains a date, formatted as 11/6/2013, then this function returns the value as "Wed Nov 06 2013 01:00:00 GMT-0700 (MST)".

我知道我想要的结果将导致十进制值的舍入错误，但是在这种情况下我并不在意.我只想获取格式化的值.

I understand that what I am wanting would result in rounding errors for decimal values, but I don't care about that in this case. I just want to get the formatted value.

谢谢.

推荐答案

您将必须了解来自每个单元格的数据类型，并在每种情况下以正确的方式进行处理...

You will have to know the type of data coming from each cell and handle it the right way in each case...

如果是字符串，请保持原样；如果是日期，请使用Utilities.formatDate来显示所需的方式(

if it's a string, keep it as it is, if it's a date use Utilities.formatDate to show it the way you want (see doc here) and if it's a number you can also format it as necessary using Utilities.formatString (see doc here).

要获取类型，请使用instanceof，例如，请查看

To get the type, use instanceof, for an example have a look at this recent post

这篇关于如何使用Google Apps脚本获取单元格的格式值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！