6

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!

Wind
  • 61
  • 3
  • Does this answer your question? [Apps Script: how to get hyperlink from a cell where there is no formula](https://stackoverflow.com/questions/53863752/apps-script-how-to-get-hyperlink-from-a-cell-where-there-is-no-formula) – Rafa Guillermo Jun 15 '20 at 08:53
  • 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](https://webapps.stackexchange.com/a/124343). – Brandon Nov 20 '20 at 20:54

1 Answers1

0

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
}
christianrana
  • 1
  • I get the following error `Syntax error: SyntaxError: Invalid regular expression: /=hyperlink("([^"]+)"/: Unterminated group line: 16 file: Code.gs` – zzzzzzz Jan 27 '23 at 18:12