从电子表格创建Google日历活动，但防止重复

Question

我正在尝试编写一个脚本，该脚本将从Google电子表格中获取数据并在Google日历中创建事件。

我管理得很好，但是每次运行它都会产生重复。因此，现在我试图通过在电子表格中创建第17列来为每行自动生成唯一的事件ID来防止这种情况，然后每次运行脚本时，脚本都会查看每行的事件ID并删除相应的事件在日历中重新创建原始数据或更新后的数据（如果我更改了该行）。

我不熟悉任何一种脚本并将它们拼凑在一起，但是现在却碰壁了。谁能帮忙解决这个问题？

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

    }

}

Answer 1

这与两天前提出的问题非常相似，该问题涉及将事件电子表格与日历同步。听起来您好像想将电子表格视为它所引发的事件的主控者，这将大大简化问题。该答案涵盖了您需要做的基本工作。如果您只想修改现有代码，则下面有一个实现。我有来自此Blog的代码的修改版本，该版本将修改预先存在的日历条目以匹配电子表格中的信息。我对电子表格的排列方式有所不同，这反映在代码中。日期| 标题| 开始时间 结束时间| 位置| 描述 事件ID创建新事件时，脚本会填充事件ID列，然后在以后的调用中使用该事件ID列从日历中检索事件，从而避免重复。脚本/**&nbsp;* Adds a custom menu to the active spreadsheet, containing a single menu item&nbsp;* for invoking the exportEvents() function.&nbsp;* The onOpen() function, when defined, is automatically invoked whenever the&nbsp;* spreadsheet is opened.&nbsp;* For more information on using the Spreadsheet API, see&nbsp;* https://developers.google.com/apps-script/service_spreadsheet&nbsp;*/function onOpen() {&nbsp; var sheet = SpreadsheetApp.getActiveSpreadsheet();&nbsp; var entries = [{&nbsp; &nbsp; name : "Export Events",&nbsp; &nbsp; functionName : "exportEvents"&nbsp; }];&nbsp; sheet.addMenu("Calendar Actions", entries);};/**&nbsp;* Export events from spreadsheet to calendar&nbsp;*/function exportEvents() {&nbsp; var sheet = SpreadsheetApp.getActiveSheet();&nbsp; var headerRows = 1;&nbsp; // Number of rows of header info (to skip)&nbsp; var range = sheet.getDataRange();&nbsp; var data = range.getValues();&nbsp; var calId = "YOUR_CALENDAR_ID";&nbsp; var cal = CalendarApp.getCalendarById(calId);&nbsp; for (i=0; i<data.length; i++) {&nbsp; &nbsp; if (i < headerRows) continue; // Skip header row(s)&nbsp; &nbsp; var row = data[i];&nbsp; &nbsp; var date = new Date(row[0]);&nbsp; // First column&nbsp; &nbsp; var title = row[1];&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;// Second column&nbsp; &nbsp; var tstart = new Date(row[2]);&nbsp; &nbsp; tstart.setDate(date.getDate());&nbsp; &nbsp; tstart.setMonth(date.getMonth());&nbsp; &nbsp; tstart.setYear(date.getYear());&nbsp; &nbsp; var tstop = new Date(row[3]);&nbsp; &nbsp; tstop.setDate(date.getDate());&nbsp; &nbsp; tstop.setMonth(date.getMonth());&nbsp; &nbsp; tstop.setYear(date.getYear());&nbsp; &nbsp; var loc = row[4];&nbsp; &nbsp; var desc = row[5];&nbsp; &nbsp; var id = row[6];&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // Sixth column == eventId&nbsp; &nbsp; // Check if event already exists, update it if it does&nbsp; &nbsp; try {&nbsp; &nbsp; &nbsp; var event = cal.getEventSeriesById(id);&nbsp; &nbsp; }&nbsp; &nbsp; catch (e) {&nbsp; &nbsp; &nbsp; // do nothing - we just want to avoid the exception when event doesn't exist&nbsp; &nbsp; }&nbsp; &nbsp; if (!event) {&nbsp; &nbsp; &nbsp; //cal.createEvent(title, new Date("March 3, 2010 08:00:00"), new Date("March 3, 2010 09:00:00"), {description:desc,location:loc});&nbsp; &nbsp; &nbsp; var newEvent = cal.createEvent(title, tstart, tstop, {description:desc,location:loc}).getId();&nbsp; &nbsp; &nbsp; row[6] = newEvent;&nbsp; // Update the data array with event ID&nbsp; &nbsp; }&nbsp; &nbsp; else {&nbsp; &nbsp; &nbsp; event.setTitle(title);&nbsp; &nbsp; &nbsp; event.setDescription(desc);&nbsp; &nbsp; &nbsp; event.setLocation(loc);&nbsp; &nbsp; &nbsp; // event.setTime(tstart, tstop); // cannot setTime on eventSeries.&nbsp; &nbsp; &nbsp; // ... but we CAN set recurrence!&nbsp; &nbsp; &nbsp; var recurrence = CalendarApp.newRecurrence().addDailyRule().times(1);&nbsp; &nbsp; &nbsp; event.setRecurrence(recurrence, tstart, tstop);&nbsp; &nbsp; }&nbsp; &nbsp; debugger;&nbsp; }&nbsp; // Record all event IDs to spreadsheet&nbsp; range.setValues(data);}删除/重新创建在此替代方案中，eventID用于查找和删除先前存在的事件。之后，将使用电子表格中的数据创建一个新事件。这样的好处是可以更新事件的所有值，包括开始和停止时间（请参见下面的注释）。另一方面，对原始事件所做的任何更改都将丢失-例如，如果邀请了其他人参加该事件，或者添加了自定义提醒。要使用此替代方法，只需将匹配的代码替换为：// Check if event already exists, delete it if it doestry {&nbsp; var event = cal.getEventSeriesById(id);&nbsp; event.deleteEventSeries();&nbsp; row[6] = '';&nbsp; // Remove event ID&nbsp; &nbsp;&nbsp;}catch (e) {&nbsp; // 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;&nbsp; // Update the data array with event IDdebugger;笔记getEventSeriesById错误的文档指出，null如果没有找到匹配的事件，它将返回，而抛出异常。（讨厌！）因此，我将其封装在try / catch块中，以继续游泳。不幸的是，当getEventSeriesById检索事件时，它returns是一个EventSeries不支持该setTime()方法的对象。如果您不希望更改事件的时间，则可以。否则，您可以通过设置重复规则和时间将更Event改为，或者删除旧事件并创建一个新事件，如Delete / Recreate所示。问题1154。EventSeries电子表格总是赢。通过Google日历记录的任何事件更改（在相关字段中）都会被脚本覆盖。