How do I get the Formatted Value of a cell using Google Apps Script

Question

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

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.

Thank you.

Answer 1

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

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

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

Answer 2

There's a lib for that! SheetConverter library.

Gist here.

Here's how you could get a single formatted cell:

var formattedValue = SheetConverter.convertCell(value, format);

You'll find other goodies to convert whole ranges into HTML tables, or generate the HTML for a single cell (including fonts, background colors, text colors, and most other formatting elements).

Disclosure: I'm the library author. But that's not my picture on the "ScriptExamples" site!