Google Apps Script – Search for Names and File Paths in Folders

google sheetsgoogle-apps-scriptgoogle-drive

I'm making apps script to find files that don't contain the names "Done" and "InProgress".
enter image description here
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.
enter image description here

Best Answer

  • 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);
}
Related Topic