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

The list of pre-supplied formats is here. For some formats, a javascript equivalent is relatively straight-forward. For others, it's extremely difficult. And handling user-defined custom formats - good luck with that.

Here's a screenshot showing cell content that has been replicated as html - not fixed, as you are doing, yet using the formats from the spreadsheet.

There are Google Apps Script helper functions that make it easier, Utilities.formatString() and Utilities.formatDate().

For dates & times, like "h:mm:ss am/pm", the spreadsheet formats are almost what is needed for the utility - I've found that you just need to tweak the am/pm designation for some formats:

  var format = cell.getNumberFormat();
  var jsFormat = format.replace('am/pm','a');
  var jsDate = Utilities.formatDate(
          date,
          this.tzone,
          jsFormat);

For dollars, e.g. "\"$\"#,##0.00":

  var dollars = Utilities.formatString("$%.2f", num);

For percent-formatted numbers, e.g. "0.00%":

  var matches = format.match(/\.(0*?)%/);
  var fract = matches ? matches[1].length : 0;     // Fractional part
  var percent = Utilities.formatString("%.Xf%".replace('X',String(fract)), 100*num);

For exponentials, like "0.000E+00", utilize the javascript built-in toExponential(), and tweak the output to look more like the spreadsheet:

if (format.indexOf('E') !== -1) {
  var fract = format.match(/\.(0*?)E/)[1].length;  // Fractional part
  return num.toExponential(fract).replace('e','E');
}

You can just do a string comparison against the stored spreadsheet formats to pick the right converter.

And what do I mean by extremely difficult? Try to get a formatter that comes up with the exact same numerator and denominator choices Sheets makes for # ?/? and # ??/??! In the spreadsheet, it's a matter of formatting - in a script, it's much more...

Answer 2

Indeed, you can't call spreadsheet functions directly. But you can set the format using setNumberFormats normally. Take a look at the docs here.

Answer 3

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

Answer 4

I found a JavaScript method for setting the number of digits after the decimal point called toFixed().

Here's the documentation: http://www.w3schools.com/jsref/jsref_tofixed.asp

num.toFixed(x) //where x = the number of digits after the decimal point.

My Google Apps script needed to return a message with a dollar value pulled from my spreadsheet. It looked terrible with 10 digits after the decimal point. This method applied to my variable solved the problem. I just added a $ in the text portion of the label.

Hope this helps!

Answer 5

Try the following google apps script

function NumberFormat()
{var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 var cell = sheet.getRange("A1:B10");
 // Always show 2 decimal points
 cell.setNumberFormat("0,00,000.00");
}