How to use hyperlinks in Google Sheets

0
Posted in: Google Apps ScriptGoogle Sheets

This guide explains how to easily create and manage hyperlinks in Google Sheets. An entire cell on the sheet, or specific text inside the cell, can be linked to external web pages. A cell can also contain multiple hyperlinks.

If you type a web page address into a Google sheet cell, it automatically converts to a clickable hyperlink.

You can add anchor text to simple hyperlinks for more accessible URLs. Hover your mouse over the hyperlink and click the Edit icon. Now add the anchor text in the text entry box and click the green Apply button.

Alternatively, you can use the HYPERLINK function in Google Sheet to create web links with anchor text (optional).

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

Add anchor text to the hyperlink

It is also possible to include multiple hyperlinks in a single cell of the Google sheet.

Just type any text, include the URLs in plain text, and when you move the cursor out of the cell, the URLs are converted to hyperlinks.

Bonus tip: When a cell with multiple links is selected, press Alt+Enter and all links open at the same time in new tabs.

Multiple hyperlinks in Google Sheet Cell

You can use the previous technique to edit multiple hyperlinks contained in a single cell and add anchor text.

Hover your mouse over a link in the cell, click the edit icon, and edit the anchor text. Repeat this for all the other links in the same cell.

Format multiple URLs

See also Google Drive secret URLs.

Here are some snippets that will help you manage your hyperlinks in Google Sheets using Google Script macros.

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);
};
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);
};
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);
};
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 });
};
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);
};


Source link

Share.

Comments are closed.