Use variable's value in DriveApp searchFiles query

Question

I may have silly question but I can't find a solution

I have go through a lot of files in my google drive and inside every file there is a number within 0-10000 range, so I made this:

for(var i = 0; i < 10000; i++)
{
  var test = "IT" + i;
  Logger.log(test);
  var search = DriveApp.getFolderById("ID")
                       .searchFiles('fullText contains test');
  Logger.log(search);
  while (search.hasNext()) {
    var file = search.next();
    if(search.hasNext())
      break;
    Logger.log(file.getName());
  }
  ...

My question is, how can I put a variable like this:

.searchFiles('fullText contains $variable');

so that I can use the value of the variable test, i.e. "IT0", rather than the literal string test?

Tried Your way and searchFiles('fullText contains "IT"' + test) - Didn't work. I am getting error: Invalid argument: q (line 18, file "test") — LukasEko
– LukasEko, Commented Jun 28, 2018 at 11:40

tehhowch · Accepted Answer · 2018-06-28 14:22:36Z

Per the Drive REST API documentation:

The contains operator only performs matching on entire string tokens for fullText. For example, if the full text of a doc contains the string HelloWorld only the query fullText contains 'HelloWorld' returns a result. Queries such as fullText contains 'Hello' do not return results in this scenario.

The contains operator matches on an exact alphanumeric phrase if it is surrounded by double quotes. For example, if the fullText of a doc contains the string Hello there world, then the query fullText contains '"Hello there"' will return a result, but the query fullText contains '"Hello world"' will not. Furthermore, since the search is alphanumeric, if the fullText of a doc contains the string Hello_world, then the query fullText contains '"Hello world"' will return a result.

In comments you mention you tried searchFiles('fullText contains "IT"' + test) which does not conform to the required search clause syntax - it evaluates to something like fullText contains "IT"0 (or fullText contains "IT"IT0, depending on if you changed your other code parts) instead of fullText contains "IT0"

A good practice when debugging errors in variable-based queries is to first perform the construction (so you can accurately log the query) and then actually do the query:

var myQuery = "fullText contains \"" + myVar + "\"";
// var mySingleQuotedQuery = "fullText contains '" + myVar + "'";
try {
  /* use myQuery */
}
catch (err) {
  err.message = "Error using 'myQuery': " + err.message;
  // Log debug info to StackDriver:
  console.error({message: err.message, error: err, query: myQuery, variable: myVar});
  throw err;
}

Oh my God, I was on this page but didn't go that below. Thank You very much, now it is wokring.

stepvda · Accepted Answer · 2018-06-28 12:04:41Z

0

You have to use an escape sequence when you want to add double quotes in your string. So you're string would be: 'fullText contains \"IT\"'

answered Jun 28, 2018 at 12:04

stepvda

797 bronze badges

2 Comments

LukasEko Over a year ago

I kinda thought was loking for this but unfortunately it is not working. An orginal formula looks like this .searchFiles('fullText contains "IT2595"');

stepvda Over a year ago

so did you try adding a backslash before your double quotes?

Collectives™ on Stack Overflow

Use variable's value in DriveApp searchFiles query

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related