How to extract URLs from HYPERLINK function in Google Sheets

0

The Google Sheets HYPERLINK formula allows you to insert hyperlinks into your spreadsheets. The function takes two arguments:

  1. The full URL of the link
  2. The description or anchor text of the link

URL and anchor text can be specified as a string or cell reference.

If you insert a hyperlink into a cell using the HYPERLINK function, there is no direct way to extract the URL from the formula. You may consider writing a complicated regular expression to match and extract the hyperlink in the cell formula or using Apps Script with Google Sheets API.

const extractHyperlinksInSheet = () => {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = SpreadsheetApp.getActiveSheet();

  const hyperlinks = [];

  const spreadsheedId = ss.getId();
  const sheetName = sheet.getName();

  const getRange = (row, col) => {
    const address = sheet.getRange(row + 1, col + 1).getA1Notation();
    return `${sheetName}!${address}`;
  };

  const getHyperlink = (rowIndex, colIndex) => {
    const { sheets } = Sheets.Spreadsheets.get(spreadsheedId, {
      ranges: [getRange(rowIndex, colIndex)],
      fields: 'sheets(data(rowData(values(formattedValue,hyperlink))))',
    });
    const [{ formattedValue, hyperlink }] = sheets[0].data[0].rowData[0].values;
    hyperlinks.push({ rowIndex, colIndex, formattedValue, hyperlink });
  };

  sheet
    .getDataRange()
    .getFormulas()
    .forEach((dataRow, rowIndex) => {
      dataRow.forEach((cellValue, colIndex) => {
        if (/=HYPERLINK/i.test(cellValue)) {
          getHyperlink(rowIndex, colIndex);
        }
      });
    });

  Logger.log(hyperlinks);
};

See also: Replace text in Google Docs with RegEx

Share.

About Author

Comments are closed.