I’ve run into an annoying problem with Google Spreadsheets recently: I’m using Fuzzworks’ scripts to inject market data into a sheet, that reads typeids from another sheet. So far, so good.
Now I have my typeids arranged as a list by groups (ores, PI, advanced materials, etc…), where the numbers aren’t in increasing or decreasing order. I had the market data being injected in the same order as my typeids so far, until Google decided that the data would put the typeids in growing order. Now they don’t match the typeids order on the prices sheet, and they don’t read in return the right vallue from the market data sheet.
Long story short: is there a way to restore the typeids to be listed in the correct order? I’ve been looking everywhere but couldn’t find anything relevant. Many, many thanks for your help.
The list of ids I want in a separate sheet. Typically in the sheet “price”, A2 is =UNIQUE(sort({result!B2:B18}))
for each group of 200? ids (can’t remember the actual max value) I make a concat with a separating coma => 42,43,44,45,70,77,78 (…). in the first row of the group. typically D2 is =CONCATENATE(“https://api.evemarketer.com/ec/marketstat?typeid=",JOIN(",",filter(A2:A201, not(isblank(A2:A201)))),”®ionlimit=10000002&"&$E$1) . E1 is an arbitrary value that I change to force the update
Then I make the call to evemarketer to get the BO /SO . Typically B1 is /buy/max and B2 is =importxml($D2,"//type"&B$1)
then wherever I want the price, I make a lookup . Typically in another sheet, B2 is the id of the item, then C2 is =value(VLOOKUP($B2,prices!$A$2:$C$100,2)) for BO. I replace last param ‘,2’ by ‘,3’ to use the column three, that is the SO for D2 to have SO value.
This method has the benefit of making the least possible calls. You sill need to update it when you have more than 200 IDs to fetch (once), then more than 400 (once more), etc.