getRange.getValue returning wrong date from Google sheets

Question

var sheetdate = activeSheet.getRange(x, y).getValue() I was using this line to read a date from google sheets. Date in sheets is 2021-02-01. But sheetdate is returning value Sun Jan 31 13:30:00 GMT-05:00 2021. Actual output should be Mon Feb 1 17:35:00 GMT 05:30

It seem a timezone problem. Have you tried to convert the value (returned as GMT timezone) to your timezone)? — Sourcerer

NaziA NaziA · Accepted Answer · 2021-02-01T15:35:35

This is an issue with the timezone as Sourcerer mentioned.

There are many possible answers to this one but I prefer this one, formatting the date using Utilities as you can control your output:

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  date = sheet.getRange(1, 1).getValue()
  Logger.log(date);
  Logger.log(SpreadsheetApp.getActive().getSpreadsheetTimeZone());
  Logger.log(Utilities.formatDate(date, SpreadsheetApp.getActive().getSpreadsheetTimeZone(), "E MMM dd HH:mm:ss z yyyy"));
}

For the formatting, the one I used above "E MMM dd HH:mm:ss z yyyy" is trying to emulate the default date output. See the reference below and feel free to modify based on what you need to output for the date

Referenece:

SimpleDateFormat

getRange.getValue returning wrong date from Google sheets

1 Answers