Wrong date extraction from Spreadsheet

Question

I want to extract a date from a spreadsheet that is in a "DD dd/mm/yyyy hh:mm:ss" format and put it in another spreadsheet and afterwards create an event in calendar, the problem is that when it is extracted it gets a different time zone than the one from the original spreadsheet, and it changes with different documents. The Utilities.formatDate() command doesn't work for me because it appears in the correct time zone.

Cell value : mon. 02/04/2018 12:00:00

Original Spreadsheet time zone : GMT -800

New Spreadsheet time zone : GMT -800 (also)

function Event(){
  var firstdoc =SpreadsheetApp.openById("ID"); // ouvre le fichier manifest list
  var firstdocsheet = firstdoc.getSheetByName("sheet");
  var date1= new Date(firstdocsheet.getRange(4,10).getValue());
  Logger.log(date1); 
  date1 = Utilities.formatDate(date1, "GMT-800", "dd-MM-yyyy HH:mm:ss");
  Logger.log(date1);   
}

This is what I get:

Logger.log([Mon Apr 02 21:00:00 GMT+02:00 2018)

Logger.log([02-04-2018 11:00:00)

I know that the code works but I don't get why it takes the wrong date since the beggining, any help please ?

GMT-800 isn't a valid timezone? Perhaps you mean GMT-08:00 ? — Casper
Yes sorry it was a mistake, but the thing is why it takes the date wrong. — PCamTre

Casper Casper · Accepted Answer · 2018-03-08T11:34:19

Verify your code with the example below:

function test() {
  var date = new Date('03/08/2018 09:51:00');
  Logger.log(date)
  Logger.log(Utilities.formatDate(date, "GMT-08:00", "dd-MM-yyyy HH:mm:ss"));
}

Log output:

[18-03-08 12:31:31:518 CET] Thu Mar 08 09:51:00 GMT+01:00 2018
[18-03-08 12:31:31:520 CET] 08-03-2018 00:51:00

The mechanism you're using seems to be right, so the problem is either in your cell value or the timezone as pointed out in the comment.

Wrong date extraction from Spreadsheet

1 Answers