格式化从电子表格中读取的数字 [英] Formatting numbers read from spreadsheet

问题描述

我正在编写一个脚本，用于创建Google Apps电子表格内容的固定宽度文本输出。我使用 range.getValues（）方法检索当前范围的所有值，然后循环遍历所有内容并生成一段文本，看起来像一个很好的格式化的表格粘贴到固定宽度字体的电子邮件中。

我遇到的唯一问题是我无法复制数字格式。我可以使用 range.getNumberFormats（）来获取数字格式化字符串，但是我找不到在代码中应用该格式化字符串的方法。我尝试使用TEXT（值，格式）电子表格函数，但显然Google Apps脚本尚不支持从JavaScript代码本身调用电子表格函数（请参阅 p>

这四个单元格包括两个最棘手的格式来解决;日期/时间和科学记数法。但是使用新的方法，没有什么。

function demoDisplayValue（）{
var range = SpreadsheetApp.getActiveSheet（）。getRange（A1：B2）;

Logger.log（range.getDisplayValue（））;
Logger.log（range.getDisplayValues（））;
}


记录：

  [16-01-14 13：04：15：864 EST] 1/14/2016 0:00:00 
 [16-01 -14 13：04：15：865 EST] [[1/14/2016 0:00:00，5.13123E + 35]，[$ 3.53，1,123 lb]] 
  pre> 
I'm writing a script that creates fixed-width-text output of the contents of a Google Apps Spreadsheet. I retrieve all the values of the current range using the range.getValues() method, then I loop through everything a couple times and generate a block of text that looks like a nicely-formatted table when pasted into a fixed-width-font email.

The only issue I'm having is that I cannot replicate the number formatting. I can get the number formatting string using range.getNumberFormats(), but I cannot find a method for applying that formatting string within code. I tried using the TEXT (value, format) spreadsheet function, but apparently Google Apps Script does not yet support calling spreadsheet functions from within the JavaScript code itself (see this issue for proof).
解决方案
As of December 2015, the kludgy work-arounds can be discarded. Google has provided two methods to retrieve the literal string display values from cells. They haven't provided documentation yet, so here's a summary:

Range.getDisplayValue()

Returns the display value of the top-left cell in the range, as a String, containing the text value shown on the Sheets UI. Empty cells will return an empty string.

Range.getDisplayValues()

Returns the rectangular grid of display values for this range. Returns a two-dimensional array of Strings, indexed by row, then by column. Empty cells will be represented by an empty string in the array. Remember that while a range index starts at 1, 1, the JavaScript array will be indexed from [0][0].

Example:

Say we have a range in a spreadsheet that we are interested in, like this:



Those four cells include two of the trickiest formats to work around; date/time and scientific notation. But with the new methods, there's nothing to it.
function demoDisplayValue() {
  var range = SpreadsheetApp.getActiveSheet().getRange("A1:B2");

  Logger.log( range.getDisplayValue() );
  Logger.log( range.getDisplayValues() );
}


Log:

[16-01-14 13:04:15:864 EST] 1/14/2016 0:00:00
[16-01-14 13:04:15:865 EST] [[1/14/2016 0:00:00, 5.13123E+35], [$3.53, 1,123 lb]]


                        
这篇关于格式化从电子表格中读取的数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！