How to Extract Hyperlink URLs in Google Sheets
Google Sheets has two kinds of hyperlink too. One yields to a formula; the other needs Apps Script, because no formula can see it. Here is how to tell which you have.
Google Sheets has the same split as Excel, and the same consequence: the method depends on how the link was made.
| The cell was made by | Address lives in | Use |
| --- | --- | --- |
| =HYPERLINK("…","…") | The formula | FORMULATEXT + REGEXEXTRACT |
| Insert → Link, or pasting a link over text | Rich-text formatting | Apps Script |
How to tell: click the cell and look at the formula bar. =HYPERLINK(...) means the first kind. If the bar shows plain text but the cell is blue and underlined, it is the second.
Case 1: a HYPERLINK formula
FORMULATEXT gives you the formula as a string, and REGEXEXTRACT pulls the first quoted argument out of it:
=IFERROR(REGEXEXTRACT(FORMULATEXT(A2), """([^""]+)"""), "")Google Sheets uses RE2 for REGEXEXTRACT. Inside a Sheets string literal a double quote is written as two double quotes, which is why the pattern looks the way it does.
For a whole column, wrap it in ARRAYFORMULA and MAP. FORMULATEXT does not accept a range directly, so MAP is what applies it cell by cell:
=MAP(A2:A100, LAMBDA(cell,
IF(cell = "", "",
IFERROR(REGEXEXTRACT(FORMULATEXT(cell), """([^""]+)"""), "")
)
))MAP and LAMBDA are named functions in Google Sheets. Blank cells are returned as blank rather than as an error.
Case 2 — an Apps Script that reads inserted links
- 1
Open the script editor
In your spreadsheet: Extensions → Apps Script.
- 2
Replace the contents with the script below
It reads column A, writes the addresses into column B, and handles cells that contain several differently-linked runs of text.
- 3
Run it and grant permission
Press Run. Google asks for permission to edit this spreadsheet the first time — that is the script you just pasted, and you can read exactly what it does.
- 4
Adjust the range
Change
A2:A100to match your data, and the output column if B is taken.
function extractHyperlinks() {
const sheet = SpreadsheetApp.getActiveSheet();
const source = sheet.getRange('A2:A100');
const richTexts = source.getRichTextValues();
const output = richTexts.map(function (row) {
const rich = row[0];
if (!rich) return [''];
// A whole-cell link: one address for the entire value.
const whole = rich.getLinkUrl();
if (whole) return [whole];
// Otherwise the cell may contain several runs, each with its own link.
const urls = rich
.getRuns()
.map(function (run) { return run.getLinkUrl(); })
.filter(function (url) { return Boolean(url); });
// Several links in one cell are joined so nothing is lost.
return [urls.join(', ')];
});
sheet.getRange(2, 2, output.length, 1).setValues(output);
}Uses the documented Range.getRichTextValues(), RichTextValue.getLinkUrl() and RichTextValue.getRuns() methods of the Apps Script Spreadsheet service. getLinkUrl() returns null when a cell has multiple runs with different links, which is why getRuns() is the fallback.
Frequently asked questions
Why does FORMULATEXT return #N/A?
Because that cell contains no formula. It is a case-2 link — inserted rather than written as =HYPERLINK — so the address is in the cell’s rich-text formatting and only Apps Script can read it.
Can I avoid granting the script permission?
The script edits your spreadsheet, so it needs permission to do that. If you would rather not, use the download-as-xlsx route instead — it needs no script and no permissions.
The cell has two links in different words. What happens?
getLinkUrl() returns null for that case, so the script falls back to getRuns() and joins every address it finds with a comma. Nothing is lost.
Does exporting to CSV keep the links?
No. A CSV holds one value per cell and that value is the display text. This is true of both Sheets and Excel, and it is the most common way people lose the addresses without noticing.