google-apps-script - 错误 : "You have been creating or deleting too many calendars or calendar events in a short time."

Question

第一次在这里提问。

我已经构建了一个 Google App 脚本，它根据 @Mogsdad's answer here 从 Google 表格中获取事件信息并在 Google 日历上创建事件。 . (由于我不存在的声誉，我无法在该线程上发布问题)

“exportEvents”功能第一次运行得很好。但是一旦电子表格有更多行信息，它并没有完成脚本而是说 “您在短时间内创建或删除了过多的日历或日历事件。请稍后再试。”

这不是因为 Google 设置了每日配额，因为我每天没有创建接近 10,000 个事件。我搜索了一个解决方案，并找到了 this on Google Product Forum作者@JEvans-GSA，他的脚本像我一样基于@Mogsdad 的脚本。

我调整了他的解决方案并将“updateEvents”函数与“exportEvents”分开(并创建了“deleteEvents”函数)。现在“exportEvents”不会遇到“您在短时间内创建或删除了太多日历或日历事件。请稍后再试”的问题。 但是“updateEvents”在执行到一半时仍然收到相同的警告!

例如，我创建了 83 个标题为“1”到“83”的事件并导出了
事件日历成功。

然后我将事件标题更改为“100”到“182”并尝试更新事件。

它只是将“1”~“15”更新为“101”~“114”，其他一切都保持不变。 - 我让函数将“状态”列更新为“已更新(今天的
date)"，也没有执行。

所以函数启动了但没有完成，这让我觉得错误一定是在设置重复的某个地方。是否有一种解决方法可以使“updateEvents”工作，或者作为 @Mogsdad suggested in the original answer , 是删除和重新创建事件的唯一其他方法吗？

预先感谢您的任何帮助。以下是我的电子表格的链接(由于我的低代表，我不允许使用超过 2 个链接):- https://docs.google.com/spreadsheets/d/1V6eioCo4QDPO2DdCyW93sLRWT_xtzKM-drDPVI1Gd8s/edit?usp=sharing

而我目前的脚本:

//Global settings
var calId = 'n02m72vanqabnt1gcjluk2vosk@group.calendar.google.com';
var moderatorEmail = 'EMAIL_ADDRESS';

var idId = 0;
var startTimeId = 1;
var endTimeId = 2;
var titleId = 3;
var organizerId = 4;
var locId = 5;
var descId = 6;
var urlId = 7;
var topicId = 8;
var typeId = 9;
var contactId = 10;
var contactEmailId = 11;
var actionId = 12;
var statusId = 13;

//Add a custom menu
function onOpen() {
  var ss = SpreadsheetApp.getActive();
  var items = [
    {name: 'Export Events', functionName: 'exportEvents'},
    {name: 'Update Events', functionName: 'updateEvents'},
    {name: 'Delete Events', functionName: 'deleteEvents'}
  ];
  ss.addMenu('Calendar', items);
}

//Actual functions
function exportEvents() {
  var spreadsheet = SpreadsheetApp.getActiveSheet();
  var headerRows = 2; //Number of rows of header info to skip
  var range = spreadsheet.getDataRange();
  var data = range.getValues();
  var cal = CalendarApp.getCalendarById(calId);
  for (i in data) {
    if (i < headerRows) continue; //Skip header rows
    var row = data[i];
    var title = row[titleId];
    var tstart = new Date(row[startTimeId]);
      tstart.setDate(tstart.getDate());
      tstart.setMonth(tstart.getMonth());
      tstart.setYear(tstart.getYear());
      tstart.setTime(tstart.getTime());
    var tstop = new Date(row[endTimeId]);
      tstop.setDate(tstop.getDate());
      tstop.setMonth(tstop.getMonth());
      tstop.setYear(tstop.getYear());
      tstop.setTime(tstart.getTime());
    var loc = row[locId];
    var contact = row[contactId];
    var organizer = row[organizerId];
    var topic = row[topicId];
    var type = row[typeId];
    var contactEmail = row[contactEmailId];
    var url = row[urlId];
    var status = row[statusId];
    var desc = (row[descId]
        +"\n\n-Organizer: "+organizer
        +"\n-Event URL: "+url
        +"\n-Event Type: "+type
        +"\n-Event Contact: "+contact+" ("+contactEmail+")")
    var id = row[idId];
    // Check if the 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("Month D, YYYY 00:00:00"), new Date("Month D, YYYY 00:00:00), {description:desc,location:loc});
      var newEvent = cal.createEvent(title, tstart, tstop, {description:desc,location:loc}).getId();
      row[idId] = newEvent; // Update the data array with event ID 

      var d = new Date();
      var time = d.getMonth()+1 + "/" + d.getDate() + "/" + d.getFullYear()
      row[statusId] = "Exported " + time
      }
    debugger;
  }
  range.setValues(data);
}

