Datetime seems to break Google Apps Script client-side deserialization

Question

I've uncovered an odd bug with Google Apps Script's web interface that it can't seem to transport datetime values from the server side to the client.

// Code.gs

function fetchData(){
  var spreadsheet = SpreadsheetApp.openByUrl(SHEET_URL)
  var sheet = spreadsheet.getSheetByName("My sheet")
  var values = sheet.getDataRange().getValues()
  Logger.log(values)
  return values
}

// javascript.html
<script>
    $(function() {
        google.script.run.withSuccessHandler(console.log).fetchData()
    })
</script>

If I run the above without any dates in the "My sheet" spreadsheet, it works as expected, with the server side Logger.log and the client side console.log showing the same data. But if I input a date type value into the spreadsheet, the Logger.log will still show everything as expected, but the console.log will simply log null.

I checked the XHR and it appears that the data is in fact making it to the browser, but it looks like something about the de-serialization is breaking. If the date is listed as 7/7/21 in the spreadsheet, it is coming across as Wed Jul 07 00:00:00 PDT 2021 in the XHR.

Any ideas about how to fix are much appreciated! Thank you!

Answer 1

You are describing the documented behavior of google.script.run:

Requests fail if you attempt to pass a Date, Function, DOM element besides a form, or other prohibited type, including prohibited types inside objects or arrays. Objects that create circular references will also fail, and undefined fields within arrays become null.

See the reference given by @Cooper.

To pass datetime values between the client and the server, serialize before sending them across. The easiest way is usually to use date.getTime() and pass as integer. Try this:

const values = sheet.getDataRange().getValues().map(value => {
  (Object.prototype.toString.call(value) === '[object Date]')
  ? value.getTime()
  : value
});

On the receiving side, use const date = new Date().setTime(value) to convert the integer back to a Date object.

If you do not know the locations of dates in the spreadsheet, but need to detect them in data, you may want to use a magic prefix like 'date:' + value.getTime().