How to Extract URLs from HYPERLINK Function in Google Sheets
The HYPERLINK formula of Google Sheets lets you insert hyperlinks into your spreadsheets. The function takes two arguments:
- The full URL of the link
- The description or the anchor text of the link
The URL and anchor text can either be specified as a string or as a cell reference.
If you insert a hyperlink into a cell using the HYPERLINK function, there’s 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 use 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);
};Also see: Replace Text in Google Docs with RegEx
Amit Agarwal
Google Developer Expert, Google Cloud Champion
Amit Agarwal is a Google Developer Expert in Google Workspace and Google Apps Script. He holds an engineering degree in Computer Science (I.I.T.) and is the first professional blogger in India.
Amit has developed several popular Google add-ons including Mail Merge for Gmail and Document Studio. Read more on Lifehacker and YourStory
