Check for regexmatch in multiple ranges in Google Sheets

Question

I have a sheet that auto-populates with a sub-set of articles (each a row with a column representing various information such as title, year of publication, author name, etc...) on the basis of a set of tags (that have their own column in D2:D). There is a master "All Publications" tab and using the filter function I check each cell in the 'Primary Category' column ('All Publications'!B5:B) and if it matches one of the cells in the 'Tags for Tab' column that article is included. Example: I might want all articles with a primary category of "Rot" to be included under a new sheet called 'Rot Articles'. I have this functionality in place already with the following filter:

=FILTER('All Publications'!A5:A, 
 REGEXMATCH(LOWER('All Publications'!B5:B), LOWER(TEXTJOIN("|", TRUE,D3:D))))

D3:D is the column where the tags for the given sheet are kept. I now have two additional columns called 'Secondary Category' ('All Publications'!C5:C) and 'Tertiary Category' ('All Publications'!D5:D) respectively.

I want to be able to include any article that has the keyword 'Rot' in any of the three categories (primary, secondary, tertiary). The query above only checks the primary category. In other words: I want an article to populate my sheet if a match for any of the tags in 'Rot Articles'!D3:D is found in the ranges 'All Publications'!B5:B OR 'All Publications'!C5:C OR 'All Publications'!D5:D. How do I do this? Its important to know that these articles contain hyperlinks and so using the QUERY function is not an option.

Anyone that wants to play with this toy example is free to access the sheet here: https://docs.google.com/spreadsheets/d/1MOxisNd_Nw-_YKHJHCbi1qWvoVh3K8Tu9xxAxee3520/edit?usp=sharing

Answer 1

You can still try a query, you just need to extract the url then reassemble the hyperlink.

Here's a stackexchange answer to extract the url

Add this in the script editor

/** 
 * Returns the URL of a hyperlinked cell, if it's entered with hyperlink command. 
 * Supports ranges
 * @param {A1}  reference Cell reference
 * @customfunction
 */
function linkURL(reference) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var formula = SpreadsheetApp.getActiveRange().getFormula();
  var args = formula.match(/=\w+\((.*)\)/i);
  try {
    var range = sheet.getRange(args[1]);
  }
  catch(e) {
    throw new Error(args[1] + ' is not a valid range');
  }
  var formulas = range.getFormulas();
  var output = [];
  for (var i = 0; i < formulas.length; i++) {
    var row = [];
    for (var j = 0; j < formulas[0].length; j++) {
      var url = formulas[i][j].match(/=hyperlink\("([^"]+)"/i);
      row.push(url ? url[1] : '');
    }
    output.push(row);
  }
  return output
}

Now you just have to add a column with something like =linkURL(range)

In a second time you just have to reconstruct the hyperlink

=HYPERLINK(range,description)