Get href-hyperlink from cell-CodePudding

I have hyperlinks in a cell like the following:

I want to extract from the formatted links the real href-link. Like from google the real link www.google.com

I tried using the following function:

function getCellLink(address) {

  if(!(address && typeof(address) == 'string'))
    throw new Error('The 1st param must be a cell address as a string.');

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var cell = ss.getRange(address);
  var url = cell.getRichTextValue().getLinkUrl()

  if (!url) 
    throw new Error('The cell in '   address   ' does not contain a link.');
  
  return url
}

However, I still get an error.

Here is a link to my example sheet:

Link to example spreadsheet

Any suggestions what I am doing wrong?

I appreciate your replies!

CodePudding user response：

The custom function you quote expects range references as text strings. You can make the formulas work by inserting quotes, like this:

=getCellLink("A2")

To get a larger number of links in one go, use the RichTextLinks() custom function, like this:

=RichTextLinks("A2:A4", A2:A4)

Here's the code:

/**
* Retrieves addresses contained in rich text links.
*
* When rangeA1 is one column wide, returns each link separately, and the number of
* columns in the result range is determined by the max number of links per cell.
*
* When rangeA1 spans multiple columns, returns all links found in a cell as a
* comma-separated list.
*
* @param {"A2:A42"} rangeA1 A text string that specifies the cell range where to retrieve links.
* @param {A2:A42} dynamic_reference Optional. The same range as a normal range reference, to ensure that the results update dynamically.
* @return {String[][]} The addresses contained in rich text links.
* @customfunction
*/
function RichTextLinks(rangeA1, dynamic_reference) {
  // version 1.1, written by --Hyde, 22 November 2021
  if (typeof rangeA1 !== 'string') {
    throw new Error('RichTextLinks expected rangeA1 to be a text string like "A2:A42" but got '   Object.prototype.toString.call(rangeA1)   ' instead.');
  }
  const range = SpreadsheetApp.getActiveSheet().getRange(rangeA1);
  const richTextLinks = range.getRichTextValues().map(row => row.map(value =>
    value.getRuns().map(link => link.getLinkUrl()).filter(link => link)
  ));
  if (range.getWidth() === 1) {
    return richTextLinks.map(row => row[0]);
  }
  return richTextLinks.map(row => row.map(links => links.join(', ')));
}