Issue

This Content is from Stack Overflow. Question asked by Jason O

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

Solution

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:

• Search query terms and operators

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);
}

 This Question was asked in  StackOverflow by  Jason O and Answered by Tanaike It is licensed under the terms of 
CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.