extract url from hyperlink in cell
Google apps script creates a new function to extract a URL from a hyperlink in a cell
/**
* Returns the URL of a hyperlinked cell, if it's entered with hyperlink command.
* Supports ranges
* @param {A1} reference Cell reference
* @param {A2} delimiter Delimiter to use between cell text and url (default '|')
* @customfunction
*/
function get_url(reference,delimiter="|") {
var sheet = SpreadsheetApp.getActiveSheet();
var formula = SpreadsheetApp.getActiveRange().getFormula();
var args = formula.match(/=\w+\(([^,]*).*\)/);
try {
var range = sheet.getRange(args[1]);
}
catch(e) {
throw new Error(args[1] + ' is not a valid range');
}
var formulas = range.getRichTextValues();
var output = [];
for (var i = 0; i < formulas.length; i++) {
var formula = formulas[i]
var urlsStr = "";
for (var j = 0; j < formula.length; j++) {
var run = formula[j].getRuns();
for (var k = 0; k < run.length; k++) {
var url = run[k].getLinkUrl();
if (url != null) {
urlsStr += run[k].getText() + delimiter + run[k].getLinkUrl();
if(k < run.length){
urlsStr + '\n';
}
}
}
}
output.push(urlsStr);
}
return output
}
Last modified: May 21st, 2023