Google表格可安装onEdit（）触发器无权添加日历事件 [英] Google Sheets installable onEdit() Trigger not authorized to add calendar event
问题描述
我正在制作附加到Google Spreadsheet的脚本，帮助我为员工生成一个轮班计划，创建一个Google日历，并在人员交换轮班时更新日历。我有一个连接到电子表格的onEdit（）可安装触发器，用于检查用户是否更改了特定范围内的单元格（以便切换），然后在电子表格的日历中找到单元格，将它们交换，并调用和更新GoogleCalendar。
它从脚本编辑器执行时运行完美。但是，当从电子表格的触发器执行时，出现此错误：
$ b
执行失败：您无权调用getOwnedCalendarsByName（第204行，文件代码）[总运行时间为1.021秒]
在Google应用程序权限中， ：Google Calendar
管理您的日历，因此它应该被授权。
以下是由可安装触发器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）;如果（changedRow> = rangeTop&& changedRow< = rangeBottom&&&changedCol> = rangeLeft&& changedCol< = rangeRight& e.range.getSheet（）， getName（）。toUpperCase（）=== dutySwitchSheet.getName（）。toUpperCase（））{
Logger.log（事件表名称：％s，占空比名称：％S，e.range。 getSheet（）。getName（）。toUpperCase（），dutySwitchSheet.getName（）。toUpperCase（））;
var relRow = changedRow - rangeTop + 1;
Logger.log（检查两个首字母是否存在）;
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（更新）;
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（）;
$ b $ var RA1 = {name：RA1Range.getCell（relRow，1）.getValue（），shiftNum：RA1Range.getCell（relRow，2）.getValue（），date：new Date（RA1Range.getCell （relRow，3）.getValue（））}; $（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（））;
$ b var RA2 = {name：RA2Range.getCell（relRow，1）.getValue（），shiftNum：RA2Range.getCell（relRow，2）.getValue（），date：new日期（RA2Range.getCell（relRow，3）.getValue（））}; $（b2.b）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名称：％s，日期：％s，cellValue：％s; RA2名称：％s，日期：％s，cellValue：％s，RA1.name ，RA1.date，RA1.cellValue，RA2.name，RA2.date，RA2.cellValue）;
Logger.log（RA1名称匹配：％s，RA2名称匹配：％s，RA1.cellValue.indexOf（RA1.name）！= -1，RA2.cellValue.indexOf（RA2.name）！ = -1）; （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）） ; （RA2.SwappedCell.getValue（））。replace（RA2.name，
）
if（RA2.shiftNum！=N / A）{
RA2.SwappedCell.getValue RA1.name））;
}
Logger.log（同步Google日历）;
syncGoogleCalendar（）;
ui.showModalDialog（HtmlService.createHtmlOutput（），Google日历已更新！:)）;
Logger.log（完成更新！）;
}
else {
Logger.log（未交换单元格）;
ui.showModalDialog（HtmlService.createHtmlOutput（），信息输入与电子表格不匹配。无法更新:(）;
}
}
}
}
这里是updateGoogleCalendar（）函数，直到它抛出错误
function syncGoogleCalendar（）{
var dutyCalendars = CalendarApp.getOwnedCalendarsByName（RA Duty日历）;
简短回答
更改onEdit函数的名称
解释
Don' t使用保留函数的名称来命名由可安装触发器调用的函数。
onEdit 是一个Google Apps脚本保留函数，用于说明在编辑事件发生时该功能应运行的Google Apps脚本引擎，它不能调用需要授权的服务请参阅简单触发器了解详情。
I'm working on a script attached to a Google Spreadsheet that helps me generate a shift schedule for employees, create a google calendar, and update the calendar when people swap shifts. I have an onEdit() installable trigger attached to the spreadsheet that checks if a user changed a cell in a certain range (to swap shifts), then finds the cells on the calendar in the Spreadsheet, swaps them, and calls and updateGoogleCalendar.
It runs perfectly when executed from the script editor. But when executed from the trigger on the spreadsheet, I get this error:
"Execution failed: You do not have permission to call getOwnedCalendarsByName (line 204, file "Code") [1.021 seconds total runtime]"
In the google apps permissions, it says this for the script: "Google Calendar Manage your calendars" so it should be authorized.
Here's the function called by the installable trigger 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 :(");
}
}
}
}
And here's the updateGoogleCalendar() function untill it throws the error
function syncGoogleCalendar(){
var dutyCalendars = CalendarApp.getOwnedCalendarsByName("RA Duty Calendar");
Short answer
Change the name of the onEdit function
Explanation
Don't use the name of reserved functions to name functions to be called by installable triggers.
onEdit is a Google Apps Script reserved function used to tell the Google Apps Script engine that the function should be ran when the edit event occurs. It can't call services that require authorization to run. See Simple Triggers for further details.
这篇关于Google表格可安装onEdit（）触发器无权添加日历事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
