Google Apps Script showing previous day

Question

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

I think GMT-7 is mountain time PDT is GMT-8 Check [here](https://www.timeanddate.com/time/map/) — Cooper, Oct 02 '21 at 00:17

doubleunary · Answer 1 · 2021-10-05T21:06:38.830

1

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.

edited Oct 05 '21 at 21:06

answered Oct 02 '21 at 08:02

doubleunary

13,842
3
18
51

SpreadsheetApp.getActive().getSpreadsheetTimeZone() returns "" on my system. – kztd Oct 03 '21 at 14:13
maybe true but not really helpful as the spreadsheet and the time zone I'm in are the same. – Karl S Oct 05 '21 at 17:01
Edited the answer regarding the difference between the timezones of the _script project_ and the _spreadsheet_. The timezone of the user at the keyboard does not make a difference after the spreadsheet has been created. – doubleunary Oct 05 '21 at 19:40
Got it, my app timezone is PDT on the manifest: "timeZone": "America/Los_Angeles", and its the same on the spreadsheet. GMT-8 Pacific time – Karl S Oct 05 '21 at 20:50
@KarlS did you try `Utilities.formatDate()` with `getSpreadsheetTimeZone()`? Is the result different from what you want to see? – doubleunary Oct 05 '21 at 21:06
getSpreadsheetTimeZone returned "", so that didn't work – Karl S Oct 06 '21 at 17:32
Try setting the spreadsheet timezone in **File > Spreadsheet settings**. – doubleunary Oct 06 '21 at 20:16

score 0 · Accepted Answer · answered Oct 03 '21 at 15:13

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

Google Apps Script showing previous day

2 Answers2