How to extract URL from link (link feature, not HYPERLINK formula) in Google Sheets?

user_1732483 0
Viewed 3208

Google Sheets allows to specify (hyper)links in two ways:

There are a lot of solutions around the web, and StackOverflow, for extracting URL from the first option - the HYPERLINK formula, but I haven't found any way how to extract it from the second option.

screenshot

Example Sheet

google-sheets
3 Answers

How to extract with Apps Script a link URL inserted with Insert » Insert Link

  • Apps Script has a class https://developers.google.com/apps-script/reference/spreadsheet/rich-text-value that allows you to retrieve not only the plain text contained in a cell, but also its properties, such as the link URL.
  • To access this property use the method getRichTextValue() combined with getLinkUrl()

Sample to retrieve the link URL in a custom function:

function getLink(range){
  var link = SpreadsheetApp.getActive().getActiveSheet().getRange(range).getRichTextValue().getLinkUrl();
  return link;
}

After writing and saving this code, you simply need to call it from any cell, giving it the reference of the cell with the link URL as parameter.

Important:

Since it is the reference and not the value of the cell that should be passed to the function, you need to put the A1 notation in quotes.

Sample:

=getLink("A1")

enter image description here

user_115997890

We can improve ziganotschka's solution to avoid the need for quotation marks.

Enter this in B1 (assuming text with the embedded link is in A1):

=getLink(ADDRESS(row(),column(A1)))

This way it can be dragged down to quickly get data for an entire column.

user_111965840

If want to use like normal formula without quotation marks,

function GetURL(input) {
  var formula = SpreadsheetApp.getActiveRange().getFormula();
  var rx = /=geturl\((.*)\)/gi;
  var rng_txt = rx.exec(formula)[1]
  var rng = SpreadsheetApp.getActiveSheet().getRange(rng_txt);
  return rng.getRichTextValue().getLinkUrl()
}
user_54523650
Related