Google Sheets – Query Cells in External Google Drive File

google sheetsgoogle-apps-scriptgoogle-drive

I have a huge list of names stored in Google Drive as a TXT file (constantly updated externally). Can I put a small list of names in a Google Sheet and see if any of them exist in the big external list?

I thought about doing something like:

        A               B
    1   https://...     =REGEXEXTRACT(A1, "d/(.+)/")
    2   Name            Match
    3   Joe Smith       =if(iserror(query(IMPORT*X*(A$1), concatenate("where lower(A) = lower('", A3, "')"))), "Not found", "Found")
    4   Jane Smith      =...
    5   Foo Bar         =...

(A1 will look like https://drive.google.com/file/d/1234abcd/view)

I've tried different *X* (like IMPORTDATA), but it always returns "Not Found". If I change URL to https://drive.google.com/uc?export=download&id=1234abcd then it returns #N/A even though it does exist.

Please advise how to accomplish this goal and hopefully quickly (without loading the entire huge file in each and every row). BTW, I guess I can turn the TXT file into CSV if I must.

You can also answer with a custom Google Apps Script function like =external_match(a3, b$1), if you think it's better.

Best Answer

IMPORTDATA as well as IMPORTXML, IMPORTHTML and IMPORTFEED require that the file to import is publicly available to do so share your TXT file with anyone with the link.

By the other hand, IMPORTRANGE only works with Google Sheets spreadsheets.

NOTES:

  1. When a complex formula doesn't work, decompose it on its parts in order to find which part is causing the problem and to get a more descriptive error message.

  2. Before to nest an import function as argument of other function, be sure that the imported data has the required data structure (single value or an array of values) and data type.

  3. Custom functions can't access Google Apps Script Services / Advanced Services that requires authorization to run, so they can't do what you are looking for unless the file is publicly available, you could get the file by using the URL fetch service. An alternative is to use a "regular" function that use Google Drive Service or Advanced Service.

Related Topic