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.

CodePudding user response：

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.

CodePudding user response：

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".

CodePudding user response：

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);
}