Google Sheets installable onEdit() Trigger not authorized to add calendar event

Question

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");

Rubén Rubén · Accepted Answer · 2017-10-13T02:41:35

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 Sheets installable onEdit() Trigger not authorized to add calendar event

1 Answers

Short answer

Explanation