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

查看:24
本文介绍了如何同步 Google 日历 &带脚本的电子表格的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正在尝试创建一个 Google Apps 脚本,使 Google 日历和云端硬盘上的主电子表格"保持同步——这可能吗?我找到了这两个帖子:

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/05/grabbing-google-calendar-event-details-into-a-spreadsheet/

我很确定这可以使用大量 if 语句和逻辑来完成,但也许有更简单的方法?

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

推荐答案

是的,编写双向事件同步脚本可能,但不会那么简单.您提到的那两篇文章有可以重复使用的部分,但与您在实际同步中面临的挑战相比,它们是非常基本的.您可能想阅读 将 Google Apps 脚本用于活动预订系统,该系统会根据电子表格创建日历条目(但不会进行持续同步).我过去做过一些该脚本的调试.

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.

同步需要支持:

  • 在任一位置创建事件
  • 在任一位置修改事件详细信息(尽管您可以选择仅考虑事件详细信息的一个子集以进行简化)
  • 删除任一位置的事件
  • 重复发生,例如CalendarEvent.getEventSeries() 处理(或选择避免)
  • 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)

注意事项:

  1. calEvents 的所有更新都可以立即写入数组并写入日历 API,作为批量更新的替代方法.这将消除在本地跟踪更改的需要,尽管触摸 lastUpdated 值是个好主意.

  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.

您需要在读取 calEvents 时使用 CalendarEvent.getLastUpdated(),并在电子表格中存储类似的值(绑定到 onEdit 触发器)以便于比较.

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.

记录 CalendarEvent.getId() 与电子表格中的事件的比较将简化.您还有 CalendarEvent.setTag(key,value) 可用于将自定义元数据记录到日历中,例如指示源自或已与电子表格同步的事件.(这些标签无法通过 GCal UI 访问,因此只能通过脚本访问.)

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

您应该考虑要处理的日期范围或事件数量,并限制脚本的范围.如果不这样做,在实际操作中肯定会遇到执行时间限制.

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:

  • 宾客名单
  • 提醒列表

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

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