How to fetch the Hyperlink from Google Sheets

Question

I can successfully fetch the data from a Google sheet as a JSON source, with something like the following (just a part of the code):

>     var Sheet = 'https://spreadsheets.google.com/feeds/list/SheetKey/od6/public/values?alt=json';
>     
>     function callback(data){
>         var cells = data.feed.entry;
>     .
>     .
>     .

So when I get the entries this way, I cannot find a way to fetch the "Hyperlink" URL that was added in the Google Sheet, I know I can write it as a new column and fetch it that way, but I am looking for a way to read the Hyperlink that is added to the cell itself, please see the following screenshot, it shows what I am referring to:

So I can fetch "some text", but not the hyperlink "http://www.hyperlink.com", anybody can help?

Answer 1

You can grab the URL like so:

function GETURL(input) {
    var range = SpreadsheetApp.getActiveSheet().getRange(input);
    var url = /"(.*?)"/.exec(range.getFormulaR1C1())[1];
    return url;
}

Call this like =GETURL("G3")

An example is at https://docs.google.com/spreadsheets/d/1L3ATSjSUBCDvBrFg3CYw5uZbxWkqiJwTuqh1ObFeSVU/edit#gid=0

Answer 2

You need to set the valueRenderOption parameter in your request. More details about this are here: https://developers.google.com/sheets/api/reference/rest/v4/ValueRenderOption.

To get the HYPERLINK, you need to set the value as FORMULA.

More details about the spreadsheets API is here: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/get.