Search code examples
jsonxmlgoogle-sheetsgoogle-sheets-formula

Import API to GoogleSheets

I am trying to run a script to import data into a GoogleSheet, but cannot seem to find an easy way to do this.

The API pull from the website comes out as;

curl -X GET "https://api.evemarketer.com/ec/marketstat?typeid=19720" -H "accept: application/xml"

I'm not sure what to do with this. I have tried ImportHTML as well as other functions, but I believe this requires a script. Could someone help, or point me in the right direction.

Solution

  • In the URL, XML data can be retrieved. So how about these patterns using IMPORTXML()?

    When you use this, at first, please put the URL of https://api.evemarketer.com/ec/marketstat?typeid=19720 to a cell "A1", and put the formula to other cell.

    Pattern 1:

    If you want to retrieve the values of "Max Buy Order", "Average Buy Order" and "Volume" in the order, as a simple sample, how about this?

    ={IMPORTXML(A1,"//buy/max"),IMPORTXML(A1,"//buy/avg"),IMPORTXML(A1,"//buy/volume")}

    Pattern 2:

    If you want to retrieve the values of "Max Buy Order", "Average Buy Order" and "Volume" without in the order (this means the order in XML data. So it's "Volume", "Average Buy Order" and "Max Buy Order".), how about this?

    =IMPORTXML(A1,"//buy/max | //buy/avg | //buy/volume")

    or

    =TRANSPOSE(IMPORTXML(A1,"//buy/max | //buy/avg | //buy/volume"))

    Reference: