Published in: Manage Hyperlinks with Google Scripts

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

Leave a Reply

Your email address will not be published. Required fields are marked *