2

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

0
  • 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);
}
4
  • The script is running, but the file in the sub folder does not appear. May 28 '19 at 1:40
  • In the parent folder there are sub folder, named test1 and test2. i.stack.imgur.com/6Ev3g.png May 28 '19 at 1:41
  • @Lutfi Creativesys Thank you for replying. I couldn't notice that you wanted to retrieve files in the subfolders. I apologize for this. I added a sample script. Could you please confirm it? I apologize the script is largely changed from your original one.
    – Tanaike
    May 28 '19 at 4:31
  • 1
    Solved. Thanks you so much @Tanaike May 28 '19 at 4:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.