I suppose this is a common question but I can’t find answers that are understandable by the complete rookie that I am, or not outdated.
My goal is to do some calculations that are including the Jita prices of a few items without having to update every price manually, which is long and tedious.
So I would like to pull the prices of the given items into a calc sheet automatically but I don’t know how to do so. I’ve read that I could use the ESI or Fuzzwork API but this is arcane to me so if someone could explain me slowly how to proceed it would be very appreciated.
To use ESI to achieve your goal is complicated as you cannot request a single typeid but only the entire market for the Region/System/Station, also to the fact that the return type is in JSON format which you would have to convert to other format to extract your desired information or somehow import it directly into your spreadsheet and use it’s functionality to extract the information.
Using Fuzzwork API or EVEmarketer.com API as I’m used to is way easier, while it may not be 100% accurate it’s well within the margin of error that you could expect.
To get the price for an XML-API you would use the function IMPORTXML(url, “xml-path”), so in case of the api I use it will look like this:
To use this you’d need to know the typeid of the items you’re pulling prices on, or if you want to automate you’d need to have a database sheet with all typeids and the corresponding names and then do a VLOOKUP() to get typeid by simply typing in the item’s name, it’s recommended to do datavalidation for your input cells and have it checked against the names in the database.
/**
* Query's Fuzz market API for the given types
* @param {range} A vertical range of type_ids.
* @return maxBuy and minSell for each type_id
* @customfunction
*/
function fuzzApiPriceData(type_ids) {
if (!type_ids) throw 'type_ids is required';
var type_ids = Array.isArray(type_ids) ? type_ids.map(function(id) { return id[0]; }) : [type_ids];
var fuzz_price_data = JSON.parse(UrlFetchApp.fetch("https://market.fuzzwork.co.uk/aggregates/?station=60003760&types=" + type_ids.join(',')));
return [['minSell', 'maxBuy']].concat(type_ids.map(function(type_id) { return [parseFloat(fuzz_price_data[type_id]['sell']['min']), parseFloat(fuzz_price_data[type_id]['buy']['max'])]; }));
}
Then you can use it by =fuzzApiPriceData(A1:A100). Where A1:A100 is the type_ids you want prices for.
Then as @ISD_Sakimura said, can use vlookup to get prices where you need them.
Thank you very much ! I search a bit and managed to figure out what you’re talking about, thanks !
I found a type_ids database and manage to fit it in the sheet, VLOOKUP didn’t work but it worked with INDEX(MATCH()) so in the end it’s working, and thanks to Blacksmoke16 scripts (that is total arcane to me I must say) I can pull the prices of the items that I want.
However, I still have some questions
First, the =IMPORTXML isn’t working, it returns an error message and I can’t figure out why.
Second, are these prices from Jita ? They seem to be close but don’t exactly match, is this because of the API refresh time ?
If you’re using mine you dont really have a reason to use the =IMPORTXML. Yes they are from jita, but may be slightly out of date as they will only update every 30min.
Yes, it works like a charm but I’m wondering why =IMPORTXML isn’t working aswell, after all I may need it in the future. Google sheet underlines the // before sell in red, telling that it can’t read this command in this context, so I suppose this is the problem ?
About your script it looks amazing, I’ve been staying a bit on it trying to understand what it does, I guess the first line returns an error message in case of missing typie_ids, the 3rd line fetch the data from the URL and the 4th one pick what I want among all the data and displays it. However I don’t know what the 2nd line is doing. Which language is this ? Maybe I could learn the basics to modify the script to adapt it to my needs.
Actually that’s intresting, EVE taught me English and now again I’m learning a thing or two ^^
Did you include the quotation symbols in the function’s parentheses? as in “//sell/min” and not just //sell/min
It’s making an Array of type_ids from a range you specify; example A2:A10 will become [A2,A3,A4,A5,A6,A7,A8,A9,A10].
then line 3 combines with the url and each value in the array separated with a “,” then the API will return price data for those type_ids to be worked on further…
The second line determines if you have it a single value or a range of values. If its a range of values, it assumes it is a vertical range, and parses out the ids from that range. Otherwise if its not a range, it just adds that single value to an array. All of this is so that I can call .join on the array.
