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

问题描述

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

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

我做得很好，但每次运行它都会产生重复.所以现在我试图通过在电子表格中创建一个第 17 列并为每一行自动生成唯一的事件 ID 来防止这种情况发生，然后每次运行脚本时，它都会查看每一行的事件 ID 并删除相应的事件在使用原始数据或更新的数据重新创建日历之前，如果我更改了行.

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.

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

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

事件 ID 列在创建新事件时由脚本填充，然后在以后的调用中用于从日历中检索事件，从而避免重复.

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.

/**
 * 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);
}

删除/重新创建

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

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;

注意事项

• getEventSeriesById 的文档错误地指出，当找不到匹配的事件时，它会返回 null，而是会引发异常.(讨厌！)所以我将它包含在 try/catch 块中，只是为了继续游泳.
• 不幸的是，虽然 getEventSeriesById 用于检索事件，但它返回一个 EventSeries 对象，该对象不支持 setTime() 方法.如果您不希望更改事件的时间，这可以.否则，您可以通过设置 recurrence 规则将 Event 更改为 EventSeries次，或删除旧事件并创建新事件，如删除/重新创建所示.问题 1154.
• 电子表格总是胜出.通过 Google 日历记录的任何事件更改(在相关字段中)都将被脚本覆盖.

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屋！