Using a variable in a Google Apps Script searchFiles query

Question

This is the same question asked here but I'm unable to get the solution to work. I'm sorry to ask such a dumb question.

I need to search for files in a folder based on a cell value.

function aFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('mysheet');
  var folder = DriveApp.getFolderById('1VOnXdSd12XMCMedf4gwdOl8g_CL7VeEYo');
  var cell = sheet.getRange("A1");
  var value = cell.getValue();
  var files = folder.searchFiles("name contains '" + value + "'");
  while (files.hasNext()) {
    var file = files.next();
    Logger.log(file.getName);
  }
}

I get

Error
Exception: Invalid argument: q

on the start of the while loop. I tried making my query string in a single variable like:

  var searchstring = "\"name contains '" + value + "'\"";
  Logger.log(searchstring);
  var files = folder.searchFiles(searchstring); 

That particular searchstring logs as:

"name contains '20210328-0003-00'"

20210328-0003-00 is the value of the particular cell where I'm getting the value.

I also tried the following variations:

var searchstring = "'name contains \"" + value + "\"'";
//produces 'name contains "20210328-0003-00"'

var searchstring = "\"name contains \"" + value + "\"\"";
//produces  "name contains "20210328-0003-00""

var searchstring = "'name contains '" + value + "''";
//produces 'name contains '20210328-0003-00''

....and a few others. Same result for all.

This might not be relevant, but I tried using getFilesByName like:

var files = folder.getFilesByName('*' + value + '*');

That doesn't crash, but it also doesn't find any of the files that I can see with my own eyes are right there.

I'm probably doing something stupid. I'm probably stupid. Please don't hurt me.

Answer 1

In the current stage, it seems that searchFiles of DriveApp uses Drive API v2. So, if you want to search files using the filename, it is required to use title instead of name. I thought that this might be the reason for your current issue. So, how about the following modification?

And, in your script, Logger.log(file.getName) doesn't run the method of getName. So, please modify it to Logger.log(file.getName())

From:

var files = folder.searchFiles("name contains '" + value + "'");
while (files.hasNext()) {
  var file = files.next();
  Logger.log(file.getName);
}

To:

var files = folder.searchFiles("title contains '" + value + "'"); // or folder.searchFiles(`title contains '"${value}"'`)
while (files.hasNext()) {
  var file = files.next();
  Logger.log(file.getName());
}

If this modification doesn't return your expected values, please test the following modification.

var files = folder.searchFiles("title contains '" + value + "'"); // or folder.searchFiles(`title contains '"${value}"'`)
while (files.hasNext()) {
  var file = files.next();
  var name = file.getName();
  if (name.includes(value)) {
    Logger.log(name);
  }
}

Note:

• By the way, in your search query, the files in the trash box are also returned. If you don't want to retrieve such files, please add trashed=false to the search query.

Reference:

• searchFiles(params)

  Gets a collection of all files that are children of the current folder and match the given search criteria. The search criteria are detailed in the Google Drive SDK documentation. Note that the Drive service uses v2 of the Drive API and some query fields differ from v3. Review the field differences between v2 and v3.

• Search query terms and operators