谷歌日历/ S preadsheet互惠与版gscript脚本 [英] Google Calendar / Spreadsheet reciprocity script with Gscript
问题描述
我想创建一个谷歌的脚本，保持谷歌日历和一个的硕士preadsheet关于驱动器最新相互 - 这可能吗？我发现这两个职位：
的http://blog.ouseful.info/2010/03/04/maintaining-google-calendars-from-a-google-s$p$padsheet/
<一个href=\"http://blog.ouseful.info/2010/03/05/grabbing-google-calendar-event-details-into-a-s$p$padsheet/\" rel=\"nofollow\">http://blog.ouseful.info/2010/03/05/grabbing-google-calendar-event-details-into-a-s$p$padsheet/
我敢肯定，这可能使用大量的if语句和逻辑来完成，但也许有一个更简单的方法？
编辑：最后我只是提供以下简单的脚本。所有这一切是真的有必要加入了基于两列的事件，而这将已经采取了太长时间来开发。
函数的OnOpen（）{
//生成一个菜单，触发AddToCal按钮
无功表= S preadsheetApp.getActiveS preadsheet（）;
变种项= [{
名称：活动添加到日历
functionName：AddToCal
}];
sheet.addMenu（数据为日历插件项）;
};功能AddToCal（）{ //获取当前行
VAR SS = S preadsheetApp.getActiveS preadsheet（）;
变种细胞= ss.getActiveCell（）;
变种R = cell.getRow（）; //为当前行抢值传递给日历事件
VAR date_of_event = ss.getRange（'G'+ R）.getValue（）;
VAR日期=新的日期（date_of_event）;
VAR EVENT_TITLE = ss.getRange（'A'+ R）.getValue（）;
//访问日历
VAR CAL = CalendarApp.getCalendarById（'[IDREMOVED]'）;
cal.createAllDayEvent（EVENT_TITLE，日期）; ss.toast（事件添加到+ cal.getName（））;
}
是的，它的可能的写一个双向的事件同步脚本，但它不会是简单的。你是指这两个职位有一个可重复使用的部分，但他们是有相当初级相比，你会与实际同步所面临的挑战。你可能想读一遍使用谷歌Apps脚本为其做创建基于为preadsheet日历条目的事件的预订系统（但没有做持续的同步）。我已经做了过去该脚本一些调试。
同步需要支持：
- 在任一位置的事件创建
- 的在任一位置事件的详细信息修改（虽然你可以选择只考虑对简化事件详细信息的一个子集）
- 在任一位置的事件删除
- 复发，例如
CalendarEvent.getEventSeries（）处理（或选择逃避）
这是伪code，你可以使用启动：
打开日历，读取日历活动到calArray（将所有的属性，你在乎）
开放源代码preadsheet，阅读S preadsheet事件到sheetArray对于calArray每个事件：
搜索calEvent在sheetArray。
如果找到，比较LASTUPDATED值。
如果相等，什么都不做
以其他方式复制最近更新到最早更新
与下一个事件继续
如果找不到，则calEvent复制到新的sheetEvent，包括LASTUPDATED价值。
与下一个事件继续在sheetArray每个事件（...这尚未处理）
上述类似的逻辑。写更新sheetArray至S preadsheet。
写更新calEvents日历API（见下文注1）
注：
-
要calEvents所有更新可以作出阵列和写入立即日历API，以替代一个批量更新。这将消除需要局部跟踪的变化，虽然这将是触摸LASTUPDATED值是个好主意。
-
您将需要使用
CalendarEvent.getLastUpdated（）读取calEvents时，并存储在您的S preadsheet（一个类似的值绑定到onEdit触发），以方便比较。 -
这将简化比较录制
CalendarEvent.getId（）对在s preadsheet事件。你也有CalendarEvent.setTag（键，值），可以用来记录自定义元数据到日历，例如，表明始发或已与您的S同步事件preadsheet。 （这些标签无法通过克卡UI访问，因此就只能是通过脚本访问。） -
您应该想想日期或要处理的事件数的范围，限制了脚本的范围。如果不这样做，你一定会碰到的实际操作执行的时间限制。
-
某些日历事件的特点不适合自己易恩pression呈S preadsheet，例如：
- 嘉宾名单
- 温馨提示列表
I am trying to create a Google Script that keeps a Google Calendar and a "master spreadsheet" on Drive up to date reciprocally -- 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?
EDIT: 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:
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
这篇关于谷歌日历/ S preadsheet互惠与版gscript脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
