Google Spreadsheets ImportXML

4
votes

I'm trying to work out how to use ImportXML properly within Google Spreadsheet. I'm trying to obtain the value of the key "last" from within this URL:

https://www.bitstamp.net/api/ticker/

All I have for now is, but this reports an error saying "please make sure that the URL points to a valid XML or HTML":

=ImportXML("https://www.bitstamp.net/api/ticker/","//@last")
2
google-sheetsxml-parsinggoogle-sheets-formula
I've never used ImportXML() until today so I might not be much help but it looks to be because of the way the page is being translated. As you say the error says doesn't see valid XML or HTML. This works fine -> =ImportXML("https://www.bitstamp.net/","//p") so doesn't look to be any issues with your domain. How is the ticker page created? - dev
I have no idea, it seems that Google Spreadsheets can't interpret the HTML like a web browser can. - Dan

2 Answers

4
votes

Ok looking into this you can't use =ImportXML to import JSON data, this is purely designed to use for XML and HTTP, which makes sense with the error it was throwing up.

From what I can see Google doesn't yet provide a =ImportJSON method, but I have found a script here which does then include this exact function.

I have created an example on this spreadsheet here for you.

So it basically just used this function instead ...

=ImportJSON("https://www.bitstamp.net/api/ticker/", "/")

You can then probably play about with the path to find the exact bit you're after.

3
votes

You can actually just use importdata - and it parses out the fields automatically for you. 

=IMPORTDATA("https://www.bitstamp.net/api/ticker/")