How to make a function to extract the new rich text links in Google Sheets while referencing specific ranges?

Question

Some time ago, Google Sheets changed the adding of links to rich text, and now links cannot be found at the formula anymore. Personally I dislike this change very much because I use a custom function that extracts hyperlinks from cells with the old formula, and with the new change I just cannot find a way of doing this. I am not very good with functions yet, mainly the reason why I wrote my question title as detailed as possible.

What I need to do is to extract hyperlinks from cells using a custom formula, since it will need to be used among many other vanilla formulas. How can I set up a custom function/formula to extract the new hyperlinks based on range?

Here are the sheets where I want to extract links: https://docs.google.com/spreadsheets/d/1JnSKQ7nd4J3NPRH4uSsOCYms-DF16j1pkCAuJeikToo/edit#gid=317867416

I would like to extract links from the games that are being posted, because I need to use those links elsewhere and I'd also like to have them ready to be imported if ever needed. I need to specify a formula in another cell which will extract those links. For example =GETURL(B6) which would extract the new rich text hyperlinks based on a range that I insert for it.

Alternatively, is it possible to configure the document so that it makes links in the old format whenever inserted? This way I could try to workaround the new settings and future inserted links would go into the =HYPERLINK formula instead.

Many thanks!

Agalloch

Does this answer your question? Apps Script: how to get hyperlink from a cell where there is no formula – Unexpected

I don't think Google Sheets has built in functionality to extract URLs from rich text hyperlinks. So instead you can extract the links using JavaScript. – Osmunda

Answer 1

I think this script would come in handy. It gives the possibility to retrieve back the URL from a hyperlink formula.

1. Go to script editor, and create a new project.
2. Save the file.
3. Head up to Run > linkURL to run the script. This will create a new function in Sheets.
4. Let’s say cell A1 has the hyperlink in it. Go to any cell and type =linkURL(A1), and then hit Enter.

---

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
}

Reynold

I get the following error Syntax error: SyntaxError: Invalid regular expression: /=hyperlink("([^"]+)"/: Unterminated group line: 16 file: Code.gs – Caulis