从电子表格创建Google日历活动，但防止重复 [英] Create Google Calendar Events from Spreadsheet but prevent duplicates

问题描述

我试图编写一个脚本，它可以从Google电子表格中获取数据，并在我的Google日历中创建事件。

我管理得很好，但它产生了重复每次我跑它。因此，现在我试图通过在电子表格中创建一个列17，并为每行自动生成唯一事件ID，然后每次脚本运行时都会查看每行的事件ID并删除相应的事件在日历中重新创建之前使用原始数据或更新的数据，如果我已经改变了行。

我不熟悉任何脚本，并拼凑在一起，但我现在正在打墙。任何人都可以帮助解决这个问题吗？

$ p $ 函数CalInsert（）{
var cal = CalendarApp.getDefaultCalendar（）;
var id = SpreadsheetApp.getActiveSheet（）。getRange（2,17）.getValue（）;

if（id！= 0）{
var event = cal.getEventSeriesById（id）;
event.deleteEventSeries（）;
}

var sheet = SpreadsheetApp.getActiveSheet（）;
var startRow = 2; //处理
的第一行数据var numRows = sheet.getLastRow（）; //要处理的行数
var dataRange = sheet.getRange（startRow，1，numRows，sheet.getLastColumn（））;
var data = dataRange.getValues（）;

for（i in data）{
var row = data [i];
var title = row [0]; //第一列
var desc = row [13]; //第二列
var tstart = row [14];
var tstop = row [15];

var event = cal.createEvent（title，tstart，tstop，{description：desc}）;
var eventid = event.getId（）;
SpreadsheetApp.getActiveSheet（）。getRange（2,17）.setValue（eventid）;


$ / code>

解决方案

与两天前提出的问题非常相似，即将事件电子表格与日历同步。这听起来像是你想要把电子表格视为它发源事件的主人，这将大大简化问题。 此答案涵盖了您需要执行的基本操作。如果您只想修改现有的代码，我已经在下面实现了一个实现。

我有一个来自此博客，它会将预先存在的日历条目修改为匹配电子表格中的信息。我已经对电子表格进行了不同的安排，这反映在代码中。

日期|标题|开始时间|结束时间|位置|说明|
EventID

该脚本创建新事件时，然后在稍后的调用中从日历中检索事件，从而避免重复。

脚本

在活动电子表格中添加一个自定义菜单，其中包含一个菜单项
*用于调用exportEvents（）函数。
* onOpen（）函数在定义时会在
*电子表格打开时自动调用。
*有关使用Spreadsheet API的更多信息，请参阅
* https://developers.google.com/apps-script/service_spreadsheet
* /
函数onOpen（）{
var sheet = SpreadsheetApp.getActiveSpreadsheet（）;
var entries = [{
name：Export Events，
functionName：exportEvents
}];
sheet.addMenu（日历操作，条目）;
};
$ b / **
*将电子表格中的事件导出到日历
* /
function exportEvents（）{
var sheet = SpreadsheetApp.getActiveSheet（）;
var headerRows = 1; //标题信息的行数（跳过）
var range = sheet.getDataRange（）;
var data = range.getValues（）;
var calId =YOUR_CALENDAR_ID;
var cal = CalendarApp.getCalendarById（calId）; （i <0; i if（i var row = data [i];
var date = new Date（row [0]）; //第一列
var title = row [1]; //第二列
var tstart = new Date（row [2]）;
tstart.setDate（date.getDate（））;
tstart.setMonth（date.getMonth（））;
tstart.setYear（date.getYear（））;
var tstop = new Date（row [3]）;
tstop.setDate（date.getDate（））;
tstop.setMonth（date.getMonth（））;
tstop.setYear（date.getYear（））;
var loc = row [4];
var desc = row [5];
var id = row [6]; //第六列== eventId
//检查事件是否已经存在，如果是，则更新它
try {
var event = cal.getEventSeriesById（id）;
}
catch（e）{
//什么都不做 - 我们只是想避免在事件不存在的情况下发生异常
}
if（！event） {
//cal.createEvent(title，new Date（March 3，2010 08:00:00），new Date（March 3，2010 09:00:00），{description：desc，位置：LOC}）;
var newEvent = cal.createEvent（title，tstart，tstop，{description：desc，location：loc}）。getId（）;
row [6] = newEvent; //用事件ID
更新数据数组
else {
event.setTitle（title）;
event.setDescription（desc）;
event.setLocation（loc）;
// event.setTime（tstart，tstop）; //无法在eventSeries上设置时间。
// ...但我们可以设置重复！
var recurrence = CalendarApp.newRecurrence（）。addDailyRule（）。times（1）;
event.setRecurrence（recurrence，tstart，tstop）;
}
调试器;
}
//将所有事件ID记录到电子表格
range.setValues（data）;

$ / code $ / $ p

删除/重建

在此替代方案中，eventID用于查找和删除先前存在的事件。之后，将使用电子表格中的数据创建新事件。这具有可以更新事件的所有值的益处，包括开始和停止时间（参见下面的注释）。另一方面，对原始活动进行的任何更改都将丢失 - 例如，如果其他人已被邀请参加活动或添加了自定义提醒。

<要使用这个替代方法，只需用下面的代码来替换匹配的代码：
$ b

// Check if事件已经存在，删除它，如果它
尝试{
var event = cal.getEventSeriesById（id）;
event.deleteEventSeries（）;
row [6] =''; //删除事件ID
}
catch（e）{
//什么也不做 - 我们只是想避免在事件不存在的情况下发生异常
}
//cal.createEvent（title，new Date（March 3，2010 08:00:00），new Date（March 3，2010 09:00:00），{description：desc，location：loc}） ;
var newEvent = cal.createEvent（title，tstart，tstop，{description：desc，location：loc}）。getId（）;
row [6] = newEvent; //用事件ID
调试器更新数据数组;


笔记

• 当找不到匹配的事件时， getEventSeriesById 的文档错误地指出它返回 null ，相反，它会抛出异常。 （讨厌的！）所以我把它放在try / catch块中，只是为了继续游泳。


• 不幸的是，虽然 getEventSeriesById 用于检索事件，它<返回一个 EventSeries 对象，它不支持 setTime （）方法。如果你不希望改变事件发生的时间，那么确定。否则，您可以通过设置 recurrence 规则和放大器来将 Event 更改为 EventSeries 。时间或删除旧事件并创建一个新事件，如删除/重新创建中所示。 问题1154 。


• 电子表格总是胜出。通过Google日历记录的任何事件更改（在相关字段中）都将被脚本覆盖。

I'm trying to write a script that will take data from a Google spreadsheet and create events in my Google calendar.

I managed that fine but it produced duplicates every time I ran it. So now I'm trying to prevent that by creating a column 17 in the spreadsheet with an automatically produced unique event ID for each row and then each time the script is run it will look at the event ID for each row and delete the corresponding event in the calendar before recreating it with the original data or updated data if I've changed the row.

I'm new to scripting of any kind and cobbled this together but am hitting a wall now. Can anyone help sort this out?

function CalInsert() {
    var cal = CalendarApp.getDefaultCalendar();
    var id = SpreadsheetApp.getActiveSheet().getRange(2,17).getValue();

    if (id != 0) {
        var event = cal.getEventSeriesById(id);
        event.deleteEventSeries();
    }

    var sheet = SpreadsheetApp.getActiveSheet();
    var startRow = 2; // First row of data to process
    var numRows = sheet.getLastRow(); // Number of rows to process
    var dataRange = sheet.getRange(startRow, 1, numRows, sheet.getLastColumn());
    var data = dataRange.getValues();

    for (i in data) {
        var row = data[i];
        var title = row[0]; // First column
        var desc = row[13]; // Second column
        var tstart = row[14];
        var tstop = row[15];

        var event = cal.createEvent(title, tstart, tstop, {description:desc});
        var eventid = event.getId();
        SpreadsheetApp.getActiveSheet().getRange(2,17).setValue(eventid);
    }
}

解决方案

This is very similar to a question asked just two days ago, which was about synchronizing a spreadsheet of events with a calendar. It sounds like you want to consider the spreadsheet to be the master of events that it originates, which would simplify the problem considerably. The basics of what you need to do are covered in this answer. If you'd rather just modify existing code, I've got an implementation below.

I have a modified version of the code from this blog, that will modify pre-existing calendar entries to match the info in the spreadsheet. I have arranged my spreadsheet differently, and this is reflected in the code.

Date | Title | Start Time | End Time | Location | Description | EventID

The event ID column gets filled in by the script when new events are created, and is then used in later invocations to retrieve events from the calendar, thereby avoiding duplication.

Script

/**
 * Adds a custom menu to the active spreadsheet, containing a single menu item
 * for invoking the exportEvents() function.
 * The onOpen() function, when defined, is automatically invoked whenever the
 * spreadsheet is opened.
 * For more information on using the Spreadsheet API, see
 * https://developers.google.com/apps-script/service_spreadsheet
 */
function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Export Events",
    functionName : "exportEvents"
  }];
  sheet.addMenu("Calendar Actions", entries);
};

