How can I convert links in Google Sheets cell data to HTML anchor tags?

Question

I'm trying to convert something like this:

I have some hyperlinked text.

Into this:

I have some <a href="https://www.google.com">hyperlinked text</a>.

---

However, it seems as though links created in Google Sheets using this method are considered part of the text's formatting, so I haven't been able to convert one to the other.

It's worth noting that I'm not using the HYPERLINK function here, just the "Insert Link (Ctrl+K)" method.

Answer 1

It's weird if the google sheets api doesn't provide methods to capture links. What you can do (although it's a bit hacky and I'd avoid it if I could) is use some regex like

^(http(s)?:\/\/.)?[-a-zA-Z0-9@:%._\+~#=]{2,256}\.[a-z]{2,6}\b([-a-zA-Z0-9@:%_\+.~#?&//=]*)$

to capture the links in your text and wrap them in a link tag.

How are you getting this data? Are you using an API, or do you want to do this directly in sheets?

If you're working inside sheets, there is macros support in javascript. If that's the case simply write a macro to loop through your data and convert hyperlinks to tags. Something like

/**
* Converts rich text links to HTML links.
*
* @param {"A2:A42"} rangeA1 A text string that specifies the cell range where to convert rich text links to HTML 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 text with rich text links converted to HTML links.
* @customfunction
*/
function RichTextLinksToHtmlLinks(rangeA1, dynamic_reference) {
  // version 1.0, written by --Hyde, 30 March 2022
  //  - initial version, based on RichTextLinks 1.1
  //  - license https://www.gnu.org/licenses/gpl-3.0.html
  //  - see https://support.google.com/docs/thread/157878605
  if (typeof rangeA1 !== 'string') {
    throw new Error('RichTextLinksToHtmlLinks expected rangeA1 to be a text string like "A2:A42" but got ' + Object.prototype.toString.call(rangeA1) + ' instead.');
  }
  const _escapeHTML = (text) => text.replace(/&/g, '&amp;').replace(/</g, '&lt;').replace(/>/g, '&gt;');
  const range = SpreadsheetApp.getActiveSheet().getRange(rangeA1);
  const htmlArray = range.getRichTextValues()
    .map(row => row
      .map(value => value.getRuns()
        .map(run => {
          const link = run.getLinkUrl();
          const text = _escapeHTML(run.getText());
          return link ? `<a href="${link}" target="_blank">${text}</a>` : text;
        })
        .join('')
      )
    );
  return htmlArray.map(row => row.map(html => `<p>${html}</p>`));
}

which I got from here