Import single value from a webpage/JSON into Google spreadsheet

Question

There is a web page: https://steexp.com/account/GDDHGJNRGR75ZQOXRD3FVGBTXS23JOIYYZX7ZNED7P4EQSNRR57MA54U

I'm trying to import the XLM Balance value (currently 4.9999897) into my Google spreadsheet . At this stage I attempted to use the following formula, but it returns error "Imported content is empty".

=IMPORTHTML("https://steexp.com/account/GDDHGJNRGR75ZQOXRD3FVGBTXS23JOIYYZX7ZNED7P4EQSNRR57MA54U", "table", 1)

At the same time, running the following code in Chrome's DevTools/Console gives the required table index "1":

var i = 1; [].forEach.call(document.getElementsByTagName("table"), function(x) { console.log(i++, x); }) 

I also tried the IMPORTXML, which does the trick in my other cases, with no success:

=IMPORTXML("https://steexp.com/account/GDDHGJNRGR75ZQOXRD3FVGBTXS23JOIYYZX7ZNED7P4EQSNRR57MA54U", "//span[@class='break']")

Alternatively, there is a JSON page containing the same required information: https://horizon.stellar.org/accounts/GDDHGJNRGR75ZQOXRD3FVGBTXS23JOIYYZX7ZNED7P4EQSNRR57MA54U

I tried to use the following custom function to extract the balance value from it, just as fruitless:

function GetXLMBalance(xlmAddress) {
    var response = UrlFetchApp.fetch('https://horizon.stellar.org/accounts/' + xlmAddress);
    var json = response.getContentText();
    var data = JSON.parse(json);
    return data.balance;
}

The script above copes fine if I need to pull, say, "sequence" value, but is of no help when it comes to "balance" since it returns blank value. The problem here is also that the page contains two "balance" values, but I need the second one (which is followed by "asset_type": "native").

Would be grateful for any suggestions on how to import the mentioned XLM balance value into a Google spreadsheet.

Answer 1

Please try:

=--regexextract(index(IMPORTdata("https://horizon.stellar.org/accounts/GDDHGJNRGR75ZQOXRD3FVGBTXS23JOIYYZX7ZNED7P4EQSNRR57MA54U"),58,1),"\d*\.\d*")

Pull the relevant JSON field and then extract the part required and convert it to a Number.