/**
 * Export events from spreadsheet to calendar
 */
function exportEvents() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var headerRows = 1;  // Number of rows of header info (to skip)
  var range = sheet.getDataRange();
  var data = range.getValues();
  var calId = "YOUR_CALENDAR_ID";
  var cal = CalendarApp.getCalendarById(calId);
  for (i=0; i<data.length; i++) {
    if (i < headerRows) continue; // Skip header row(s)
    var row = data[i];
    var date = new Date(row[0]);  // First column
    var title = row[1];           // Second column
    var tstart = new Date(row[2]);
    tstart.setDate(date.getDate());
    tstart.setMonth(date.getMonth());
    tstart.setYear(date.getYear());
    var tstop = new Date(row[3]);
    tstop.setDate(date.getDate());
    tstop.setMonth(date.getMonth());
    tstop.setYear(date.getYear());
    var loc = row[4];
    var desc = row[5];
    var id = row[6];              // Sixth column == eventId
    // Check if event already exists, update it if it does
    try {
      var event = cal.getEventSeriesById(id);
    }
    catch (e) {
      // do nothing - we just want to avoid the exception when event doesn't exist
    }
    if (!event) {
      //cal.createEvent(title, new Date("March 3, 2010 08:00:00"), new Date("March 3, 2010 09:00:00"), {description:desc,location:loc});
      var newEvent = cal.createEvent(title, tstart, tstop, {description:desc,location:loc}).getId();
      row[6] = newEvent;  // Update the data array with event ID
    }
    else {
      event.setTitle(title);
      event.setDescription(desc);
      event.setLocation(loc);
      // event.setTime(tstart, tstop); // cannot setTime on eventSeries.
      // ... but we CAN set recurrence!
      var recurrence = CalendarApp.newRecurrence().addDailyRule().times(1);
      event.setRecurrence(recurrence, tstart, tstop);
    }
    debugger;
  }
  // Record all event IDs to spreadsheet
  range.setValues(data);
}

