CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets-api
HHeckner
HHeckner

Reputation: 5056

GoogleApps Utilities.formatDate returns wrong day

I have a Google spreadsheet and try to read a date from the sheet. The following script delivers the date as String. But the day is one too less.

function testDate() {
  var selectedSpreadsheetId = PropertiesService.getUserProperties().getProperty("spreadsheetId");
  var sheet = SpreadsheetApp.openById(selectedSpreadsheetId);

  var rows = sheet.getDataRange();
  var values = rows.getValues();
  var row = values[0];
  var test = "";
  var test  = Utilities.formatDate(new Date(row[0]), "GMT+1", "MM/dd/yyyy");     
  Logger.log("result is: "+test)
}

You need to create a Google Spreadsheet and enter the date 14.10.2017 in the upper left cell. The date given in the example is the GERMAN date format.

The result in the log is 10/13/2017 but should be 10/14/2017

I know that Google uses the "normal" Java SimpleDateFormat

Why?

Upvotes: 4

Views: 1554

Answers (1)

Serge insas
Serge insas

Reputation: 46794

You shouldn't hardcode the timezone like you did because your country uses daylight savings. You can use Session.getScriptTimeZone() instead or SpreadsheetApp.getActive().getSpreadsheetTimeZone()

in your code it goes like below :

  var test  = Utilities.formatDate(new Date(row[0]), Session.getScriptTimeZone(), "MM/dd/yyyy");     
  var test2  = Utilities.formatDate(new Date(row[0]), SpreadsheetApp.getActive().getSpreadsheetTimeZone(), "MM/dd/yyyy");     
  Logger.log("result is: "+test)
  Logger.log("result2 is: "+test2)

enter image description here

Upvotes: 7

Related Questions