Reading & writing dates from Google Spreadsheets

问题:

I am importing almost 30,000 dates from one Google Spreadsheet to another with a Google Apps Script. My import from a sheet of 10,000 works fine, but the larger one doesn't. For the larger import, all dates are being imported 1 date in the past, so 1/1/2018 becomes 12/31/2017, etc.

I know Javascript handles dates a bit unusually, so I'm not sure why it's failing on one and working on the other. But mostly, I'm not sure how to increment the entire array (actually array of arrays) at once).

When I checked the date values in the GAS debugger, I noticed that the January 1, 2018 date was being returned as Sun Dec 31 17:00:00 GMT-07:00 2017. The working sheet is returning Mon Jan 01 00:00:00 GMT-06:00 2018. I checked the properties on both script files and the underlying spreadsheets, and both are set to GMT-7.

EDIT

First 12 rows of dates:
January 1, 2018
January 1, 2018
January 1, 2018
January 1, 2018
January 1, 2018
January 1, 2018
January 1, 2018
N/A
N/A
January 1, 2018
January 2, 2018

Output: 12/31/2017
12/31/2017
12/31/2017
12/31/2017
12/31/2017
12/31/2017
12/31/2017
N/A
N/A
12/31/2017
1/1/2018

The code I'm using is destination.getRange(1,7,lastRow+1).setValues(source.getRange(2,columns[0],lastRow+1).getValues()); I understand is is probably a timezone issue, but especially across an entire array, I don't know what to do about it.

Desired behavior: The output to be an exact copy from the first rows of the source data.

I hope that is clear/good enough. If there's anything else I need to do to get this out of on-hold, please let me know.

回答1:

The answer came through trying things from the comments, so I'm answering this to actually give what happened.

I had checked in File > Spreadsheet Settings in the Google Sheets UI, and File > Project Properties from the Script Editor, to verify that the time zone settings were the same - and they were.

However, when I ran getSpreadsheetTimeZone() from the Script Editor, its output was blank. I manually changed the timezone to something else and then changed it back, and finally the script recognized the timezone and the issue was resolved.

I can't say what caused the Google Sheet's timezone setting to be wrong, only that getting it right made it all work.