Delete / Recreate

In this alternative, the eventID is used to find and delete the previously existing event. After that, a new event is created with the data in the spreadsheet. This has the benefit that all values of the event can be updated, including start and stop times (see Notes below). On the other hand, any changes that were made to the original event will be lost - for instance, if other people had been invited to the event, or custom reminders were added.

To use this alternative, simply replace the matching code with this:

// Check if event already exists, delete it if it does
try {
  var event = cal.getEventSeriesById(id);
  event.deleteEventSeries();
  row[6] = '';  // Remove event ID    
}
catch (e) {
  // do nothing - we just want to avoid the exception when event doesn't exist
}
//cal.createEvent(title, new Date("March 3, 2010 08:00:00"), new Date("March 3, 2010 09:00:00"), {description:desc,location:loc});
var newEvent = cal.createEvent(title, tstart, tstop, {description:desc,location:loc}).getId();
row[6] = newEvent;  // Update the data array with event ID
debugger;

Notes

• The Documentation for getEventSeriesById wrongly states it returns null when no matching event is found, when instead it throws an exception. (nasty!) So I've enclosed it in a try / catch block just to keep on swimming.
• Unfortunately, while getEventSeriesById works to retrieve an event, it returns an EventSeries object, which does not support the setTime() method. If you don't expect to change the time of events, this OK. Otherwise, you can change the Event into an EventSeries by setting the recurrence rules & times, or delete the old event and create a new one, as shown in Delete / Recreate. Issue 1154.
• The spreadsheet always wins. Any event changes (in relevant fields) recorded via the Google Calendar will be overwritten by the script.

这篇关于从电子表格创建Google日历活动，但防止重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！