You may encounter a [[Google Sheets|Google Sheet]] with lots of links embedded as rich text within a cell. To extract these links, you'll need to write a custom function in [[Google Apps Script]].
```javascript
function GETURL(input){
return SpreadsheetApp.getActiveSheet().getRange(input).getRichTextValue().getLinkUrl(0,1);
}
```
`.getLinkUrl(0,1)` ensures you only get the first link in the cell.
Because custom functions receive the value within the cell, not the range object itself, you need to pass the cell as a cell address to the function. To call this function, use the formula
```
=GETURL(CELL("Address",A1))
```
If you expect more than one URL is embedded in the cell, use this
```javascript
function GETURLS(input) {
var richTextValues = SpreadsheetApp.getActiveSheet().getRange(input).getRichTextValues();
var urls = [];
richTextValues.forEach(function (row) {
row.forEach(function (cell) {
var runs = cell.getRuns();
runs.forEach(function (run) {
var url = run.getLinkUrl();
if (url) {
urls.push(url);
}
});
});
});
return urls.length > 0 ? urls.join("; ") : "No URLs found";
}
```