Permissions API Call Failing when used in onOpen simple trigger

Question

I've created a Google Sheet with an Apps Script to do issue and task tracking. One of the features I'm trying to implement is permissions based assigning of tasks. As a precursor to that, I have a hidden sheet populated with a list of users and their file permissions, using code similar to that in this StackOverflow question

When I manually run the code, it works fine. However, I want it to load every time the sheet is opened in case of new people entering the group or people leaving the group. So I made a call to my function in my onOpen simple trigger. However, when it is called via onOpen, I get the following:

GoogleJsonResponseException: API call to drive.permissions.list failed with error: Login Required at getPermissionsList(MetaProject:382:33) at onOpen(MetaProject:44:3)

Here are my functions:

My onOpen Simple Trigger:

  function onOpen() {

  //Constant Definitions for this function
  const name_Access = 'ACCESS';
  const row_header = 1;
  const col_user = 1;
  const col_level = 2;

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sht_Access = ss.getSheetByName(name_Access);

  var ui = SpreadsheetApp.getUi();
  ui.createMenu('MetaProject')
    .addSubMenu(
        ui.createMenu('View')
          .addItem('Restore Default Sheet View', 'restoreDefaultView')
          .addItem('Show All Sheets', 'showAllSheets')
        )
    .addSeparator()
    .addToUi();

  //Clear Contents, Leave Formatting
  sht_Access.clearContents();

  //Set Column Headers
  var head_name = sht_Access.getRange(row_header,col_user);
  var head_level = sht_Access.getRange(row_header,col_level);

  head_name.setValue('User');
  head_level.setValue('Level');

  //Refresh User List for use in this instance
  getPermissionsList();

}

Here is getPermissionsList:

function getPermissionsList() {

  const fileId = "<REDACTED>"; // ID of your shared drive
  const name_Sheet = 'ACCESS';
  const row_start = 2;
  const col_user = 1;
  const col_access = 2;

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var thissheet = ss.getSheetByName(name_Sheet);

  // THIS IS IMPORTANT! The default value is false, so the call won't 
  // work with shared drives unless you change this via optional arguments
  const args = {
    supportsAllDrives: true
  };

  // Use advanced service to get the permissions list for the shared drive
  let pList = Drive.Permissions.list(fileId, args);

  //Put email and role in an array
  let editors = pList.items;

  for (var i = 0; i < editors.length; i++) {
    let email = editors[i].emailAddress;
    let role = editors[i].role;

    //Populate columns with users and access levels / role
    thissheet.getRange(row_start + i,col_user).setValue(email);
    thissheet.getRange(row_start + i,col_access).setValue(role);

  }

Trashman · Accepted Answer · 2022-01-31T15:35:19.380

I searched before I posted and I found the answer via some related questions in the comments, but I didn't see one with an actual answer. The answer is, you cannot call for Permissions or anything requiring authorization from a Simple Trigger. You have to do an Installable trigger.

In order to do that do the following:

Create your function or rename it something other than "onOpen".

NOTE: If you name it onOpen (as I originally did and posted in this answer) it WILL work, but you will actually run TWO triggers - both the installable trigger AND the simple trigger. The simple trigger will generate an error log, so a different name is recommended.
Click the clock (Triggers) icon on the left hand side in Apps Script.
Click "+ Add Trigger" in the lower right
Select the name of your function for "which function to run"
Select "onOpen" for "select event type"
Click "Save".

The exact same code I have above now runs fine.

Permissions API Call Failing when used in onOpen simple trigger

1 Answers1