从表格更新了Google日历事件 [英] Updated Google Calendar events from Sheets

问题描述

我正在尝试将Google日历同步到工作表，以便我可以轻松地从工作表中批量更新日历事件，然后将所有更改推回日历。

I am trying to sync a Google Calendar to a Sheet to allow me to easily mass update calendar events from within the sheet, then push all the changes back to the calendar.

我能够将所有事件下载到我的工作表中，特别是使用以下代码：

I'm able to download all the events into my Sheet, specifically using this bit of code:

    // Loop through all calendar events found and write them out starting on calulated ROW 2 (i+2)
for (var i=0;i<events.length;i++) {
var row=i+2;
var myformula_placeholder = '';
// Matching the "header=" entry above, this is the detailed row entry "details=", and must match the number of entries of the GetRange entry below
// NOTE: I've had problems with the getVisibility for some older events not having a value, so I've had do add in some NULL text to make sure it does not error
var details=[[events[i].getTitle(), events[i].getStartTime(), events[i].getEndTime(), events[i].getId()]];
var range=sheet.getRange(row,1,1,4);
range.setValues(details);

我可以使用以下代码从下载的数据向日历中创建新事件：

I'm able to create NEW events onto my calendar from the downloaded data using this code:

     function caltest1() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var numRows = 2;   // Number of rows to process
  var dataRange = sheet.getRange(startRow, 1, numRows, 5);
  var data = dataRange.getValues();
  var cal = CalendarApp.getDefaultCalendar();
  for (i in data) {
    var row = data[i];
    var title = row[0];  // First column
    var desc = row[1];       // Second column
    var tstart = row[2];
    var tstop = row[3];
    var loc = row[4];
    //cal.createEvent(title, new Date("March 3, 2010 08:00:00"), new Date("March 3, 2010 09:00:00"), {description:desc,location:loc});
    cal.createEvent(title, tstart, tstop, {description:desc,location:loc});
 }
}

但是，因为它创建了一个新事件而不是简单地更新原始事件，每次按下脚本时都会创建重复事件。

However because it's creating a new event and not simply updating the original event, it creates duplicate events every time I push the script.

我想查找代替使用第一个函数中收集的事件ID并简单地更新具有新信息的现有事件。

I want to find code that instead uses the event ID gathered in the first function and simply updates the existing event with the new information.

我不希望使用删除所有日历事件然后创建所有新事件的代码。

I'd prefer not to use code that deletes all calendar events then creates all the new ones.

推荐答案

可以使用 getEventSeriesById（）方法。即使名称表明此方法用于获取一系列日历事件，也适用于单个事件。

The getEventSeriesById() method can be used. Even though the name indicates that this method is for getting a calendar event that is a series, it works for a single event.

文档指出：

获取具有给定ID的事件系列。如果给定的ID是针对单个CalendarEvent，则将返回CalendarEventSeries，其中包含该系列中的单个事件。

Gets the event series with the given ID. If the ID given is for a single CalendarEvent, then a CalendarEventSeries will be returned with a single event in the series.

应用脚本文档-getEventSeriesById

function caltest1() {
  var cal,desc,i,iCalId,loc,row,sheet,startRow,thisEvent,title,tstart,
      tstop,;

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

  cal = CalendarApp.getDefaultCalendar();

  for (i in data) {
    row = data[i];
    title = row[0];  // First column
    desc = row[1];       // Second column
    tstart = row[2];
    tstop = row[3];
    loc = row[4];
    iCalId = row[5];//Column 6 or F

    //cal.createEvent(title, new Date("March 3, 2010 08:00:00"), new Date("March 3, 2010 09:00:00"), {description:desc,location:loc});
    //cal.createEvent(title, tstart, tstop, {description:desc,location:loc});

    thisEvent = cal.getEventSeriesById(iCalId);//Get a calendar event by it's ID

    if (thisEvent) {
      thisEvent.setDescription('Test it to determine if it works');//Edit the description
    }
 }
}

请注意，上面的代码假定日历事件ID在F列中，并且下载日历数据的函数将ID放在F列中。

Note that the above code assumes that the calendar event ID is in column F, and that the function that downloaded the calendar data put the ID in column F.

这篇关于从表格更新了Google日历事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！