为什么使用Google Apps脚本将日期值的日期减少1天? [英] Why is the date value result off by 1 day with Google Apps Script?

问题描述

目标:我正在使用以下代码简单地测试我是否能够提取正确的日期和天数.

  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);}  

代码意图:假定getDate从单元格B4获取日期，该日期包括日期格式的值"2020-11-01".至于getDays，应该根据单元格B4的设置值获得天数.

问题:当我尝试注销getDate的结果时，它显示为:Sat Oct 31 20:00:00 GMT-04:002020.我不明白，因为B4中的值为2020-11-01.

问题:我想了解为什么会发生这种情况，并想知道如何根据B4单元格中的内容确定正确的日期.

有人可以帮忙吗?

解决方案

修改点:

• 当我看到从 2020-11-01 Sat Oct 31 20:00:00 GMT-04:00 2020 的值时>，我认为根据您的情况，Google Apps Script项目的时区可能与Google Spreadsheet的时区不同.为了对此进行检查，请使用以下脚本.

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

  • 运行此脚本后，可以在日志中看到Google Apps脚本项目和Google Spreadsheet的时区.
  • 我认为从 Sat Oct 31 20:00:00 GMT-04:00 2020 的值来看，根据您的情况，Google Apps Script项目的时区可能是 America/纽约.与此相关，当使用新的IDE创建新的Google Apps脚本项目时，默认时区为 America/New_York .我不确定这是错误还是当前的规范.
    • 此问题已被报告给问题跟踪器.参考.

我认为从上述情况来看，当更改时区时，您的问题可能会得到解决.那么下面的修改如何?

修改流程:

首先，请从电子表格中检查您的时区.在当前阶段，当电子表格创建为新电子表格时，您的时区已设置.

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

下一步，请将您的时区复制并粘贴到Google Apps Script项目中.在这种情况下，请使用检索到的时区修改清单文件( appsscript.json ).参考

 "timeZone":"###"，< ---请在此处设置您的时区. 

完成上述设置后，请再次测试脚本.

注意:

当然，我认为您可以使用Google Apps Script项目的时区来设置Google Spreadsheet的时区.但是，在这种情况下，Google Apps Script项目的时区可能与您的时区不同.请注意这一点.

参考文献:

• 清单
• getScriptTimeZone()
• getSpreadsheetTimeZone()

2021年4月6日更新:

在问题跟踪器中，我可以确认新IDE的这个错误与当时有关Google Apps脚本项目的区域.

• 关于该错误，当使用新的IDE创建新的Google Apps脚本项目时，GAS项目的时区始终为 America/New_York .在我所在的地区是亚洲/东京.

今天，当我对此进行测试时，我可以确认此错误已被删除.当我创建新的Google Apps脚本项目时， appsscript.json 中的 timeZone 的值为 Asia/Tokyo .这对我来说是正确的值.

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?

解决方案

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:

• Manifests
• getScriptTimeZone()
• getSpreadsheetTimeZone()

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.

这篇关于为什么使用Google Apps脚本将日期值的日期减少1天?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！