If you getValues from a spreadsheet, and those values contain dates, and you expect to be able to send them to an add-on, then you’ll find you get an error about unsupported data types. This is because Dates in JavaScript are special kind of objects, and can’t be stringified for transfer between client and server.


Here’s an example of some spreadsheet data, with a few date objects amongst the cells.

If you were to return that to a google.script.run() request, you’d get an error. You’d first have to transform the date into some kind of string or number. A typical thing to do would be to use one of the JavaScript Date methods to format into a string, or perhaps a timestamp, or you could use the Apps Script Utilities to do the same thing.

Here’s a convenient function to take care of that. (I have it in my Utils namespace – just change that to where you keep it, or get rid of the Utils prefix if you have it in your global space)

Convert to timestamps

By default, dates are converted to timestamps

any dates are converted to timestamps – everything else remains as is.

Convert to some other format

If you want some different kind of date transformation, you can provide a function as the second argument that will do the transform you want.Your custom function will be called for any value that is a date object. This example converts all dates to ISO format.


Do something based on the row number or column number

It may be you want to do a different kind of transformation based on the row number (for example headings might need different treatment). Your custom function will be passed the row and column number of the date being looked at.



The code

For more like this see Google Apps Scripts Snippets
Why not join our forum, follow the blog or follow me on twitter to ensure you get updates when they are available.