Cant search files with underscore in name with Google Apps Script

Viewed 1359

I am using Google Apps Script to find files by name in my Drive account. I have problems with finding files whose names contain underscores.

For example, I have a file whose name is FB_51.pdf

This code does not retrieve the file

folder.searchFiles('title contains "51"');

This code does not retrieve the file

folder.searchFiles('title contains "_51"');

But this code retrieves the file

folder.searchFiles('title contains "FB_51"');

I'd like to retrieve the file only with the two digits "51".

2 Answers

Unfortunately, I believe what you are perceiving as a glitch, is actually considered "by design" from Google's perspective. Neither the Apps Script doc on searching, nor the page that doc links to mentions this, but I found the answer at the API docs page for search syntax, under a footnote:

The contains operator only performs prefix matching for a name. For example, the name "HelloWorld" would match for name contains 'Hello' but not name contains 'World'.

That seemed pretty conclusive to me, but just to be sure, I tested with your example:

Log

In this case, Google treats the underscore as a normal character, not a delimiter or word boundary, so "FB_51" is treated as one single word, not "FB" and "51", and thus it can only match on an exact word match, or a prefix match (per the warning I put above).

Alternative

Other than forcing your files into a format that will fit the search syntax (e.g. swapping to 51_FB.pdf), or always prefixing your search term with FB_, if files always match that syntax, your options are pretty limited. Your best bet is likely to limit the start of your search to as narrow of a location as possible, for example a specific Drive folder, then get all files, iterate over them, and use Regex to match the filename. Example script:

function findNumberedPdf(folderId, number) {
    var folder = DriveApp.getFolderById(folderId);
    var files = folder.searchFiles("mimeType contains 'pdf'");
    while (files.hasNext()) {
        var file = files.next();
        var regPattern = new RegExp(number);
        if (regPattern.test(file.getName())) {
            return file;
        }
    }
    return false;
}
/**
 * Test:
 *   Logger.log(findNumberedPdf('0CdI2-...', 51).getName());
 *   >> "FB_51.pdf"
 */

Of course, if your files really do always start with FB_, you could also just create a wrapper function to always prepend that string to your searches.


Note - why this is by design:

The reason why this is "by design" and Google seems to care about word boundaries and tokenization is because of how string matching works. Usually, when we search for something, we expect a full word (or similar word) match for each token in our search query. If searches didn't work this way, a search for "51" could pull up files like "fileA-v5251989.jpg" or a search for "cat" would match "multiplication" and "modification".

Google's "by design" has the consequence that if you or anybody place an underscore within a filename you render that file 'hard to search for' for you and any other users (say in GSuite).

Businesses that rely on Google Drive's feature for storage of compliance related documentation and expect Auditors to search for files (perhaps sometimes using filenames) are thus rendered into a 'compliance-unlikely' situation. An employee can accidentally or intentionally break business processes by adding an underscore. app systems that integrate with GDrive APIs and rely on a single, exact match for searches on filenames can be broken/dos attacked by Users simply by adding an underscore to a filename. A whole bunch of problem scenarios caused by Google's persistent unwillingness to update old parts of a behemoth code repository (note: paraphrasing a Google employee who worked on Google Drive platform).

Perhaps this is one reason Governments around the world do not find Google suitable for their Cloud Services needs?

A solution for Auditors is to replace in search strings any underscore with a whitespace or hyphen, then perhaps work out which is the correct match from a list of results.

Related