How to Use Hyperlinks in Google Sheets Easily

Hello, Guys welcome back to learn How to Use Hyperlinks in Google Sheets Easily. This tutorial will show you how to simply generate and manage hyperlinks in Google Sheets. External web pages can be accessed by linking a full cell in the sheet or specific text within the cell. Multiple hyperlinks can also be contained in a single cell.

When you type a web page address into a Google Sheet cell, it gets turned into a clickable hyperlink.

For more accessible URLs, you can add anchor text to plain hyperlinks. Hover your cursor over the hyperlink and select Edit. Now, in the Text input box, enter the anchor text and press the green Apply button.

Alternatively, you can utilise Google Sheet’s built-in HYPERLINK function to create web links with (optional) anchor text.

 =HYPERLINK("https://www.labnol.org", "Digital Inspiration")

It is also possible to add numerous hyperlinks within a single Google Sheet cell.

Simply input any content, including URLs in plain text, and the URLs will be converted to hyperlinks when you move the cursor out of the cell.

Bonus Tip: While a cell with several links is chosen, press Alt+Enter and all of the links will open in new tabs at the same time.

You can use the previous method to change and add anchor text to numerous hyperlinks contained in a single cell.

Hover your cursor over a cell link, click the edit icon, and alter the Anchor text. Rep for all of the other links in the same cell.

Read: Learn How to Create Zoom Meetings with Google Script easily

Manage Hyperlinks with Google Scripts

Here are some code snippets to assist you to manage hyperlinks in Google Sheets using Google Script macros.

1. Create links in Google Sheet cells with the Hyperlink formula.

const createHyperLinkWithFormula = () => {
  const link = 'https://www.labnol.org';
  const text = 'Digital Inspiration';
  const value = `=HYPERLINK("${link}", "${text}")`;
  const sheet = SpreadsheetApp.getActiveSheet();
  const range = sheet.getRange('A1');
  range.setValue(value);
};

2. Create links with the RichTextValue builder

const createHyperLinkWithRichTextValue = () => {
  const link = 'https://www.labnol.org';
  const text = 'Digital Inspiration';
  const value = SpreadsheetApp.newRichTextValue()
    .setText(text)
    .setLinkUrl(link)
    .build();
  SpreadsheetApp.getActiveSheet().getRange('A1').setRichTextValue(value);
};

3. Create multiple links in a single cell

const createMultipleHyperLinks = () => {
  const value = SpreadsheetApp.newRichTextValue()
    .setText('Google acquired YouTube in 2006')
    .setLinkUrl(0, 6, 'https://www.google.com')
    .setLinkUrl(16, 23, 'https://www.youtube.com')
    .build();
  SpreadsheetApp.getActiveSheet().getRange('A1').setRichTextValue(value);
};

4. Extract links from a cell that uses the HYPERLINK formula

const extractLinkFromFormula = () => {
  const sheet = SpreadsheetApp.getActiveSheet();
  const range = sheet.getRange('A1');
  const formula = range.getFormula();
  const [, url, , text] =
    formula.match(/=HYPERLINK\("(.+?)"([;,]"(.+?)")?\)/) || [];
  Logger.log({ url, text: text || url });
};

5. Extract multiple links and anchor text from a cell

const extractMultipleLinks = () => {
  const urls = SpreadsheetApp.getActiveSheet()
    .getRange('A1')
    .getRichTextValue()
    .getRuns()
    .map((run) => {
      return {
        url: run.getLinkUrl(),
        text: run.getText(),
      };
    })
    .filter(({ url }) => url);
  Logger.log(urls);
};