Skip to content

Instantly share code, notes, and snippets.

@haggen
Last active August 29, 2015 14:11
Show Gist options
  • Save haggen/461a454b200efe89f3e6 to your computer and use it in GitHub Desktop.
Save haggen/461a454b200efe89f3e6 to your computer and use it in GitHub Desktop.
EVE Spreadsheet Extension

EVE Spreadsheet Extension

Instructions

  1. Open your Spreadsheet, on the menu choose Tools, then Script editor
  2. When it prompt you to create a script, choose to create for Spreadsheet
  3. Replace everything in the editor with the source of script below
  4. Save it, naming it with whatever suits your fancy
  5. Done, go back to your spreadsheet and use the formula as described below:
=EVEMARKET("max", "buy", "any", "Massive Scordite", "Sinq Laison");

The first argument is the value you're looking for, it accepts min for minimum price, max for maximum price, or avg for average price, the next argument is the type of order, it accepts buy, sell, or all for both, the following argument is the minimum amount required for the order to be considered in the calculations, it accepts any for any[1] amount, the following argument is the exact name of the you item you're looking for, e.g. "Veldspar", "Retriever", "1MN Microwarpdrive I", etc. The final argument is optional, it's the exact name of the region or solar system you're interested in, e.g. "Sinq Laison", "The Forge", "Jita", "Trossere", etc. If it's not provided the formula returns the universal average.

Beware that the script depends on two external services, meaning if any of them are unresponsive, the formula won't work. Also the market data retrieved is cached for 1 minute.

[1] It's not really any amount, there's already a fixed minimum requirement in place for each type of item.

function _fetch(type, name) {
var uri, cache, response, data;
uri = 'https://eve-spreadsheet-extension-api.herokuapp.com/' + type + '.json?name=' + name;
cache = CacheService.getUserCache();
Logger.log(uri);
// cache.remove(uri);
response = cache.get(uri);
if(!response) {
response = UrlFetchApp.fetch(uri).getContentText();
// Cache it for a month
cache.put(uri, response, 60 * 60 * 24 * 30);
// Avoid "service invoked too many times"
Utilities.sleep(1000);
}
Logger.log(response);
data = JSON.parse(response)[0];
return data && 'id' in data ? data.id : 0;
}
/**
* Fetches information about item prices in Eve, optionally, in a specific region or solar system.
*
* @param {"max"} value The value you're looking for, accepts "min" for minimum price, "max" for maximum price, or "avg" for average price.
* @param {"buy"} order The type of the order, accepts "buy", "sell", or "all" for both types.
* @param {2000} amount Minimum amount of the item being bought or sold. Accepts "any" for any amount.
* @param {"Veldspar"} item Exact name of the item.
* @param {"Jita"} locationName Optional. Exact name of the region or solar system.
* @return Average price of the item if it's found, 0 otherwise.
* @customfunction
*/
function EVEMARKET(value, order, amount, item, location) {
var uri, cache, response, locationName, locationID;
item = _fetch("types", item);
uri = 'http://api.eve-central.com/api/marketstat/json?typeid=' + item;
if(location) {
locationID = _fetch("regions", location);
if(locationID) {
uri += '&regionlimit=' + locationID;
} else {
locationID = _fetch("systems", location);
uri += '&usesystem=' + locationID;
}
}
if(typeof amount == "number") {
uri += '&minQ=' + amount;
}
cache = CacheService.getUserCache();
Logger.log(uri);
cache.remove(uri);
response = cache.get(uri);
if(!response) {
response = UrlFetchApp.fetch(uri).getContentText();
// Cache it for 1 minute
cache.put(uri, response, 60);
// Avoid "service invoked too many times"
Utilities.sleep(1000);
}
Logger.log(response);
return JSON.parse(response)[0][order][value];
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment