Login

Please fill in your details to login.





extract url from hyperlink in cell

Google apps script creates a new function to extract a URL from a hyperlink in a cell
/** 
 * Returns the URL of a hyperlinked cell, if it's entered with hyperlink command. 
 * Supports ranges
 * @param {A1}  reference Cell reference
 * @param {A2}  delimiter Delimiter to use between cell text and url (default '|')
 * @customfunction
 */
function get_url(reference,delimiter="|") {
  var sheet = SpreadsheetApp.getActiveSheet();
  var formula = SpreadsheetApp.getActiveRange().getFormula();
  var args = formula.match(/=\w+\(([^,]*).*\)/);
  try {
    var range = sheet.getRange(args[1]);
  }
  catch(e) {
    throw new Error(args[1] + ' is not a valid range');
  }

  var formulas = range.getRichTextValues();
  var output = [];
  for (var i = 0; i < formulas.length; i++) {
    var formula = formulas[i]
    var urlsStr = "";
    for (var j = 0; j < formula.length; j++) {
      var run = formula[j].getRuns();
      for (var k = 0; k < run.length; k++) {
        var url = run[k].getLinkUrl();
        if (url != null) {
          urlsStr += run[k].getText() + delimiter + run[k].getLinkUrl();
          if(k < run.length){
            urlsStr + '\n';
          }
        }
      }
    }
    output.push(urlsStr);
  }
  return output
}

Last modified: May 21st, 2023
The Computing Café works best in landscape mode.
Rotate your device.
Dismiss Warning