CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Solana_Station
Solana_Station

Reputation: 311

Why is the date value result off by 1 day with Google Apps Script?

Goal: I'm using the following code to simply test if I am able to extract the correct date and the number of days.

function myFunction() {
  const spreadSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Purchases (Beta)');
  const getDate = new Date(spreadSheet.getRange('B4').getValue());
  const getDays = (new Date(getDate.getFullYear(), getDate.getMonth() + 1, 0)).getDate();
  Logger.log(getDate);
  Logger.log(getDays);
}

Intention of the code: The getDate is supposed to get the date from cell B4, which it includes a date formatted value as "2020-11-01." As to getDays, it's supposed to get the number of days based on the set value of cell B4.

Issue: When I tried to log out the result for getDate, it shows as: Sat Oct 31 20:00:00 GMT-04:00 2020. Which I don't understand because the value in B4 is 2020-11-01.

Question: I want to understand why this happens and want to know how I can out put the correct date based on what is in the cell of B4.

Can someone help?

Upvotes: 3

Views: 2256

Answers (2)

Tanaike
Tanaike

Reputation: 201378

Modification points:

  • When I saw the value of Sat Oct 31 20:00:00 GMT-04:00 2020 retrieved from the cell value of 2020-11-01, I thought that in your situation, the timezone of Google Apps Script project might be different from that of the Google Spreadsheet. In order to check this, please use the following script.

      console.log(Session.getScriptTimeZone())
  console.log(SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone())
    • When this script is run, the timezones of Google Apps Script project and the Google Spreadsheet can be seen at the log.
    • I thought that from the value of Sat Oct 31 20:00:00 GMT-04:00 2020, in your situation, the timezone of Google Apps Script project might be America/New_York. About this, when new Google Apps Script project is created with new IDE, the default timezone becomes America/New_York. I'm not sure whether this is a bug or the current specification.
      • This issue has already been reported to the issue tracker. Ref.

I thought that from above situation, when the timezone is changed, your issue might be resolved. So how about the following modification?

Modification flow:

At first, please check your timezone from Spreadsheet. In the current stage, when the Spreadsheet is created as new Spreadsheet, your timezone is set.

console.log(SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone())

As the next step, please copy and paste your timezone to Google Apps Script project. In this case, please modify the manifest file (appsscript.json) using the retrieved timezone. Ref

"timeZone": "###", <--- Please set your timezone here.

After above settings, please test your script again.

Note:

Of course, I think that you can set the timezone of Google Spreadsheet using the timezone of Google Apps Script project. But, in this case, the timezone of Google Apps Script project might be different from your timezone. Please be careful this.

References:

Updated at April 6, 2021:

At the issue tracker, I could confirm that this bug of new IDE about the time zone of Google Apps script project.

  • About the bug, when new Google Apps Script project is created using new IDE, the time zone of GAS project is always America/New_York. In my area, it's Asia/Tokyo.

Today, when I tested this, I could confirm that this bug was removed. When I created new Google Apps Script project, the value of timeZone in appsscript.json is Asia/Tokyo. This is the correct value for me.

Upvotes: 5

Marios
Marios

Reputation: 27348

Issues (updated):

The main issue most likely has to do with the discrepancy between the timezone you see in the script editor and the timezone of the spreadsheet file. You can change/check both to make sure they are the same.

Another issue in the code is related to this answer and in this line:

new Date(getDate.getFullYear(), getDate.getMonth() + 1, 0)
  • You have specified 0 as the third argument of the new Date() which is out of range and this is why you get the day of the previous month (October).

In the very same line you add 1 to the current month of the cell. This is going to give you december (assuming you fix the day index in the 3rd argument). As you can read here:

Integer value representing the month, beginning with 0 for January to 11 for December.

Solution:

So overall, if you fix these two issues, this line should be:

const getDays = (new Date(getDate.getFullYear(), getDate.getMonth(), 1)).getDate();

Regarding your other question:

I want to understand why this happens and want to know how I can out put the correct date based on what is in the cell of B4.

as I explained in the comment, if you want the exact format as it is displayed in the cell, you should use getDisplayValue() instead of getValue().

More details on why in this question:

Difference between getValue() and getDisplayValue() on google app script

Illustration:

When I execute this code:

function myFunction() {
  const spreadSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Purchases (Beta)');
  const getDate = new Date(spreadSheet.getRange('B4').getValue());
  const getDate2 = spreadSheet.getRange('B4').getDisplayValue();
  const getDays = (new Date(getDate.getFullYear(), getDate.getMonth(), 1)).getDate();
  Logger.log(getDate);
  Logger.log(getDate2);
  Logger.log(getDays);
}

I get these logs:

enter image description here

with the date in cell B4 to be: 2020-11-01, so everything works fine to me except for the issues I described above.

Upvotes: 1

Related Questions