Formatting Numbers Read From Spreadsheet

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() m

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

functiondemoDisplayValue() {
  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]]

Solution 2:

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!

Solution 3:

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 partvar 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 partreturn 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...

Solution 4:

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");
}

Solution 5:

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