function updateEvents() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var headerRows = 2; //Number of rows to skip
  var range = sheet.getDataRange();
  var data = range.getValues();
  var cal = CalendarApp.getCalendarById(calId);
  for (i in data) {
    if (i < headerRows) continue;
    var row = data[i];
    var title = row[titleId]; //Second column
    var tstart = new Date(row[startTimeId]);
      tstart.setDate(tstart.getDate());
      tstart.setMonth(tstart.getMonth());
      tstart.setYear(tstart.getYear());
      tstart.setTime(tstart.getTime());
    var tstop = new Date(row[endTimeId]);
      tstop.setDate(tstop.getDate());
      tstop.setMonth(tstop.getMonth());
      tstop.setYear(tstop.getYear());
      tstop.setTime(tstart.getTime());
    var loc = row[locId];
    var contact = row[contactId];
    var organizer = row[organizerId];
    var topic = row[topicId];
    var type = row[typeId];
    var contactEmail = row[contactEmailId];
    var url = row[urlId];
    var action = row[actionId];
    var status = row[statusId];
    var desc = (row[descId]
        +"\n\n-Organizer: "+organizer
        +"\n-Event URL: "+url
        +"\n-Type: "+type
        +"\n-Event Contact: "+contact+" ("+contactEmail+")")
    var id = row[idId];
    try {
      var event = cal.getEventSeriesById(id);
    }
    catch (e) {
      // do nothing
    }
    if (event) {
      if (action === "Update") {
        event.setTitle(title);
        event.setDescription(desc);
        event.setLocation(loc);
      // eventSetTime(tstart, tstop); // cannot setTime on eventSeries.
      // ... but we Can set recurrence!
        var recurrence = CalendarApp.newRecurrence().addDailyRule().times(1);
        event.setRecurrence(recurrence, tstart, tstop);

        var d = new Date();
        var time = d.getMonth()+1 + "/" + d.getDate() + "/" + d.getFullYear()
        row[statusId] = "Updated " + time
      }
    }
  }
  debugger;
  range.setValues(data);
}


function deleteEvents() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var headerRows = 2; //Number of rows to skip
  var range = sheet.getDataRange();
  var data = range.getValues();
  var cal = CalendarApp.getCalendarById(calId);
  for (i in data) {
    if (i < headerRows) continue;
    var row = data[i];
    var action = row[actionId];
    var status = row[statusId];
    var id = row[idId];
    try {
      var event = cal.getEventSeriesById(id);
    }
    catch (e) {
    }
    if (event) {
      if (action === "Delete") {
        event.deleteEventSeries();

        var d = new Date();
        var time = d.getMonth()+1 + "/" + d.getDate() + "/" + d.getFullYear()
        row[idId] = "Deleted";
        row[statusId] = "Deleted " + time;
      }
    }
  }
  debugger;
  range.setValues(data);
}

Answer 1

在工作日创建日历事件块的脚本也有类似的问题，仅使用嵌套的 while 循环。我插入了 Utilities.sleep(3000) 以便脚本在每 10 次迭代后暂停。我对其进行了 20 周的测试，每周创建 20 个事件(400 个事件)并且没有错误消息。我将调整这个时间，看看我是否可以减少 sleep 时间。这是 Google 开发人员引用信息:

https://developers.google.com/apps-script/reference/utilities/utilities#sleep(Integer)