EVE Spreadsheet Extension

README.md

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.

eve-spreadsheet-extension.js

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];
}