CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Adam Glazier
Adam Glazier

Reputation: 85

date getValue() is one day off from getDisplayValue()

I have a cell that's date formatted and set to: "Sat, Nov 2, 2019".

But when I look up that cells date with a script, it's one day off (-1).

var date = new Date();
date = sheet_log.getRange(110, 1).getValue();
var dateDisplay = sheet_log.getRange(110, 1).getDisplayValue();
// date = Fri Nov 01 2019 18:00:00 GMT-0600 (CST)
// dateDisplay = Sat, Nov 2, 2019

Why would Google Sheets let me set a date using a calendar picker, yet compute a date minus one day in script?

Is there any way around this?

Upvotes: 0

Views: 603

Answers (1)

Tanaike
Tanaike

Reputation: 201388

How about this answer? Please think of this as just one of several possible answers.

Although I'm not sure about your actual condition, for example, the values of the timezone for the spreadsheet and the timezone for the script are different, such situation might occur.

  • The value retrieved by getValue() follows the timezone of the script.
  • The value retrieved by getDisplayValue() follows the timezone of the Spreadsheet.

The both timezones can be confirmed by the following script.

var timezone1 = SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone();
var timezone2 = Session.getScriptTimeZone();
Logger.log(timezone1)
Logger.log(timezone2)
  • If timezone1 is different from timezone2, it is considered that the reason of the issue is due to the different timezone. In this case, please set the timezone of Spreadsheet or script.
  • If timezone1 is the same with timezone2, it is considered that the reason of the issue is not due to the timezone. So it is required to investigate more for clarifying the reason of issue.

References:

If I misunderstood your situation, I apologize.

Upvotes: 1

Related Questions