How to get/extract the link and text of a Google sheet cell?

Question

Can we get the text and link URL from a cell created in a Google sheet and save the result using either a formula or script into 2 cells: text and URL?

Answer 1

Add this Custom Functions in Google Sheets

/** 
 * 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.getRichTextValues();
  var output = [];
  for (var i = 0; i < formulas.length; i++) {
    var formula = formulas[i]
    var urls = [];
    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() + " " + run[k].getLinkUrl() + "\n";
        }
      }
    }
    output.push(urlsStr);
  }
  return output
}

Answer 2

to get url from hyperlink you need script:

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

and then use formula:

=LINK(A1)

or shorter script:

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

with formula:

=LINK("A1")

---

or without script (if you got hyperlink fx) you can use regex like:

=REGEXEXTRACT(FORMULATEXT(A1), """(.+)"",")

---

to get text from hyperlink use:

=""&A1

or:

=TO_TEXT(A1)