Querying if cells exist in an external Google Drive file

Question

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.

Answer 1

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.

Answer 2

As I've mentioned, maybe GAS is the only way to go here. But as @Rubén mentioned, they can't be custom.

So my solution is run a function, which I called "checker", via a drawing or a menu. The function called "reset" is just there as a quick way to delete the list of names.

The biggest advantage with this approach is that the external file is called just once! I've even made it display (in C2) the external file's last modified date so the users will know if the match is based on current data or not.

The Spreadsheet structure is:

        A               B               C
    1   ID/Name         https://...     =REGEXEXTRACT(B1, "d/(.+)/") or just filename.txt
    2   Charset         UTF-8
    3
    4   Name            Match
    5   Joe Smith       =...
    6   Jane Smith      =...
    7   Foo Bar         =...

(Cell B1 is either a link like https://drive.google.com/file/d/1234abcd/view or a file name - my function will take the first file with that name)

function reset() {
  var spreadsheet = SpreadsheetApp.getActiveSheet(), firstrow = 4;
  spreadsheet.getRangeList(["a" + firstrow + ":b", "c2"]).clearContent();
}

function checker() {
  var regex = '^{value}$', regex_mod = 'mi', file, found, name = '.', id, value, charset, spreadsheet = SpreadsheetApp.getActiveSheet(), popup = SpreadsheetApp.getUi(), i, firstrow = 4, maxrows;
  spreadsheet.getRange("b" + firstrow + ":b").clear;
  maxrows = spreadsheet.getMaxRows();
  id = spreadsheet.getRange('c1').getValue();
  charset = spreadsheet.getRange('b2').getValue();
  if (id.indexOf(name) == -1)
    file = DriveApp.getFileById(id);
  else
    file = DriveApp.getFilesByName(id).next();
  spreadsheet.getRange("c2").setValue(file.getLastUpdated());
  if (charset == null)
    file = file.getBlob().getDataAsString();
  else
    file = file.getBlob().getDataAsString(charset);
  for (i=firstrow; i<=maxrows; i++) {
    value = spreadsheet.getRange(i, 1).getValue();
    if (value == '')
      break;
    value = new RegExp(regex.replace('{value}', preg_quote(value, '')), regex_mod);
    spreadsheet.getRange(i, 2).setValue(value.test(file));
  }
}

function preg_quote (str, delimiter) { // eslint-disable-line camelcase
  //  discuss at: http://locutus.io/php/preg_quote/
  // original by: booeyOH
  // improved by: Ates Goral (http://magnetiq.com)
  // improved by: Kevin van Zonneveld (http://kvz.io)
  // improved by: Brett Zamir (http://brett-zamir.me)
  // bugfixed by: Onno Marsman (https://twitter.com/onnomarsman)
  //   example 1: preg_quote("$40")
  //   returns 1: '\\$40'
  //   example 2: preg_quote("*RRRING* Hello?")
  //   returns 2: '\\*RRRING\\* Hello\\?'
  //   example 3: preg_quote("\\.+*?[^]$(){}=!<>|:")
  //   returns 3: '\\\\\\.\\+\\*\\?\\[\\^\\]\\$\\(\\)\\{\\}\\=\\!\\<\\>\\|\\:'

  return (str + '')
    .replace(new RegExp('[.\\\\+*?\\[\\^\\]$(){}=!<>|:\\' + (delimiter || '') + '-]', 'g'), '\\$&')
}