Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 254
user2961572 Avatar asked Nov 06 '13 17:11

user2961572


People also ask

How do I get a cell value in Google Sheets?

In Google Sheets, the formula INDEX() allows you to return the value of a cell by specifying which row and column to look at in the specified array. =INDEX(A:A,1,1) for example will always return the first cell in column A.

How do you reference a cell in Google script?

Type = followed by the sheet name, an exclamation point, and the cell being copied. For example, =Sheet1! A1 or ='Sheet number two'! B4 .


2 Answers

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

like image 175
Serge insas Avatar answered Oct 23 '22 03:10

Serge insas


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!

like image 28
Mogsdad Avatar answered Oct 23 '22 05:10

Mogsdad