javascript 如何使用脚本同步 Google 日历和电子表格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15744425/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to synchronize Google Calendar & Spreadsheet with Script
提问by digitalsteez
I am trying to create a Google Apps Script that keeps a Google Calendar and a "master spreadsheet" on Drive synchronized -- is this possible? I found these two posts:
我正在尝试创建一个 Google Apps 脚本，使 Google 日历和 Drive 上的“主电子表格”保持同步——这可能吗？我找到了这两个帖子：
http://blog.ouseful.info/2010/03/04/maintaining-google-calendars-from-a-google-spreadsheet/
http://blog.ouseful.info/2010/03/05/grabbing-google-calendar-event-details-into-a-spreadsheet/
http://blog.ouseful.info/2010/03/04/maintaining-google-calendars-from-a-google-spreadsheet/
http://blog.ouseful.info/2010/03/05/grabbing-google-calendar-event-details-into-a-spreadsheet/
I'm quite sure this could be done using a lot of if statements and logic, but maybe there's a simpler way?
我很确定这可以使用很多 if 语句和逻辑来完成，但也许有更简单的方法？
I ended up just providing the following simple script. All that was really necessary was adding events based on two columns, and this would've taken too long to develop.
我最终只提供了以下简单的脚本。真正需要的是添加基于两列的事件，而这将花费太长时间来开发。
function onOpen() {
//spawns a menu with a button that triggers AddToCal
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var entries = [{
name : "Add event to calendar",
functionName : "AddToCal"
}];
sheet.addMenu("Data To Calendar Plugin", entries);
};
function AddToCal(){
//get the current row
var ss = SpreadsheetApp.getActiveSpreadsheet();
var cell = ss.getActiveCell();
var R = cell.getRow();
//grab values for current row to pass to calendar event
var date_of_event = ss.getRange('G'+R).getValue();
var date = new Date(date_of_event);
var event_title = ss.getRange('A'+R).getValue();
//access the calendar
var cal = CalendarApp.getCalendarById('[IDREMOVED]');
cal.createAllDayEvent(event_title,date);
ss.toast("Event added to " + cal.getName());
}
采纳答案by Mogsdad
Yes, it's possibleto write a two-way event synchronization script, but it isn't going to be simple. Those two posts you refer have parts that could be reused, but they are quite elementary compared to the challenges you'll face with actual synchronization. You may want to read over Using Google Apps Script for a event booking system which does create calendar entries based on a spreadsheet (but doesn't do on-going synchronization). I've done some debugging of that scriptin past.
是的，可以编写双向事件同步脚本，但不会那么简单。您提到的那两篇文章有可以重复使用的部分，但与您在实际同步中面临的挑战相比，它们是非常基本的。您可能需要阅读将 Google Apps 脚本用于活动预订系统，该系统会根据电子表格创建日历条目（但不会进行持续同步）。我过去曾对该脚本进行过一些调试。
Synchronization would need to support:
同步需要支持：
- Creation of events in either location
- Modification of event details in either location (although you could opt to consider only a subset of event details for simplification)
- Deletion of events in either location
- Recurrence, e.g.
CalendarEvent.getEventSeries()handling (or choose to avoid)
- 在任一位置创建事件
- 在任一位置修改事件详细信息（尽管您可以选择仅考虑事件详细信息的一个子集以进行简化）
- 删除任一位置的事件
- 复发，例如
CalendarEvent.getEventSeries()处理（或选择避免）
This is pseudo-code that you could start with:
这是您可以开始使用的伪代码：
Open Calendar, Read Calendar events into calArray (will all attributes you care for)
Open Spreadsheet, Read Spreadsheet events into sheetArray
For each event in calArray:
Search for calEvent in sheetArray.
If found, compare lastUpdated values.
If equal, do nothing
Otherwise copy most recently updated to least recently updated
Continue with next event
If not found then copy calEvent to new sheetEvent, including lastUpdated value.
Continue with next event
For each event in the sheetArray (...that hasn't been handled yet)
Similar logic above.
Write updated sheetArray to spreadsheet.
Write updated calEvents to calendar API (see note 1 below)
Notes:
笔记：
All updates to calEvents could be made to array and written to calendar API immediately, as an alternative to a bulk update. This would eliminate the need to track the changes locally, although it would be a good idea to touch the lastUpdated value.
You will want to use
CalendarEvent.getLastUpdated()when reading calEvents, and store a similar value in your spreadsheet (tied to anonEdittrigger) to facilitate comparisons.It would simplify comparisons to record
CalendarEvent.getId()against events in the spreadsheet. You also haveCalendarEvent.setTag(key,value)that could be used to record custom metadata into the calendar, for instance to indicate events that originated or have been synchronized with your spreadsheet. (These tags are not accessible through the GCal UI, so would only be accessible via script.)You should think about the range of dates or number of events you want to deal with, and limit the scope of the script. If you don't, you are sure to run into execution time limits in real operation.
Some Calendar Event characteristics don't lend themselves to easy expression in a spreadsheet, for instance:
- Guest list
- Reminder list
calEvents 的所有更新都可以对数组进行并立即写入日历 API，作为批量更新的替代方法。这将消除在本地跟踪更改的需要，尽管触摸 lastUpdated 值是个好主意。
您将需要
CalendarEvent.getLastUpdated()在读取 calEvents 时使用，并在电子表格中存储类似的值（绑定到onEdit触发器）以方便比较。它将简化比较以记录
CalendarEvent.getId()电子表格中的事件。您还CalendarEvent.setTag(key,value)可以使用它来将自定义元数据记录到日历中，例如指示源自或已与电子表格同步的事件。（这些标签无法通过 GCal UI 访问，因此只能通过脚本访问。）您应该考虑要处理的日期范围或事件数量，并限制脚本的范围。如果不这样做，在实际操作中肯定会遇到执行时间限制。
某些日历事件特征无法在电子表格中轻松表达，例如：
- 嘉宾名单
- 提醒列表
回答by Serge insas
As mentioned (thanks Henrique) in the other post I've spent some time - it was actually what brought me to GAS originally - on data exchange between spreadsheets and calendars mainly because people I worked with where used to organize their time schedule (for a highschool) in spreadsheets and I had to take care of the transition to Google Calendars.
正如在另一篇文章中提到的（感谢 Henrique），我花了一些时间——这实际上是我最初使用 GAS 的原因——主要是因为与我一起工作的人过去常常组织他们的时间安排（对于一个高中）在电子表格中，我不得不照顾到 Google 日历的过渡。
After some time though it appeared that the online Calendar interface is far more effective to create events so they don't use the sheet to Calendar scripts anymore !!
一段时间后，虽然在线日历界面似乎更有效地创建事件，因此他们不再使用工作表来日历脚本！
On the other hand, the printing and presentation options in GCal are very limited so the other direction is still very useful and we use it all the time !
另一方面，GCal 中的打印和演示选项非常有限，因此另一个方向仍然非常有用，我们一直在使用它！
I know this will seem to be out of subject regarding the original question and maybe too anecdotic but I just wanted to point out that you should thoroughly think about what you really need before reinventing the wheel... As Mogsdad mentioned, some events parameter are not easily described in spreadsheet logic and finally it could become a lot more complicated to use than the original tool.
我知道这似乎与原始问题无关，而且可能过于轶事，但我只是想指出，在重新发明轮子之前，您应该彻底考虑自己真正需要的东西……正如 Mogsdad 提到的，一些事件参数是在电子表格逻辑中不容易描述，最终它可能变得比原始工具更复杂。
The only really useful tool I developed using bidirectional data transfer is a 'batch modification tool" when I need to delete or edit a big number of similar events.
当我需要删除或编辑大量类似事件时，我使用双向数据传输开发的唯一真正有用的工具是“批量修改工具”。
For example if we need to change a teacher's name for some reason all along the year I import all the events for a number of class, replace the name in the spreadsheet and update back the class calendar... it takes me 5 minutes and is very easy but these are very specific use cases and I'm not sure it is very common.
例如，如果我们全年由于某种原因需要更改教师的姓名，我会导入多个班级的所有事件，替换电子表格中的姓名并更新班级日历......这需要我 5 分钟，是非常简单，但这些是非常具体的用例，我不确定它是否很常见。
Anyway I wouldn't call that "synchronization" since it only takes some events some time and changes them... I've never attempted to keep a spreadsheet up to date with a calendar, from my experience calendars are pretty reliable and I consider them as the original data source. As I already said, we import data in spreadsheets every week just for printing and local archiving.
无论如何，我不会称其为“同步”，因为它只需要一些时间并更改某些事件……我从未尝试过使用日历使电子表格保持最新，根据我的经验，日历非常可靠，我认为它们作为原始数据源。正如我已经说过的，我们每周都会在电子表格中导入数据，只是为了打印和本地存档。
Sorry for this long and a bit vague comment (that was way too long to fit in a normal 500 chrs comment ;-)
抱歉，这个长而有点模糊的评论（这太长了，无法放入正常的 500 chrs 评论；-）
回答by Henrique G. Abreu
No there isn't. And although a lot of issues regarding Apps Script Calendar Service has been solved (timezones, all day events, queries and so on), it's still a fairly complex task.
不，没有。而且，虽然关于 Apps Script Calendar Service 的很多问题已经解决（时区、全天事件、查询等），但它仍然是一项相当复杂的任务。
I know Serge, the top contributor here in SOon #google-apps-script tag, has developed quite some scriptsinvolving the Calendar Service.
我知道 Serge是#google-apps-script 标签上SO 中的顶级贡献者，他开发了很多涉及日历服务的脚本。
But I don't know of anyway that made a two-way update between a calendar and a spreadsheet. It should be a hard one. If you ever do, please be kind to share :)
但我不知道在日历和电子表格之间进行了双向更新。这应该是一件很难的事。如果你曾经这样做，请善待分享:)
回答by Dave
I wrote a script that synchronizes between GCalendar and a GSheet. You may be able to use it as is, or can certainly borrow ideas from it. There are separate commands for copying events each way: https://github.com/Davepar/gcalendarsync
我写了一个在 GCalendar 和 GSheet 之间同步的脚本。您可以按原样使用它，或者当然可以借鉴它的想法。每种方式都有用于复制事件的单独命令：https: //github.com/Davepar/gcalendarsync
