How to extract hovered links from a cell in Google Sheets?

Question

I have set up this demo spreadsheet.

Can someone please help me extract the URL?

Answer 1

• add this script to your spreadsheet and then use this custom formula: =URL(A1)

  function URL(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
  }

  

Answer 2

• or this indirect (shorter) one with a custom formula: =URL("A1")

  function URL(input) {
  var range = SpreadsheetApp.getActiveSheet().getRange(input);
  var url = /"(.*?)"/.exec(range.getFormulaR1C1())[1];
  return url;
  }