EVE Technology Lab

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.