Script places the dates correctly on my goog cal, then misplaces them all one day early, then places the last one correctly again

huangapple go评论59阅读模式
英文:

Script places the dates correctly on my goog cal, then misplaces them all one day early, then places the last one correctly again

问题

脚本在我的Google日历上正确放置日期,然后将它们全部提前一天,然后再次正确放置最后一个。

从Google表格输入,显示在Google日历中

1/4/23 -> 1/4/23在Google日历中正确放置
1/8/23 -> 1/7/23 - 提前一天
2/5/23 -> 2/4/23 - 提前一天
6/18/23 -> 6/17/23 - 提前一天
10/15/23 -> 10/15/23 - 正确

功能添加事件() {
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var lr = ss.getLastRow();
var cal = CalendarApp.getCalendarById("@group.calendar.google.com");

var data = ss.getRange("A1:C" + lr).getValues();

for (var i = 0; i<data.length; i++) {
cal.createAllDayEvent(data[i][0], new Date(data[i][1]),
{description:'PRO: ' + data[i][2]});
}
}

Google表格中有三列:标题、日期和描述。

英文:

The script places the dates correctly on my Google Calendar, then misplaces them all one day early, then places the last one correctly again.

input from google sheet, displayed in Google Calendar

1/4/23  -&gt;  1/4/23 put in goog cal correctly 
1/8/23 -&gt;  1/7/23  - one day off
2/5/23 -&gt; 2/4/23 - one day off
6/18/23 -&gt; 6/17/23 - one day off
10/15/23 -&gt; 10/15/23 - correct

<hr>

function addEvents(){
  var ss = 
 SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
 var lr = ss.getLastRow();
 var cal = 
 CalendarApp.getCalendarById(&quot;@group.calendar.google.com&quot;);

  var data = ss.getRange(&quot;A1:C&quot; + lr).getValues();


  for(var i = 0; i&lt;data.length;i++){


    cal.createAllDayEvent(data[i][0], new Date(data[i][1]), 
   {description:&#39;PRO: &#39; + data[i][2]});

    }
   } 

   three columns in the google sheet
   title, date, and description 

答案1

得分: 0

尝试GetDisplayValues()

var data = ss.getRange("A1:C" + lr).GetDisplayValues();

并可能将列格式化为yyyy-MM-dd。

这将确保脚本使用的数据与您在工作表上看到的数据相同。格式化列将确保new Date()正确转换数据。

如果仍然不准确,添加几个小时到您的Date对象,以确保时间位于一天中间。

let today = new Date();
today.setHours(today.getHours() + 4);
英文:

Try GetDisplayValues()

var data = ss.getRange(&quot;A1:C&quot; + lr).GetDisplayValues();

and potentially format the column as yyyy-MM-dd.

This will ensure the data the script is using is the same as you see on the sheet. Formatting the column will ensure new Date() converts the the data correctly.

If its still off add a couple hours to your Date object to ensure the time is firmly in the middle of the day.

let today = new Date();
today.setHours(today.getHours() + 4);

huangapple
  • 本文由 发表于 2023年2月10日 03:46:35
  • 转载请务必保留本文链接:https://go.coder-hub.com/75403711.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定