Add Viewers and Editors listed on Sheets to folders and files in Shared Drive using GAS

Question

I have a list of folders and files that need to be shared with various users (as Viewers and Editors) in Shared Drive. When I copied over the folders and files, it did not copy over the users.

I have a sheet with the following information below. I pulled the viewers and editors prior to copying the folder and its contents to Shared Drive. My goal is to run code to pull the new Folder or File Id in Share Drive and the viewers and editors respectively.

Info prior to copying to Shared Drive to get the viewers and editors: Path, Name, Folder ID, User Access, Owner, Viewers, Editors, Folder/File Date

Info after copying to Shared Drive: Path, Name, Folder ID, User Access, Viewers, Editors, Folder/File Date

I have used this code in the past and works just fine for MyDrive folder sharing (shared to <3 people) but when I try to do the same for a Shared Drive folder/files, it does not seem to work. Is there an easy way to add these users and silently sharing the folders and files?

 function setPermissions(){
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName('sheetname');
  var start = 2;
  var last = sheet.getLastRow();
  var sheetValues = ssFilesSheet.getRange(start,1,last,ssFilesSheet.getLastColumn()).getValues();

  for (var i = 0; i < sheetValues.length; i++) {
    var row = sheetValues[i];   
    var id = row[0];
    var reader = row[3];
    var writer = row[4];

    var roles = {
  writer: [writer],
  reader: [reader]      
};

for(var key in roles) {
  var role = roles[key];
  role.forEach(function (email) {
    var resource = {
      role: key,
      type: "user",
      value: email
    }
    if(key == "commenter") {
      resource.role = "reader";
      resource.additionalRoles = ["writer"]
    }        
    Drive.Permissions.insert(resource, 
                             id, 
                             {sendNotificationEmails: false, 
                              // supportsTeamDrives:true,
                              supportsAllDrives: true, 
                              // useDomainAdminAccess:false });
                             });

  }
}

Your time and help are greatly appreciated!

ziganotschka ziganotschka · Accepted Answer · 2020-06-15T08:22:47

To set a user as writer or reader for a file on a shared drive, as of now you need to set the request paramter supportsAllDrives to true, so in Apps Script:

Drive.Permissions.insert(
        {
          'role': 'owner',
          'type': 'user',
          'value': 'user email'
        },
        folder/fileid,
        {
          'sendNotificationEmails': 'false',
          'supportsAllDrives' : `true`
        });

Setting a user as the owner of a file on a shared drive via Drive API is currently not possible. There have been feature requests for this, so it is likely that this feature will be incorporated by Google in the future.

UPDATE

Based on your updated code, if you want to extract several email addresses located in the same cell, you need to retrieve them as a string a convert them into an array e.g. with split().

Sample:

 for (var i = 0; i < sheetValues.length; i++) {

    var row = sheetValues[i];   
    var id = row[0];
    var reader = row[1];
    var writer = row[2];

    var roles = {
      writer: writer,
      reader: reader
    };

    for(var key in roles) {
      var role = roles[key];
      var roleArray = role.split(", ");
      roleArray.forEach(function (email) {
        if(email !="" && email !=" "){
          var resource = {
            role: key,
            type: "user",
            value: email
          }
          if(key == "commenter") {
            resource.role = "reader";
            resource.additionalRoles = ["writer"]
          }        
          Logger.log("value: " + email);
          //Customize parameters sendNotificationEmails:false,supportsTeamDrives:true,useDomainAdminAccess:false
          Drive.Permissions.insert(resource, 
                                   id, 
                                   {sendNotificationEmails: false, 
                                    // supportsTeamDrives:true,
                                    supportsAllDrives: true, 
                                    // useDomainAdminAccess:false });
                                   });
        }
      })
    };

  }
  ssFilesSheet.getRange(i+2,6).setValue('Added User Permissions');

Add Viewers and Editors listed on Sheets to folders and files in Shared Drive using GAS

1 Answers