从Google表格创建重复的Google日历活动 [英] Creating recurring Google Calendar events from Google Sheet

问题描述

我正在尝试通过Google电子表格设置年度重复事件，尽管我一直遵循

I'm attempting to set up yearly recurring events from a Google spreadsheet, and although I've followed this answer, I still can't get the ID to write to each row and thus allow me to write and check entries.

列布局为

RenDate | Domain | Client | Type | Registrar | ID

代码:

/**
 * 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 = "[id removed]@group.calendar.google.com";
  var cal = CalendarApp.getCalendarById(calId);
  for (i in data) {
    if (i < headerRows) continue; // Skip header row(s)
    var row = data[i];
    var date = new Date(row[0]);  // First column - Renewal Date
    var domain = row[1];          // Second column - Domain
    var client = row[2];          // Third column - Client
    var type = row[3];            // Fourth column - Type
    var source = row[4];          // Fifth column - Registrar
    var id = row[5];              // Sixth column - ID
    if(row[1]=="") continue;      // Skip rows if no date exists
    var title = type + " renewal for " + domain;
    var desc = "Renew with " + source + " for " + client;

    // Check if event already exists, update it if it does
    try {
      var event = cal.getEventSeriesById(id);
      event.setTitle('got you');  // this is to "force error" if the event does not exist
    }catch (e) {
      var newEvent = cal.createEvent(title, date, {description:desc});
      row[5] = newEvent.getId();   // Update the data array with event ID
      Logger.log('event created'); // For debugging
      var event = cal.getEventSeriesById(row[5]);
    }
    event.setTitle(title);
    event.setDescription(desc);
    var recurrence = CalendarApp.newRecurrence().addYearlyRule();
    event.setRecurrence(recurrence, date);
    debugger;
    data[i] = row ;
  }
  // Record all event IDs to spreadsheet
  range.setValues(data);
}

调试似乎很好，但是不会创建任何事件.我可以使用CreateAllDayEventSeries函数创建事件，但是用ID方法似乎无法解决这个问题，所以这就是我尝试这种方法的原因.

Debugging seems to be fine, but no events are created. I was able to create events using the CreateAllDayEventSeries function, but that seems to be impossible to work out with the ID approach, so that's why I'm trying this method.

编辑:进行了更改以继续执行Serge insas捕获的功能.

Edit: Made change to continue function as caught by Serge insas.

现在脚本无法在catch命令上找到createEvent的方法.

Now the script is not finding the method for createEvent on the catch command.

推荐答案

我能够自行解决该问题(尽管再次感谢Serge最初提供了出色的帮助和可靠的答案，即使我没有看到在我解决问题之前).这是我最终解决的代码:

I was able to resolve the issue on my own (although thanks again to Serge for some excellent assistance initially and a solid answer, even though I didn't see it before I fixed the issue). Here's the code I eventually settled with:

/**
 * 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 = "[id withheld]@group.calendar.google.com";
  var cal = CalendarApp.getCalendarById(calId);
  for (i in data) {
    if (i < headerRows) continue; // Skip header row(s)
    var row = data[i];
    var date = new Date(row[0]);  // First column - Renewal Date
    var domain = row[1];          // Second column - Domain
    var client = row[2];          // Third column - Client
    var type = row[3];            // Fourth column - Type
    var source = row[4];          // Fifth column - Registrar
    var id = row[5];              // Sixth column - ID
    if(row[0]=="") continue;      // Check to see if date exists
    var title = type + " renewal for " + domain;
    var desc = "Renew with " + source + " for " + client;

    // Check if event already exists, update it if it does
    try {
      var event = cal.getEventSeriesById(id);
    }catch(e){
      //Do nothing
    }
    if(!event){
      var newEvent = cal.createAllDayEventSeries(title, date, CalendarApp.newRecurrence().addYearlyRule(), {description:desc});
      row[5] = newEvent.getId();   // Update the data array with event ID
      Logger.log('Event ID ' + row[5] + ' created'); // For debugging
    }
    else{
      var event = cal.getEventSeriesById(row[5]);
      event.setTitle(title);
      event.setDescription(desc);
      Logger.log('Event ID ' + row[5] + ' already exists'); //For debugging
    }
    debugger;
    data[i] = row ;
  }
  // Record all event IDs to spreadsheet
  range.setValues(data);
}

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