Transform dates for add-on transfer

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.

Example

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

var values = [[new Date(), 1,'a','',1120,new Date()],[new Date(), 2,'b','12/5/78',11,new Date(2016,11,17)]];

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

Logger.log(Utils.transformDates (values));

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

[[1.480250420892E12, 1.0, a, , 1120.0, 1.480250420892E12], [1.480250420892E12, 2.0, b, 12/5/78, 11.0, 1.4819328E12]]

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.

 // custom transform
  Logger.log(Utils.transformDates (values, function (value) {
      return value.toISOString();
	  }));

result

[[2016-11-27T12:40:20.892Z, 1.0, a, , 1120.0, 2016-11-27T12:40:20.892Z], [2016-11-27T12:40:20.892Z, 2.0, b, 12/5/78, 11.0, 2016-12-17T00:00:00.000Z]]

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.

// do something different depending on the row offset
  Logger.log(Utils.transformDates (values, function (value,rowOffset) {
    return rowOffset > 0 ? value.toISOString() : 'Month ' + value.getMonth ();
	}));

result

[[Month 10, 1.0, a, , 1120.0, Month 10], [2016-11-27T12:40:20.892Z, 2.0, b, 12/5/78, 11.0, 2016-12-17T00:00:00.000Z]]

// do something different depending on the column offset
  Logger.log(Utils.transformDates (values, function (value,rowOffset , columnOffset) {
    return columnOffset === 0 ? value.toISOString() : value.getTime();
	}));

result

[[2016-11-27T12:40:20.892Z, 1.0, a, , 1120.0, 1.480250420892E12], [2016-11-27T12:40:20.892Z, 2.0, b, 12/5/78, 11.0, 1.4819328E12]]

The code

/**
   * you can't transfer date objects between server and client in a spreadsheet
   * @param {[[]]} values the values to transform
   * @param {function} [tranformFunc] an optional function - by default they'll be changed to timestamps
   * @return [[]] if any dates are found they'll be transformed
   */
  Utils.transformDates = function (values , transformFunc) {
    
    // how to check for a date
    function isDate_ (value) {
      return (typeof value === "object" && value.constructor && value.constructor.name === "Date");
    }
    
    // the default transform
    transformFunc = transformFunc || function (value , rowOffset , columnOffset) {
      // check if its a date
      return value.getTime();
    };
    
    // mow through the data
    return values.map (function (row , rowOffset) {
      return row.map (function (cell , columnOffset) {
        return isDate_ (cell) ? transformFunc (cell, rowOffset, columnOffset) : cell;
      });
    });
    
	};

Why not join our forum, follow the blog or follow me on twitter to ensure you get updates when they are available.