Apps script doGet return not the same date as on sheet

Question

I am spotting some weird behavior in my Google Sheets / Apps Script. May be someone has already encountered this and have a solution?

I already checked my Spreadsheet Locale and Timezone setting to ensure that this setting is the same as timeZone setting in Google Apps Script.

appsscript.json file:

{
  "timeZone": "Asia/Ho_Chi_Minh",
  "dependencies": {},
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "webapp": {
    "executeAs": "USER_DEPLOYING",
    "access": "ANYONE_ANONYMOUS"
  }
}

I am pulling data from sheet log and use this data for a GET Endpoint from Apps Script.

	A	B
1	Date	Some data
2	16/07/2022	2.34
3	17/07/2022	1.18
4	18/07/2022	4.16

My script:

function doGet(e) {

  try {

    const sh = SpreadsheetApp.getActive().getSheetByName('log')
    const data = sh.getDataRange().getValues()

    return ContentService
      .createTextOutput(JSON.stringify({ success: true, data }))
      .setMimeType(ContentService.MimeType.JSON)

  } catch (e) {

    return ContentService
      .createTextOutput(JSON.stringify({ success: false }))
      .setMimeType(ContentService.MimeType.JSON)

  }

}

This script will be deployed as a Webapp then Google will provide me with a link. When I do a GET request to this Webapp link, this is what I received:

{
    "success": true,
    "data": [
        [
            "Date",
            "Some data"
        ],
        [
            "2022-07-15T17:00:00.000Z",
            2.34
        ],
        [
            "2022-07-16T17:00:00.000Z",
            1.18
        ],
        [
            "2022-07-17T17:00:00.000Z",
            4.16
        ]
    ]
}

When I read data from cell A2 on sheet log with

function test() {
  const sh = SpreadsheetApp.getActive().getSheetByName('log')
  const data = sh.getRange('A2').getValue()
  
  Logger.log(data)
}

The result is:

Sat Jul 16 00:00:00 GMT+07:00 2022

I expect the date return from Apps Script Endpoint should be the same as date on sheet, but it is different. Am I missing something here?

I am currently using getDisplayValues to "pin down" the date as displayed on sheet and receive this date as string.

Updates:

Thank you all for your valuable time and information on this subject. With this information, I am able to switch from using getDislayValues() to getValues() to retain the underlying date value. And then after that in my internal system, I will process to local time accordingly. Example code in Python using arrow package:

import arrow

s = '2022-07-15T17:00:00.000Z'
arrow.get(s, 'YYYY-MM-DDTHH:mm:ss.SSSZ').to('local').format('DD/MM/YYYY')

or in JavaScript:

const s = '2022-07-15T17:00:00.000Z'
new Date(s).toLocaleDateString('vi-VN')

Answer 1

For anyone who doesn't want to deal with JS / timezone complexities and want to keep as much of the problem on the Google Sheet, here is a simple trick.

Imagine that on one cell you have the date. As the cell knows it is a date, when you click on it a calendar will appear.

Somewhere else you format that date as you wish (this could be done in the initial cell as well, but on my case I need it with 2 formats, so 2 cells).

Now here is the trick. If on the cell that your App Script will read you specify Format > Number > Plain Text

When App Script reads that cell it will read text and not a date. So you App Script data will have exactly what you expect, that is, what you see on the sheet.

Note: The cell in orange has the formula =TEXT(D5;"yyyy-mm-dd")

Answer 2

Sat Jul 16 00:00:00 GMT+07:00 2022 and 2022-07-15T17:00:00.000Z are the same date in different timezone. JSON doesn't support timezones or date formats and they use Z zero offset ISO8601 format to format them as strings. MDN says

The instances of Date implement the toJSON() function by returning a string (the same as date.toISOString()). Thus, they are treated as strings.

The timezone is always zero UTC offset, as denoted by the suffix "Z".

Answer 3

Date objects are tricky beasts. As far as I can tell in this case the dates you're taking from the sheet have internally GMT+0 timezone always. If you want to show them somewhere with another timezone you have to do it directly: to get the wanted timezone (from the curren Spreadsheet, for example) and convert the date object into a string with this timezone. Something like this:

function get_date() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var tz = ss.getSpreadsheetTimeZone();
  var date_obj = sh.getRange('a2').getValue();
  var date_str = Utilities.formatDate(date_obj, tz, 'YYYY-MM-dd');
  Logger.log(date_str);
}

Answer 4

Yes, you are missing something.

Logger.log doesn't parse as string a Date object the same way that JSON.stringify does. Considering this, in order to make it easier to compare the output of your first script with the second, instead of

function test() {
  const sh = SpreadsheetApp.getActive().getSheetByName('log')
  const data = sh.getRange('A2').getValue()
  
  Logger.log(data)
}

use

function test() {
  const sh = SpreadsheetApp.getActive().getSheetByName('log')
  const data = sh.getRange('A2').getValue()
  
  Logger.log(JSON.stringify(data)) // Edited
}

In order to log the same value that is displayed on a Google Sheets cell, the easier way is, when reading the cell value(s), instead of using getValue / getValues use getDisplayValue / getDisplayValues.