CODEWITHSUNDEEP
google-apps-scriptgoogle-drive-api
pgSystemTester
pgSystemTester

Reputation: 9897

Return Collection of Google Drive Files Shared With Specific User

I'm trying to get a collection of files where user (let's use [email protected]) is an editor.

I know this can be accomplished almost instantly on the front-end of google drive by doing a search for to:[email protected] in the drive search bar.

I presume this is something that can be done in Google App Scripts, but maybe I'm wrong. I figured DriveApp.searchFiles would work, but I'm having trouble structuring the proper string syntax. I've looked at the Google SDK Documentation and am guessing I am doing something wrong with the usage of the in matched to the user string search? Below is the approaches I've taken, however if there's a different method to accomplishing the collection of files by user, I'd be happy to change my approach.

var files = DriveApp.searchFiles(

   //I would expect this to work, but this doesn't return values
  'writers in "[email protected]"');

   //Tried these just experimenting. None return values
  'writers in "to:[email protected]"');
  'writers in "to:[email protected]"');
  'to:[email protected]');

   // this is just a test to confirm that some string searches successfully work
   'modifiedDate > "2013-02-28" and title contains "untitled"');

Upvotes: 0

Views: 270

Answers (2)

pgSystemTester
pgSystemTester

Reputation: 9897

Thanks @theAddonDepot! To illustrate specifically how the accepted answer is useful, I used it to assist in building a spreadsheet to help control files shared with various users. The source code for the full procedure is at the bottom of this post. It can be used directly within this this google sheet if you copy it.

The final result works rather nicely for listing out files by rows and properties in columns (i.e. last modified, security, descriptions... etc.).

The ultimate purpose is to be able to update large number of files without impacting other users. (use case scenario for sudden need to immediately revoke security... layoffs, acquisition, divorce, etc).

//code for looking up files by security
//Posted on stackoverlow here: https://stackoverflow.com/questions/62940196/return-collection-of-google-drive-files-shared-with-specific-user
//sample google File here: https://docs.google.com/spreadsheets/d/1jSl_ZxRVAIh9ULQLy-2e1FdnQpT6207JjFoDq60kj6Q/edit?usp=sharing


const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("FileList");
const clearRange = true;


//const clearRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName("ClearRange").getValue();
//if you have the named range setup.


function runReport() {
//var theEmail=  SpreadsheetApp.getActiveSpreadsheet().getRangeByName("emailFromExcel").getValue();
 //or
 var theEmail = '[email protected]';
  
  findFilesByUser(theEmail);
  
}

function findFilesByUser(theUserEmail) {
  if(clearRange){
   ss.getDataRange().offset(1,0).deleteCells(SpreadsheetApp.Dimension.ROWS)
  }

  var someFiles = DriveApp.searchFiles('"' + theUserEmail + '" in writers');

  var aListOfFiles = []
  
  while(someFiles.hasNext()){
    var aFile = someFiles.next();
    aListOfFiles.push([aFile.getId()
                       ,aFile.getName()
                       ,aFile.getDescription()
                       ,aFile.getSharingAccess()
                       ,aFile.getSharingPermission()
                       ,listEmails(aFile.getEditors())
                       ,listEmails(aFile.getViewers())
                       ,aFile.getMimeType().replace('application/','').replace('vnd.google-apps.','')
                       ,aFile.getDateCreated()
                       ,aFile.getLastUpdated()
                       ,aFile.getSize()
                       ,aFile.getUrl()
                       ,aFile.getDownloadUrl()
                       ])                                  
  }
  
  if(aListOfFiles.length==0){
    aListOfFiles.push("no files for " + theUserEmail);
  }
  ss.getRange(ss.getDataRange().getLastRow()+1,1, aListOfFiles.length, aListOfFiles[0].length).setValues(aListOfFiles); 
}

function listEmails(thePeople){
  var aList = thePeople;
 
  for (var i = 0; i < aList.length;i++){
    aList[i] = aList[i].getEmail();
  }
  
  return aList.toString();
}

Upvotes: 1

TheAddonDepot
TheAddonDepot

Reputation: 8964

Try flipping the operands within the in clause to read as:

var files = DriveApp.searchFiles('"[email protected]" in writers');

Upvotes: 2

Related Questions