从电子表格添加事件时，Google日历中的时间有所不同 [英] different time in google calendar when adding events from spreadsheet

问题描述

通过Google Appscript和spreedsheet添加到Google日历中时的时间变化：
应用程序脚本代码（仅时间部分）：

time changes when added to google calender by google appscript and spreedsheet: code for app script (only time portion):

function setUpCalendar_(values, range) {
var cal = CalendarApp.createCalendar('Conference Calendar');

Browser.msgBox('Calender time zone: ' + cal.getTimeZone()); 
//to check timezone for debugging.

for (var i = 1; i < values.length; i++) {
var session = values[i];
var title = session[0];
var start = joinDateAndTime_(session[1], session[2]);
var end = joinDateAndTime_(session[1], session[3]);
var options = {location: session[4], sendInvites: true};
var event = cal.createEvent(title, start, end, options)
                .setGuestsCanSeeGuests(false);
//session[5] = event.getId();
   ///====:::: test :::: replacingsession[5] with values[i][5]
   }

range.setValues(values);

// Store the ID for the Calendar, which is needed to retrieve events by ID.
ScriptProperties.setProperty('calId', cal.getId());
}

function joinDateAndTime_(date, time) {
  date = new Date(date);
  date.setHours(time.getHours());
  date.setMinutes(time.getMinutes());
  Browser.msgBox(date); // to check time for debuging.
  return date;
  }

使用脚本链接到电子表格：

Link to spreadsheet with script:

https://docs.google.com/spreadsheet/ccc?key=0AorrYgKfVJpQdEdBY2lYdEpIUUc0TlRRUkNlNmRPTWc&usp=sharing

到目前为止已采取的步骤：
1.将电子表格的时区更改为区域设置。
2.将日历的时区更改为语言环境。

steps taken so far: 1. changed timezone of spreadsheet to locale. 2. changed timezone of calender to locale.

结果：
电子表格和日历中的时间不同。

result: different time in spreadsheet and calender.

问题：
在电子表格和日历中必须相同。

issue: must be same time in spreadsheet and calender.

引用：

calendarApp createEvent在错误的日期添加了事件

如何使用日历的timeZone为日期对象设置timeZone

推荐答案

我已经有了这种使用您的方法将日期和时间从电子表格添加到JavaScript日期对象（一些随机值添加到小时和/或分钟）时出现问题，因此我发现了另一种方法更可靠...（而-我承认-学术性较低。） 。）

I already had that kind of issue when using your method to join date and time from spreadsheet to JavaScript date object (some random values added to hours and/or minutes) so I found another approach that works more reliably...(while - I admit - less academic...)

我在您的电子表格副本中进行了测试，并且在设置会议重新创建事件（在加尔各答时间（Calcutta Time），这对我来说并不完美，但我暂时动了以检查结果；-）

I tested in a copy of your spreadsheet and when I setiup a conference the events are created as they should (in Calcutta Time, which is not perfect for me but I "moved" temporarily to check the results ;-)

代码，只需复制并粘贴到脚本中即可替换您的函数。

here is the code, just copy and paste in your script to replace your function.

function joinDateAndTime_(date, time){
  var hrs = Number(Utilities.formatDate(time,Session.getScriptTimeZone(),'HH'));
  var min = Number(Utilities.formatDate(time,Session.getScriptTimeZone(),'mm'));
  var sec = Number(Utilities.formatDate(time,Session.getScriptTimeZone(),'ss'));
  Logger.log('date = '+Utilities.formatDate(date, Session.getScriptTimeZone() ,'dd-MMM-yyyy  HH:mm:ss'));
  Logger.log('time = '+hrs+':'+min+':'+sec);
  var dateAndTime = new Date(date).setHours(hrs,min,sec,0);
  Logger.log('full date object = '+Utilities.formatDate(new Date(dateAndTime), Session.getScriptTimeZone() ,'dd-MMM-yyyy  HH:mm:ss'))
  return new Date(dateAndTime);
}

别忘了检查工作表和脚本的时区... （因为您提到过您尝试过不同的设置），它们当然必须与您所在的地区和日历相匹配。

Don't forget to check the timezones of the Sheet and script... (since you mentioned you tried different setups) They must of course match your area and calendars.

这篇关于从电子表格添加事件时，Google日历中的时间有所不同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！