Apps Script to search for names and file paths in folders by name

Question

I'm making apps script to find files that don't contain the names "Done" and "InProgress". The script that I have created is like this:

function testSearch(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  var parentFolder = DriveApp.getFolderById('1gr0RIGIt0AEzEqIxJigGxYlxQGy8yLqp')
  var allFile = parentFolder.getFiles()

  var filter = parentFolder.searchFiles('fullText not contains "Done"' && 'fullText not contains "InProgress"');

  var cnt = 0;

  while (allFile.hasNext()) {
    var filter = allFile.next();
    cnt++;
      var data = [
        filter.getName(),
        filter.getUrl(),
      ];
        sheet.appendRow(data);
   }
}

After I run the script, the results do not match.

Answer 1

• You want to retrieve files in the specific folder.
• You want to retrieve all files with the filenames which don't include Done and InProgress.

If my understanding is correct, how about this modifiaction?

Modification point:

• You can directly retrieve files with the filename using the search query of not title contains 'Done' and not title contains 'InProgress'.

Modified script:

function testSearch(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var parentFolder = DriveApp.getFolderById('1gr0RIGIt0AEzEqIxJigGxYlxQGy8yLqp')

  var files = parentFolder.searchFiles("not title contains 'Done' and not title contains 'InProgress'"); // Modified

  var cnt = 0;
  while (files.hasNext()) { // Modified
    var file = files.next();
    cnt++;
    var data = [
      file.getName(),
      file.getUrl(),
    ];
    sheet.appendRow(data);
  }
}

Note:

• I'm not sure about cnt in your script.

Reference:

• Search for Files
  • This is for Drive API v3. When you use Class DriveApp, please use the parameter for Drive API v2.
  • In your case, the filename is title.

If I misunderstood your question and this was not the result you want, I apologize.

Edit:

When you want to retrieve files in the subfolders, please use the following script.

Sample script:

function testSearch(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var parentFolder = DriveApp.getFolderById('1gr0RIGIt0AEzEqIxJigGxYlxQGy8yLqp')
  function getFiles(folder){
    var files = folder.searchFiles("not title contains 'Done' and not title contains 'InProgress'");
    while (files.hasNext()) {
      var file = files.next();
      var data = [
        file.getName(),
        file.getUrl(),
      ];
      sheet.appendRow(data);
    }
    var folders = folder.getFolders();
    while (folders.hasNext()) getFiles(folders.next());
  }
  getFiles(parentFolder);
}