Google Apps script query to search for all files not owned by a user

Jason O 0
Viewed 40

In Google Drive, if I want to search for all files not owned by a certain user, I would put in "-owner: somebody@gmail.com" and it will list all files that are not owned by somebody@gmail.com. Now, I would like to create an equivalent search query in a Google Apps script to do the same thing but I'm having some trouble forming it correctly. So far, I have tried

var files = operatorFolder.searchFiles('not "someUser@gmail.com" in owners');

But this doesn't seem to work. What am I missing?

Edit:

Here's a copy of the full script. It searches the folder with the given ID and saves the results into the Active google sheet with the name "File List"

function getMyFilesFromDrive() {
  // Get reference to Lit Folder
  var parentFolder = DriveApp.getFolderById("IDSTRING");
  var childFolders = parentFolder.getFolders();
  var count = 0;

  // Get reference to active spreadsheet and clear it
  var sheet = SpreadsheetApp.getActive().getSheetByName("File List");
  sheet.clear();

  // Create Header row
  var header = ["Name", "Owner", "Last Modified"];
  sheet.appendRow(header);

  while(childFolders.hasNext())
  {
    var operatorFolder = childFolders.next();

    Logger.log(operatorFolder.getName());

    // Get File List (all files not owned by Lauren)
    var files = operatorFolder.searchFiles("not 'someUser@gmail.com' in owners");

    while (files.hasNext()) {
      var file = files.next();
      count++;
      var data = [
        file.getName(),
        file.getOwner().getName(),
        file.getLastUpdated(),
        
      ];

      sheet.appendRow(data);
      Logger.log("Count: " + count);
    }
  }

  sheet.appendRow([""]);
  sheet.appendRow(["File Count: " + count])
}

function getSubFolders(parent) {
  parent = parent.getId();
  var childFolder = DriveApp.getFolderById(parent).getFolders();
  while(childFolder.hasNext()) {
    var child = childFolder.next();
    Logger.log(child.getName());
    getSubFolders(child);
  }
  return;
}
google-drive-api google-sheets google-apps-script
1 Answers

I thought that 'not "somebody@gmail.com" in owners' can be used. In this case, how about the following modification using not operator? But, in this case, I thought that when the owner of file is not found, an error occurs. So, as a sample script, when the owner of file is checked, how about the following modification?

From:

var files = operatorFolder.searchFiles('not "somebody@gmail.com" in owners');

To:

var files = operatorFolder.searchFiles('not "somebody@gmail.com" in owners'); // or var files = operatorFolder.searchFiles("not 'somebody@gmail.com' in owners");
while (files.hasNext()) {
  var file = files.next();
  var owner = file.getOwner();
  if (owner) {
    console.log(owner.getEmail())
  }
}

Reference:

Added:

From your showing script, I thought that the reason for your current issue might be due to as follows.

Modification points:

  • In the current stage, for example, when the owner of the original file is not you, the file is existing in your Google Drive as the shortcut file. In this case, the owner of the shortcut file is you. By this, "not 'someUser@gmail.com' in owners" cannot be used. This situation appears when the files are retrieved from a specific folder. For example, when the files are retrieved from all Google Drive like DriveApp.searchFiles('not "somebody@gmail.com" in owners'), this can be worked. In the current stage, it seems that there is no method for directly excluding the owner of the original file by the email address in the search query, when the file list is retrieved from a specific folder.
  • In your script, it seems that the subfolders are not retrieved.
  • When appendRow is used in the loop, the process cost becomes high. Ref In this modification, the retrieved values are put using one call of setValues.

When these points are reflected in a sample script, it becomes as follows.

Modified script:

function sample1() {
  var parentFolder = DriveApp.getFolderById("###"); // Please set the folder ID.
  var excludeOwnerEmail = "someUser@gmail.com";

  var searchFlies = (folder, res = []) => {
    var files = folder.getFiles();
    while (files.hasNext()) {
      var file = files.next();
      file = file.getMimeType() == MimeType.SHORTCUT ? DriveApp.getFileById(file.getTargetId()) : file;
      var owner = file.getOwner() ? file.getOwner().getEmail() : "";
      if (owner && owner != excludeOwnerEmail) {
        res.push([file.getName(), file.getOwner().getName(), file.getLastUpdated()]);
      }
    }
    var folders = folder.getFolders();
    while (folders.hasNext()) searchFlies(folders.next(), res);
    return res;
  }
  var v = searchFlies(parentFolder);
  var header = ["Name", "Owner", "Last Modified"];
  var values = [header, ["File Count: " + v.length, ...Array(header.length - 1).fill(null)], ...v];
  var sheet = SpreadsheetApp.getActive().getSheetByName("File List");
  sheet.clear();
  sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}

Or, if you want to retrieve the file list just under the specific folder, how about the following sample script?

function sample2() {
  var parentFolder = DriveApp.getFolderById("###"); // Please set the folder ID.
  var excludeOwnerEmail = "someUser@gmail.com";

  var res = [];
  var files = parentFolder.getFiles();
  while (files.hasNext()) {
    var file = files.next();
    file = file.getMimeType() == MimeType.SHORTCUT ? DriveApp.getFileById(file.getTargetId()) : file;
    var owner = file.getOwner() ? file.getOwner().getEmail() : "";
    if (owner && owner != excludeOwnerEmail) {
      res.push([file.getName(), file.getOwner().getName(), file.getLastUpdated()]);
    }
  }
  var header = ["Name", "Owner", "Last Modified"];
  var values = [header, ["File Count: " + res.length, ...Array(header.length - 1).fill(null)], ...res];
  var sheet = SpreadsheetApp.getActive().getSheetByName("File List");
  sheet.clear();
  sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}
Tanaike0
Related