Problems with dates using Google Apps Script

Question

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 and

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:

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):

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!)

Can anyone explain this to me??

NightEye · Accepted Answer · 2021-03-19 14:49:02Z

1

I'm not sure why there is 1 year that happened, but try this instead:

Code:

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

Use yyyy instead of YYYY. yyyy is the year while YYYY is year of the week. See reference for more details.

Comparison of yyyy vs YYYY run:

Reference:

yyyy vs YYYY

answered Mar 19, 2021 at 14:49

NightEye

11.3k2 gold badges8 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

yago Over a year ago

Great! That was it! How simple, yet how blind I was to the answer...

yago Over a year ago

Unfortunately, @nazia's answer did not solve the problem entirely, see Edit

yago · Accepted Answer · 2021-03-22 13:21:08Z

0

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...

answered Mar 22, 2021 at 13:21

yago

1784 silver badges13 bronze badges

1 Comment

NightEye Over a year ago

I'm glad you were able to find out the issue on the second one.

Collectives™ on Stack Overflow

Problems with dates using Google Apps Script

2 Answers 2

Code:

Comparison of yyyy vs YYYY run:

Reference:

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Code:

Comparison of yyyy vs YYYY run:

Reference:

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related