javascript - Formatting numbers read from spreadsheet

Question

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

Answer 1

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]]