These forums have been archived and are now read-only.

The new forums are live and can be found at https://forums.eveonline.com/

EVE Technology Lab

 
  • Topic is locked indefinitely.
 

xpath formula for parsing assets

Author
Calarague
The Waldos
Hofmann's Heros
#1 - 2017-01-22 06:06:20 UTC
Hi Everyone,

I've been playing with a spreadsheet I built for manufacturing that uses the XML api of my assets to look at how much of each material I have in stock so I know how much more I need to purchase in order to fill orders that I'm working on.

Essentially the way I have my sheet set up is my A column has the name of each material, my B column has my api import formula which I fill down to complete the whole column, and my C column has the typeID of each material. So far I've managed to build an XPATH equation that does work for what I want it to do (looks through the API, and then gives me the sum of all the quantity values that match that location and the corresponding typeID from column C)

=importxml("http://api.eveonline.com/char/AssetList.xml.aspx?keyID=XXXXX&vCode=XXXXXXXXX&characterid=XXXXXXXXXXXX&flat=1", concatenate("sum(//row[@locationID=XXXXXXXXXXX and @typeID=",C3,"]/@quantity)"))

The problem that I'm running into is that google sheets seems to have a limit as to how many importXML equations you can have in a single spreadsheet (read somewhere it's 50, but it seems to be somewhat variable to me), so each time I load the sheet a random set of the xml calls don't actually get made. I would like to change the formula so that I can just put it in cell B3 so that it only makes one API call, and then it will parse that API for each of the materials from C3:C134 and place the quantity of each into the corresponding cell in column B. I suspect this will involve using the join command and | but I can't seem to get it to work. If anybody can help me out I'd appreciate it.
Blacksmoke16
Resilience.
#2 - 2017-01-22 16:11:38 UTC
Ok few things.

typeName isn't included in the assetList endpoint so you would have to preimport a list from the SDE or something like that then usevLookup to get the names for each value.

Best bet would be to just load in all of the data using two calls for in each column (typeID, quantity), then use unique() to get a list of unique typeIDs (or supply a manual list if you don't want them all). Then you can use sumIFS() to sum the quantities where they equal that typeID and the given locationID. This way you are doing the math in the sheet and not the xPath.