根据Google表格中的日期列表创建定期全天日历活动 [英] Create Recurring All Day Calendar Event from List of Dates in Google Sheet

问题描述

我在A列中有一个日期列表（从A2开始），在B列中有一个标题的文本文本（从B2开始）。我的单元格E2中列出了我的日历ID。

I have a list of dates in column A (starting at A2) paired with text for a title in column B (starting at B2). I have my calendar ID listed in cell E2.

我想将此数据发送到Google日历，以创建重复的全天日历活动。更改电子表格后，该日历应该会更新。

I would like to send this data to Google Calendar to create recurring, all-day calendar events. This calendar should update when the spreadsheet is changed.

推荐答案

我编写了这小段代码，使用其中的数据创建重复事件床单。

I wrote this small piece of code that creates recurring events using the data in sheets.

我没有在触发器中编写此代码，因此您必须手动运行它。它可以用 onEdit触发器编写，但我不喜欢认为这是最好的主意，因为您很快就会遇到大量重复的事件，即使可以通过添加一些条件来检查是否已经存在具有这些特征的事件来避免这种情况：

I didn't write this in a trigger, so you would have to run this manually. It could be written in an onEdit trigger, but I don't think it would be the best idea, since you would soon end up having mountains of duplicate events, even though this could be avoided by adding some condition that checks whether an event with those characteristics already exists:

function createEvents() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow();
  var firstRow = 2;
  var firstCol = 1;
  var numRows = lastRow - firstRow + 1;
  var numCols = 2;
  var data = sheet.getRange(firstRow, firstCol, numRows, numCols).getValues();
  var calId = sheet.getRange("E2").getValue();
  var cal = CalendarApp.getCalendarById(calId);
  var recurrence = CalendarApp.newRecurrence().addYearlyRule();
  for(var i = 0; i < data.length; i++) {
    var title = data[i][1];
    var date = new Date(data[i][0]);
    var event = cal.createAllDayEventSeries(title, date, recurrence);
  }
}

此外，如果要删除以前创建的事件，请在创建新事件后，应该跟踪所有旧事件并进行一些编辑，但是我不确定要删除它们。

Also, if you wanted to delete previously created events when you create new events, you should keep track of all old events and edit this code a bit, but I'm not sure you want to delete them.

如果要在编辑工​​作表时创建事件，而不必手动运行该函数，建议使用 onEdit触发器，用于创建与已写入行相对应的事件。此外，仅当行中的数据有效（A和B列都不为空，并且A列中的值是有效的日期）。

In case you want to create events when the sheet is edited, without having to run the function manually, I'd recommend using an onEdit trigger that creates an event corresponding to the row that has been written. Additionally, a condition can be added to create the event only if the data in the row is valid (columns A and B are not empty, and the value in column A is a valid Date).

以下函数完成所有先前的操作：

The following function accomplishes all previous actions:

function createEvent(e) {
  var sheet = e.source.getActiveSheet();
  var range = e.range; // Edited range
  var rowIndex = range.getRow(); // Edited row index
  var firstCol = 1;
  var numCols = 2;
  var data = sheet.getRange(rowIndex, firstCol, 1, numCols).getValues()[0];
  var title = data[1];
  var date = data[0];
  // Check whether column A is a valid Date and column B is not empty:
  if(Object.prototype.toString.call(date) === '[object Date]' && title != "") {
    var calId = sheet.getRange("E2").getValue(); // Get calendar id from cell 'E2'
    var cal = CalendarApp.getCalendarById(calId);
    var recurrence = CalendarApp.newRecurrence().addYearlyRule();
    var event = cal.createAllDayEventSeries(title, date, recurrence); // Create event
  }  
}

为了在编辑时运行，此功能需要 onEdit 触发器。必须 安装此触发器，因为简单触发器 无法访问需要授权的服务。

In order to run on edit, this function needs an onEdit trigger. This trigger has to be installed, because a simple trigger cannot access services that require authorization.

您可以手动安装此触发器 （检查此屏幕截图，如果在配置触发器类型时遇到问题）。

You can install this trigger manually by following these steps (check this screenshot if you have problems when configuring the type of trigger).

您可以还可以通过编程方式安装此触发器，如此处所述。

You can also install this trigger programmatically, as explained here.

请告诉我现在是否适合您。希望对您有所帮助。

Please let me know if that works for you now. I hope this is of any help.

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