第一次在这里提问。
我构建了一个 Google 应用程序脚本,它从 Google 表格获取事件信息,并基于以下内容在 Google 日历上创建事件:@Mogsdad 的回答在这里。 (由于我不存在的声誉,我无法在该线程上发布问题)
“exportEvents”函数第一次运行得非常好。但是,一旦电子表格有了更多行信息,它就没有完成脚本,而是说“您在短时间内创建或删除了太多日历或日历事件。请稍后再试。”
这不是由于 Google 设置的每日配额造成的,因为我每天不会创建接近 10,000 个事件。我寻找解决方案,发现此内容位于 Google 产品论坛上作者是 @JEvans-GSA,他的脚本是基于像我一样的 @Mogsdad 的脚本。
我改编了他的解决方案,并将“updateEvents”函数与“exportEvents”分离(并且还创建了“deleteEvents”函数)。现在,“exportEvents”不会遇到“您在短时间内创建或删除了太多日历或日历事件。请稍后重试”的问题。但是“updateEvents”在执行中途仍然收到相同的警告!
- 例如,我创建了 83 个标题为“1”到“83”的事件,并导出了
事件成功添加到日历。
- 然后我将事件标题更改为“100”到“182”并尝试更新事件。
- 它只是将“1”~“15”更新为“101”~“114”,其他一切保持不变。 - 我使该函数将“状态”列更新为“已更新(今天的
date)”,也没有执行。
所以函数开始但没有完成,这让我认为错误一定是在设置重复发生的某个地方。是否有解决方法可以使“updateEvents”工作,或者作为@Mogsdad 在原始答案中建议,是删除和重新创建事件的唯一其他方法吗?
预先感谢您的任何帮助。以下是我的电子表格的链接(由于我的代表程度较低,我不允许使用超过 2 个链接):-https://docs.google.com/spreadsheets/d/1V6eioCo4QDPO2DdCyW93sLRWT_xtzKM-drDPVI1Gd8s/edit?usp=sharing
我当前的脚本:
//Global settings
var calId = '[email protected]';
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);
}