Google Apps Script showing previous day-CodePudding

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

CodePudding user response：

The Date object represents the same moment in time as the spreadsheet date, but they are in different timezones. To get the date as a text string in the timezone of the spreadsheet, use:

const formattedDate = Utilities.formatDate(date, SpreadsheetApp.getSpreadsheetTimeZone(), "yyyy-MM-dd");

CodePudding user response：

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()))