getValues() gives you the visible text of a hyperlink, not the URL. getCellLinks(range) reads rich text (getRichTextValues, getRuns, getLinkUrl) and returns every real URL in the range, including cells that have more than one link.
When to use
- Exporting or following the URLs behind "Open ticket" cells
- Auditing a column where people pasted mixed text and links
- Any script that needs the href, not the label
How to use this snippet
/**
* Collect hyperlink URLs from a range, including multiple links in one cell.
*
* @param {GoogleAppsScript.Spreadsheet.Range} range
* @return {{row: number, column: number, text: string, urls: string[]}[]}
*/
function getCellLinks(range) {
var rich = range.getRichTextValues();
var r0 = range.getRow();
var c0 = range.getColumn();
var out = [];
for (var i = 0; i < rich.length; i++) {
for (var j = 0; j < rich[i].length; j++) {
var rt = rich[i][j];
if (!rt) continue;
var urls = [];
var seen = {};
function add(url) {
if (url && !seen[url]) { seen[url] = true; urls.push(url); }
}
if (rt.getLinkUrl) add(rt.getLinkUrl());
var runs = rt.getRuns ? rt.getRuns() : [];
for (var k = 0; k < runs.length; k++) add(runs[k].getLinkUrl());
if (urls.length) {
out.push({
row: r0 + i,
column: c0 + j,
text: rt.getText ? rt.getText() : '',
urls: urls
});
}
}
}
return out;
}
Example
function logTicketUrls() {
var sheet = SpreadsheetApp.getActive().getSheetByName('Tickets');
var col = sheet.getRange(2, 3, sheet.getLastRow() - 1, 1); // column C
getCellLinks(col).forEach(function (cell) {
Logger.log('R' + cell.row + ': ' + cell.urls.join(', '));
});
}
Notes
- Cells with no links are omitted from the result.
- Multiple runs that share the same URL are listed once per cell.
- This reads rich-text links. A formula like
=HYPERLINK(...)usually shows up the same way; a URL that is only plain text (not linked) will not.
Happy Coding!
