1

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.

0

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.

  • What if it's not public but belongs to the same account? By "custom" I just meant adding it to the built-in Google Spreadsheet functions. How can you use Google Drive Service or Advanced Service to query a file from the same account? – LWC May 12 '18 at 18:22
  • @lwc that are too many things to talk about on comments. Lets talk about the first one: If the file isn't public the built-in funcions can't access them no matter who owns that file – Rubén May 12 '18 at 19:18
0

As I've mentioned , maybe GAS is the only way to go here. But as @Rubén mentioned, they can't be custom. So here's my solution. Run function checker via a drawing or a menu. Function reset is just there for 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-16
    3   Name            Match
    4   Joe Smith       =...
    5   Jane Smith      =...
    6   Foo Bar         =...

(B1 will look like https://drive.google.com/file/d/1234abcd/view - but it's not needed if you decide to just user a file name - my function will take the first file with this 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'), '\\$&')
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.