Say I have =HYPERLINK("http://google.com/abc","Google") in A1 in my spreadsheet and I would like to create a custom Google Apps Script (eg. =GetURL(A1)) to return the url portion of this field. What would I have to do to get that working?
Whenever I create a custom script like so
function GetURL(input) {
return input
}
All I get is the description of the link, in this case "Google".
Is there any way of actually getting this working?
It's an old question, but I had the issue right now.
Turns out that when you add a hyperlink to text in a cell with the spreadsheet web-application, the text gets a rich text value. (also see here of how to set a richt text value by Apps Script).
Therefore range.getFormula(); would get nothing, as there was no formula in the cell.
You can extract the link that is bound to the rich text with:
var value = range.getRichTextValue().getLinkUrl()
Doc for getRichTextValue and getLinkUrl
So the full function function (as Tom Woodward wrote) would look like:
getUrl(input) {
var range = SpreadsheetApp.getActiveSpreadsheet().getRange(input);
var value = range.getRichTextValue().getLinkUrl();
return value;
}
function getUrl(input) {
var range = SpreadsheetApp.getActiveSpreadsheet().getRange(input);
var value = range.getFormula();
return value;
}
This gets the raw formula =HYPERLINK("http://google.com/abc","Google" you can then regex or substring the URL out.
I did have to put the ss reference in like =getUrl("A1") with A1 in quotes to make it work.
A) If the value of the link is text then you can use range.getRichTextValue().getLinkUrl() (or the batched version getRichTextValues()). But beware that getRichTextValue() returns "null if the cell value is not text".
B) Or if the formula is static you may be able to use range.getFormula() and use text manipulation to extract the URL from there.
C) Otherwise (e.g. if the value is a number/date/duration), it seems the only way is to enable the Advanced Sheets service*.
Then you'll be able to use the following:
/** Returns (string | null)[][] of hyperlinks for this range. */
function getHyperlinks(range) {
// Use the Advanced Sheets service to fetch the hyperlinks.
const result = Sheets.Spreadsheets.get(
range.getSheet().getParent().getId(),
{
ranges: range.getSheet().getName() + "!" + range.getA1Notation(),
fields: "sheets.data.rowData.values.hyperlink",
},
);
// Convert `result` to a 2D array the same size as `range`.
const rowIndices = [...Array(range.getHeight()).keys()];
const colIndices = [...Array(range.getWidth()).keys()];
return rowIndices.map((r) =>
colIndices.map((c) =>
// r and c are 0-indexed row and col index relative to range.
result.sheets[0].data[0].rowData?.[r]?.values?.[c]?.hyperlink ?? null,
),
);
}
*If you're running this code from a simple trigger, you'll have to upgrade to an installable trigger to use the Advanced Sheets service.
