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

Question

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:

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

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?

Answer 1

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.

Answer 2

I found a way around this by doing the following. I'm getting the time zone of the current sheet and the sheet that I'm copying from, changing the current one to match the source and then changing it back after copying. Seems to work great!

// Super annoying hack... Copying dates across time zones changes the timestamps and sometimes changes the dates so we're temporarily adjusting the time zone while we copy.

var DestTimeZone = SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone();

var SourceTimeZone = SourceSpreadSheet.getSpreadsheetTimeZone();

SpreadsheetApp.getActiveSpreadsheet().setSpreadsheetTimeZone( SourceTimeZone );

// Overwrite data

Sheet.clearContents();
Sheet.getRange(1,1,SourceRows,SourceColumns).setValues(SourceData);

// Reset the timezone.

SpreadsheetApp.getActiveSpreadsheet().setSpreadsheetTimeZone( DestTimeZone );