Search code examples
google-sheetsxml-parsinggoogle-sheets-formula

Use ImportXML to import data from an API

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")
Solution

  • 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.