How to obtain a list of all users with access to a Google shared drive using apps script

4.9k views Asked by At

I used getEditors to obtain a list of editors of a spreadsheet and the returned list includes shared drive users. However, users with 'content manager' access to the shared drive are not included in the list. Any reason why this is the case?

I also found that getAccess may be used to obtain the type of access a specific user has to a drive folder. Using this approach, my objective would be to identify all users with FILE_ORGANIZER or ORGANIZER permission. See official documentation on permissions. Is it possible to use an if statement or a loop to get this information?

Alternatively, is there a workaround to obtaining a list of all users with access to a shared drive that I might not have considered?

PS: Advanced drive service is not enabled.

// This code aims to protect a sheet in a spreadsheet 
// by granting specific users access (super users) 
// and revoking the access of any other user that can edit the spreadsheet. 

/*
Attempted approach:

user1 and user2 have manager permission to the shared drive as a whole
user3 has content manager permission to the shared drive as a whole
user4 only has access to this spreadsheet in the shared drive

My objective is to ensure that only users 1 and 2 can edit the protected sheet.

Result:
Log shows that users 1 and 2 have access and user 4 does not.
However, user3 can still edit the sheet because they were no included in the getEditors() result hence their access could not be revoked. 
*/

function protectASheet() {
  var superusers = ['user1', 'user2'];
  var ss = SpreadsheetApp.getActive();
  var editors = ss.getEditors(); //get file editors
  var sheet = SpreadsheetApp.getActive().getSheetByName('TestSheet');

  //Set protection  
  var protection = sheet.protect().setDescription('This sheet is protected.');

  protection.addEditors(superusers); //Grant superusers edit permission
  protection.removeEditors(editors); //Revoke other file editors' permission

  Logger.log("Only the following can edit " + sheet.getSheetName() + ": " + protection.getEditors());


}

1

There are 1 answers

1
Jabak On

Got some help from a colleague. This approach relies on the advanced drive service being activate in the script editor. To turn on go to Resources -> Advanced Google Services.

It fetches the permissions and other details of every user that has any form of access to a file or folder without exception.

Code below:

function getPermissionsList() {
  const fileId = "<FILE, FOLDER OR SHARED DRIVE ID HERE>"; // ID of your shared drive

  // 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;
  var arr = [];

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

    arr.push([email, role]);

  }

  Logger.log(arr);

}