Search code examples
dategoogle-apps-scriptdate-format

Problems with dates using Google Apps Script

I'm fairly new to Apps Script, so please bear with me.

Basically, I have a spreadsheet comprising several sheets with data. With Apps Script I loop through them to populate an array with entire rows based on a given condition that isn't relevant to the problem. Then I loop through the resulting array to extract the date located at array position 3 and when I try to Logger.log the resulting date I get weird results...

Here are two screenshots of data data from sheet 1 and data from sheet 2

As you can see, the dates I'm interested in are in the format dd-MM-YY, which is what I want to output, but the logger shows me a long format. I've tried logging that out and comparing it to the output after formatting them using the Utilities class and that's when I find the inconsistency. Here's what my code looks like:

for(j = 0; j < valuesArr.length; j++) {
  let rowValues = valuesArr[j];

  Logger.log(valuesArr[j][3]);

  let flDate = Utilities.formatDate(rowValues[3], "GMT+1","dd-MM-YYYY");
  
  Logger.log(flDate);

}

and this is what my logger.log looks like: Logger.log output

To begin with, the first logger outputs the date in the "GMT-5" timezone even though both the spreadsheet settings and my locale are "GMT+1". I've tried changing the locale of the spreadsheet using setSpreadSheetLocale() but nothing changed.

Then, the last logger output is actually one year after the actual date that is in the data sheet and I really can't make heads or tails as to why is that...

If the spreadsheet is going to consistently give me the dates with the wrong locale, I can live with that, but if the dates that are in the datasheets are not going to be same as the ones I get via apps script, then I have a big problem...

Any ideas?... Any help would be very much appreciated.

EDIT Even though @NAZIA's answer seemed to work at first, I now have found an even weirder problem as I add more dates to my data: Here's the dummy data (all dates ARE dates, picked with the date picker): dummy data

Here's my code:

for (g = 0; g < arrayWithValues.length; g++) {
  let monthDate = Utilities.formatDate(value[3], "GMT+1", "dd-MM-yyyy");

  Logger.log(monthDate);
  Logger.log(value[3]);
}

And this is my log... (look closely at the last value!) log

Can anyone explain this to me??

Solution

  • Ok, so I've finally figured this out.

    @nazia's answer helped for the first part of my problem, but wasn't enough to explain the second part.

    So for the first part, just use 'yyyy' instead of 'YYYY' because the latter refers to the year of the week, as stated by @nazia in his/her answer.

    The second part of the problem is due to day-time savings differences... Google shows the penultimate day of the month only for the months between March and October, with a timezone of 'GMT-4' to compensate for the one hour that we go forward...