Google-sheets – Misinterpreted hours and minutes in Google Apps Script while reading Google Spreadsheet data

dategoogle sheetsgoogle-apps-script

I have a datetime columns in Google Spreadsheet which I'd like to iterate and use "hour" value to calculate something in custom Google Script. This is an example of a value I have in Spreadsheet:

enter image description here

My script looks like this:

function CALC_TEST() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("jan");
  var range = sheet.getRange("E3:F14");
  var values = range.getValues();

  for (var i = 0; i < values.length; i++) {
    Logger.log(values[i][1]);
  }  
}

If I take a look at my logs, the value is interpreted in a really strange way – as Sat Dec 30 23:28:00 GMT+00:00 1899

enter image description here

My Spreadsheet's timezone is set to GMT+00:00 and I get the same result no matter if I try to parse the value with getHours() or getUTCHours() in my Script (obviously, since the value is already wrong before parsing :)).

Why is this happening? How is it possible that 22:00 becomes 23:28 all the sudden?

Best Answer

Switching the timezone to a different one and then switching back fixed the problem. Might be related to one of the bugs already reported to Google.

Related Topic