When I look at a date on the spreadsheet, I see 11/1/2005.
When I read it into google scripts, it changes to 10/31/2005.
I am in PDT, my spreadsheet is in PDT, (UTC-8).
Is there an easy way to read the date from the spreadsheet so it shows the same date as on the spreadsheet?
Maybe related to Question or Question
this is not working:
let date2 = Utilities.formatDate(date, Session.getScriptTimeZone(), "yyyy-MM-dd");
Here is as simple an example as I could think of:
function myFunction() {
let data = SpreadsheetApp.getActive()
.getSheetByName("Sheet1")
.getDataRange()
.getValues();
let header = data[0]
data.splice(0,1) // remove header
data.forEach(row => {
let date = row[0]
let month = date.getMonth() + 1 // zero based month
let day = date.getDate()
let year = date.getFullYear()
row[1] = month;
row[2] = day;
row[3] = year;
})
data.splice(0,0,header) // put header back
SpreadsheetApp.getActive()
.getSheetByName("Sheet1")
.getDataRange()
.setValues(data);
}
Date MM DD YYYY
11/1/2005 0:00:00 10 31 2005 <-- 1 day behind
11/1/2007 0:00:00 10 31 2007
11/1/2009 0:00:00 10 31 2009
11/1/2011 0:00:00 10 31 2011
11/1/2015 0:00:00 10 31 2015
11/1/2017 0:00:00 10 31 2017
11/1/2019 0:00:00 10 31 2019
11/1/2021 0:00:00 10 31 2021
Issues like this usually come about because the script project and the spreadsheet are in different timezones. Many script projects are in GMT while spreadsheets are in the user's default timezone.
The timezone difference does not necessarily cause problems, because the Date object still represents the same moment in time as the spreadsheet date, even when they are in different timezones.
To deal with the issue when creating a user-readable datetime text string, use Utilities.formatDate() to get the date in the timezone of the spreadsheet, like this:
const formattedDate = Utilities.formatDate(date, SpreadsheetApp.getSpreadsheetTimeZone(), 'yyyy-MM-dd HH:mm');
Use File > Spreadsheet settings to set the spreadsheet's timezone. It is not system dependent but can be set separately on every spreadsheet. In new spreadsheets, the default comes from the account settings of the user who created the spreadsheet.
I think whats happening is the date on the spreadsheet has no time and when you read it into GAS, GAS makes the assumption is that the original time on the spreadsheet was UTC, so when you read it, it's transformed to PDT, by subtracting 8 hours. So what you see is (date - 8 hours) PDT. e.g. starting with 11/01/2005, you see:
Fri Oct 31 2005 16:00:00 GMT-0800 (Pacific Standard Time)
To fix, you just need to add 8 hours back either:
let date = row[0]
let date1 = new Date(date)
date1 = new Date(date1.setHours( date1.getHours() + 8 )) // timezone adjustment
or, a better idea might be to pull the date offset from the date first, and use that for the adjustment. That might work if you go outside PDT.
let date = row[0]
let offset = date.getTimezoneOffset() // offset in minutes
let date2 = new Date(date)
date2 = new Date(date2.setMinutes(date2.getMinutes() + offset))
as a 1-line fix:
date = new Date(date.setMinutes(date.getMinutes() + date.getTimezoneOffset()))