How to extract hyperlink inside Cells

[Vo Tu Duc]

Hi everyone,

I would like to use GSheet formula or Apps script to extract hyperlink inside cells.

The sample is File here . 

I've tried the script as below. But It doesn't work.

Could anyone can give me an advices?

Enter code here.../**

 * 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
}

Thank you in advance,

With regards,

Duc

[Tanaike]

I think that this thread of Stackoverflow might be useful for your situation.

Apps Script: How to get Hyperlink from a cell where there is no formula Hyperlink

[Clark Lind]

Tanaike's answer should work for you. I was curious to see how to do it in basic Apps Script without advance services, and the below is working for me.
It only works for the current active cell, so click the cell where you want the hyperlink from to make it active.

function getHyperlink() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getActiveCell();
  var formula = range.getFormula();

  Logger.log(formula.slice(formula.indexOf("\(")+1,formula.indexOf("\,")) );
}

Hyperlink formula:   =HYPERLINK(" [url]  ", " [display text] ")

Because the Hyperlink Formula has two unique characters we can look for [ open parentheses '(' and comma ',' ] , just grab the part in between.

Hope that is helpful.