如何同步Google日历&与脚本的电子表格 [英] How to synchronize Google Calendar & Spreadsheet with Script

问题描述

我正在尝试创建一个Google Apps脚本，使Google日历和云端硬盘上的主电子表格保持同步 - 这可能吗？我发现了这两个帖子：

• 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 /

我很确定这可以用很多if语句和逻辑来完成，但也许有一个更简单的方法？

我最终只提供了以下简单的脚本。所有这一切真的需要添加基于两列的事件，这将花费太长的时间来开发。

  function onOpen （）{
 //产生一个带有触发AddToCal 
的按钮的菜单var sheet = SpreadsheetApp.getActiveSpreadsheet（）; 
 var entries = [{
 name：将事件添加到日历中，
 functionName：AddToCal
}]; 
 sheet.addMenu（Data To Calendar Plugin，条目）; 
}; 
 
函数AddToCal（）{
 
 //获取当前行
 var ss = SpreadsheetApp.getActiveSpreadsheet（）; 
 var cell = ss.getActiveCell（）; 
 var R = cell.getRow（）; 
 
 //获取当前行传递给日历事件的值
 var date_of_event = ss.getRange（'G'+ R）.getValue（）; 
 var date = new Date（date_of_event）; 
 var event_title = ss.getRange（'A'+ R）.getValue（）; 
 //访问日历
 var cal = CalendarApp.getCalendarById（'[IDREMOVED]'）; 
 cal.createAllDayEvent（event_title，date）; 
 $ b s .toast（Event added to+ cal.getName（））; 
 
  

解决方案

是的，可能编写一个双向事件同步脚本，但它不会很简单。您提到的这两个帖子有可重用的部分，但与实际同步所面临的挑战相比，它们相当基本。您可能需要阅读将Google Apps脚本用于事件预订系统，该系统根据电子表格创建日历条目（但不会进行正在进行的同步）。我过去曾对该脚本进行调试。

同步需要支持：

• 在任一位置创建事件
  
• 修改事件详细信息（尽管您可以选择只考虑事件详情的一个子集来简化）


• 删除任一位置的事件 CalendarEvent.getEventSeries（）处理（或选择避免）
  

您可以开始的伪代码：

 打开日历，将日历事件读入calArray（将关注所有属性）
打开Spreadsheet，将电子表格事件读入sheetArray 
 
对于calArray中的每个事件：
在sheetArray中搜索calEvent。 
如果找到，比较lastUpdated值。 
如果相等，则不做任何操作
否则复制最近更新到最近更新的
继续下一个事件
如果未找到，则将calEvent复制到新的sheetEvent，包括lastUpdated值。 
继续下一个事件
 
对于sheetArray中的每个事件（...尚未处理）
上面的类似逻辑。 
 
将更新后的sheetArray写入电子表格。 
将已更新的calEvents写入日历API（请参阅下面的注1）
  

注意：

1. 可以对calEvents进行所有更新，以便立即对日历API进行阵列和写入，以替代批量更新。这将消除在本地跟踪更改的需求，但触摸lastUpdated值是一个好主意。 在读取calEvent时，您会希望使用 CalendarEvent.getLastUpdated（），并存储一个类似的值在您的电子表格（绑定到 onEdit 触发器）上，以便于比较。




2. 这将简化比较对电子表格中的事件记录 CalendarEvent.getId（）。您还可以使用 CalendarEvent.setTag（key，value）来将自定义元数据记录到日历中，例如指示已生成或已与电子表格同步的事件。 （这些标签不能通过GCal UI访问，因此只能通过脚本访问。）

你应该考虑日期范围或事件数量你想处理，并限制脚本的范围。如果你不这样做，你一定会在实际操作中遇到执行时间限制。 有些日历事件特性不适合用简单的表达式一个电子表格，例如：




• 访客列表


• 提醒列表

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:

• 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?

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

解决方案

Yes, it's possible to 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 script in past.

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)

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:

1. 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.

2. You will want to use CalendarEvent.getLastUpdated() when reading calEvents, and store a similar value in your spreadsheet (tied to an onEdit trigger) to facilitate comparisons.

3. It would simplify comparisons to record CalendarEvent.getId() against events in the spreadsheet. You also have CalendarEvent.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.)

4. 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.

5. Some Calendar Event characteristics don't lend themselves to easy expression in a spreadsheet, for instance:

   • Guest list
   • Reminder list

这篇关于如何同步Google日历&与脚本的电子表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！