CODEWITHSUNDEEP
google-apps-script
user2961572
user2961572

Reputation: 83

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

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.

Upvotes: 8

Views: 15988

Answers (3)

Frederik Wordenskjold
Frederik Wordenskjold

Reputation: 10231

I believe the existing answers are now outdated, as a new function getDisplayValue() seems to be what OP is looking for.

Using your example, we can get the formatted value, in this case 30%, of a range (cell) like this:

function getValueByName() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var theRange = ss.getRangeByName("A1Notation");
  var value = theRange.getValue() // 0.3
  var formattedValue = theRange.getDisplayValue(); // 30%
  return formattedValue;
}

This returns the displayed value of the top-left cell in the range, so useful if the range only has one value. Use getDisplayValues() for a larger range.

Upvotes: 3

Mogsdad
Mogsdad

Reputation: 45750

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!

Upvotes: 2

Serge insas
Serge insas

Reputation: 46812

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

Upvotes: 2

Related Questions