Google Sheets installable onEdit()触发器未被授权添加日历事件

Question

我正在编写一个附在谷歌电子表格上的脚本，该脚本可以帮助我为员工生成轮班时间表，创建谷歌日历，并在人们换班时更新日历。我在电子表格上附加了一个onEdit()可安装触发器，用于检查用户是否更改了某个范围内的单元格(以交换班次)，然后在电子表格中找到日历上的单元格，交换它们，并调用和更新GoogleCalendar。

当从脚本编辑器执行时，它可以完美地运行。但当从电子表格上的触发器执行时，我会得到以下错误：

"执行失败：您无权调用getOwnedCalendarsByName(第204行，文件"Code")[1.021秒总运行时间]">

在谷歌应用程序权限中，它对脚本说："谷歌日历管理您的日历"，因此它应该得到授权。

以下是可安装触发器OnEdit 调用的函数

function onEdit(e) {
var sheets = SpreadsheetApp.getActive();
var dutySwitchSheet = sheets.getSheetByName('Duty Switches');
if(!e){
var e = {};
e.range = dutySwitchSheet.getRange(30, 9);
}
Logger.log(e.range.getValue());
var changedRow = e.range.getRow();
var changedCol = e.range.getColumn();
var RAInitials = sheets.getRangeByName("dutySwitchRAInitials");
var rangeTop = RAInitials.getRow();
var rangeBottom = RAInitials.getLastRow();
var rangeLeft = RAInitials.getColumn();
var rangeRight = RAInitials.getLastColumn();
Logger.log("changedRow: %s, changedCol: %s, rangeTop: %s, rangeBottom: %s, rangeLeft: %s, rangeRight: %s", changedRow, changedCol, rangeTop, rangeBottom, rangeLeft, rangeRight);
if( changedRow >= rangeTop && changedRow <= rangeBottom && changedCol >= rangeLeft && changedCol <= rangeRight && e.range.getSheet().getName().toUpperCase() === dutySwitchSheet.getName().toUpperCase()){
Logger.log("event sheet name: %s, Duty Switch Sheet Name: %S", e.range.getSheet().getName().toUpperCase(), dutySwitchSheet.getName().toUpperCase());
var relRow = changedRow - rangeTop + 1;
Logger.log("Checking if both initials are present");
if(RAInitials.getCell(relRow, 1).getValue() && RAInitials.getCell(relRow,2).getValue()){
var ui = SpreadsheetApp.getUi()
ui.showModalDialog(HtmlService.createHtmlOutput(""), "Checking Your input, please wait..");
Logger.log("updating");
var RA1Range = sheets.getRangeByName("dutySwitchRA1");
var RA2Range = sheets.getRangeByName("dutySwitchRA2");
var calendarRange = sheets.getRangeByName("dutyDays");
var startDate = new Date(sheets.getRangeByName("startDate").getValue());
var startDay = startDate.getDay();
var RA1 = {name: RA1Range.getCell(relRow, 1).getValue(), shiftNum: RA1Range.getCell(relRow, 2).getValue(), date: new Date(RA1Range.getCell(relRow, 3).getValue()) };
if(RA1.shiftNum != "N/A"){
RA1.day = Math.floor( (RA1.date.getTime() - startDate.getTime())/(24*60*60*1000) ) + startDay;
RA1.SwappedCell = calendarRange.getCell( Math.floor(RA1.day / 7) * 3 + Number(RA1.shiftNum) + 1, (RA1.day % 7) + 1 );
RA1.cellValue = String(RA1.SwappedCell.getValue());
}
var RA2 = {name: RA2Range.getCell(relRow, 1).getValue(), shiftNum: RA2Range.getCell(relRow, 2).getValue(), date: new Date(RA2Range.getCell(relRow, 3).getValue()) };
if(RA2.shiftNum != "N/A"){
RA2.day = Math.floor( (RA2.date.getTime() - startDate.getTime())/(24*60*60*1000) ) + startDay;
RA2.SwappedCell = calendarRange.getCell( Math.floor(RA2.day / 7) * 3 + Number(RA2.shiftNum) + 1, (RA2.day % 7) + 1 );
RA2.cellValue = String(RA2.SwappedCell.getValue());
}
Logger.log("RA1 name: %s, date: %s, cellValue: %s; RA2 name: %s, date: %s, cellValue: %s" , RA1.name, RA1.date, RA1.cellValue, RA2.name, RA2.date, RA2.cellValue);
Logger.log("RA1 name matches: %s, RA2 name matches: %s", RA1.cellValue.indexOf(RA1.name) != -1, RA2.cellValue.indexOf(RA2.name) != -1);
if(RA1.cellValue.indexOf(RA1.name) != -1 || RA1.shiftNum == "N/A" && RA2.cellValue.indexOf(RA2.name) != -1 || RA2.shiftNum == "N/A" ){
Logger.log("Swapping Cells");
if(RA1.shiftNum != "N/A"){
RA1.SwappedCell.setValue( String(RA1.SwappedCell.getValue()).replace(RA1.name, RA2.name) );
}
if(RA2.shiftNum != "N/A"){
RA2.SwappedCell.setValue( String(RA2.SwappedCell.getValue()).replace(RA2.name, RA1.name) );
}
Logger.log("Syncing Google Calendar");
syncGoogleCalendar();
ui.showModalDialog(HtmlService.createHtmlOutput(""), "The Google Calendar has been updated! :)");
Logger.log("Done Updating!");
} 
else {
Logger.log("Did Not Swap Cells");
ui.showModalDialog(HtmlService.createHtmlOutput(""), "The info input does not match the spreadsheet.  Couldn't update :(");
}
}
}
}

这里是updateGoogleCalendar()函数，直到它抛出错误

function syncGoogleCalendar(){
var dutyCalendars = CalendarApp.getOwnedCalendarsByName("RA Duty Calendar");

Answer 1

简短回答

更改onEdit功能的名称

解释

不要使用保留函数的名称来命名要由可安装触发器调用的函数。

onEdit是一个Google Apps Script保留函数，用于告诉Google Apps脚本引擎在编辑事件发生时应运行该函数。它不能调用需要授权才能运行的服务。有关更多详细信息，请参阅简单触发器。