How to extract URLs from HYPERLINK function in Google Sheets


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

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


See also: Replace text in Google Docs with RegEx


About Author

Comments are closed.