Despite the fact that OP appears to have already selected a solution, this is for those who are having trouble getting the "GETLINK()" script to work when:
- The GETLINK() custom google apps script function returns null despite knowing that a cell contains hyperlinks from richtext.
I followed the instructions to create a custom "GETLINK()" function from Tyler Robertson's web post, but it always returned NULL.
His original solution and those posted here didn't work for my case because my cell's rich text had multiple formats in it resulting in a portion of the text (what google documentation calls a "run") having no hyperlink and another run actually containing a hyperlink.
So, the original solution by Tyler Robertson would always return null even though a hyperlink existed in the richtext.
After digging through googles documents for too long, I came to this solution:
function GETLINK(input){
//input = "'Sheet1'!$A$63"
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange(input);
Logger.log(range.getRichTextValue())
Logger.log(input + " /n" + range.getA1Notation());
var rtRuns = range.getRichTextValue().getRuns();
var url = "";
for (let i = 0; i < rtRuns.length; i++){
if(rtRuns[i].getLinkUrl()!==null){
url=rtRuns[i].getLinkUrl();
break;
}
};
Logger.log(rtRuns + " / " + url);
return url;
}
function sheetName() {
return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
}
And here is an example of the function in use:
getlink( "'" & sheetname() & "'!" & cell("address", offset($A62,1,0) ))
Which evaluates to this:
getlink('Sheet1'!A63)
Finally, here is a screenshot of it in use for those who want to see:
![enter image description here]()
