Building Better Spreadsheets - Crius Toolset

Crius releases on Tuesday, and most industrialists are scrambling to replace their spreadsheets (I know I am).  What's worse, the job-cost equation requires live data from the game, which can be difficult for someone relying on spreadsheet feeds.  Fear not!  I have great news!

Crius Feeds In Google Spreadsheets

These functions let you read in the CREST/API feeds of common calls and import them directly into your spreadsheet.  Furthermore, the triggers are set up to automatically refresh the data periodically so your spreadsheet will always be up-to-date.  Though this is not an exhaustive API tool, and still could use some more features, it should be a huge leg up for any spreadsheet jockey.

Most of the feeds are designed to dump the entire feed, and don't offer much filtering in the call.  Instead, they were designed to be used in a reference sheet that could then be leveraged using VLOOKUP() or QUERY().  This might lead to some issues with complexity down the line, so I intend to eventually add some finer calls that will just return single-line kind of data.

Getting Started

Method 1: Clone the Master Spreadsheet

This will give you the tools and triggers, but will not stay up-to-date with the master sheet.  Until I can wrap up the code as a stand-alone Drive app, this will be the most stupid proof way to get a copy:

1. Open the spreadsheet
2. Go to File -> Make a Copy
   
3. Set the name of your copy (do not share with collaborators)
   
4. Remove any extra sheets/calls you need to and start developing your spreadsheet

This method is the easiest to start with, but has the issue that it will not keep current with updates.  

Method 2: Copy-Paste from Github

The codebase is free and open source, and is designed to be copy-pasted into the gdoc script interface.  This method is a little more tedious, but will be easy to copy updates as they come out.

1. Get plain-text code from the GitHub repo
2. In your spreadsheet, go to Tools -> Script Editor...
   
3. This opens a new window.  Select "Blank Project" from the initialization prompt
   
4. Copy the raw code into code.js space
   
5. Set the name of the project
6. Save the changes
7. Configure the app triggers.  Set get/All functions to 1hr timers
   
   

This will give you all the utilities in a fresh, or existing codebase.  Also, configuring the triggers appropriately will keep the data up-to-date automatically.  It's technically optional, but without time triggers, it will require a fresh open to pull fresh data.

Also, as updates come out, you'll be able to drop in the new code.  I expect to keep this project backwards compatible, so each drop in should ADD features.  Though, of course, if you go editing the code, you will need to be more careful about dropping in changes.  

Function List

• getPOS (keyID, vCode, header_bool, verbose_bool, test_server_boo l)
• getFacilities (keyID, vCode, header_bool, verbose_bool, test_server_bool )
• getIndustryJobs (keyID, vCode, header_bool, verbose_bool, test_server_bool )
• getAvgVolume (days, item_id, region_id )
• getVolumes (days, item_id, region_id )
• AllItemPrices (header_bool, test_server_bool )
• AllSystemIndexes (header_bool, test_server_bool )
• AllTeams (header_bool, verbose_bool, test_server_bool )
• AllAuctions (header_bool, verbose_bool, test_server_bool )

The functions are designed to be referenced as simply as possible.  CREST feeds like AllItemPrices and AllSystemIndexes can be referenced without arguments if desired.  Also, the classic API feeds are designed to return as much information as they can, with internal switches to try and use the /corp/Locations feeds if possible.  Also, most feeds come with a "verbose_bool" trigger to add/remove ugly or useless raw ID kind of data.  Lastly, the test_server_bool has been left in the release.  For TQ this value can either be blank or false.

Function guide below the cut

Function Guide

getPOS (keyID, vCode, header_bool, verbose_bool, test_server_bool)

getPOS requires a corp API key with the following feeds:

• StarbaseList
• StarbaseDetails (optional)
• Locations (optional)

getPOS will return a list of all corp POS, and their locations (thanks FuzzySteve).  If the key will allow, it will also report the fuel status and names of each tower as well.  This feed will not return what labs/arrays are attached to a POS however, because that would require a complicated asset call.  Also, fuel times will account for the tower type, but will not account for sov bonus.  I expect to add a fancier calculator function in the future.

getFacilities (keyID, vCode, header_bool, verbose_bool, test_server_bool)

This is a new feed and will not work on TQ until Crius is released.  getFacilities requires a corp API key with the following feeds:

• Assets
• Locations (optional)

getFacilities will return a list of online labs, and their ID information.  If Locations is allowed, it will also do an ID->name conversion, making it very easy to keep track of what equipment you have, and its status.  This feed will not tell you the location of any given asset, again because of an obnoxious lookup loop required.

getIndustryJobs (keyID, vCode, header_bool, verbose_bool, test_server_bool)

This feed will work with both corp and personal APIs.  The following feeds are required

• Industry Jobs
• Locations (optional: corp)

getIndustryJobs will return a list of active, non-delivered, or recently delivered jobs.  It will not show an extended history.  I have not coded up a tool to look at extended history, but could mock up one if people are interested in the feature.  If the Locations feed is enabled on a corp key, it will do pretty conversions for POS work.  All feeds will return NPC space names (thanks FuzzySteve).  I expect this to be buggy on outposts.

getAvgVolume (days, type_id, region_id)

This is one of my favorite feeds.  It takes an x-days average of an item's volume off the CREST /market feed.  It returns a single value corresponding to the simple average of values over the period.  Can go up to 365, like the CREST feed, excludes zero-values.  I usually pair this with eve-central's //sell/volume as a sanity check for orders up.

getVolumes (days, type_id, region_id)

Same as getAvgVolume, except it returns an array.  I don't remember if I nullified zeros on this feed or not.  I don't use this one very often, but it's there if you need it.

AllItemPrices (header_bool, test_server_bool)

AllItemPrices returns the server-prices for every item tracked.  This is required for the new job cost calculation, where it leverages the server-price as part of the cost.  The result is meant to be a lookup table to be referenced with VLOOKUP().  Math for calculating job costs can be found on this devblog: Industry & 3rd Party Developers.  HINT: job cost uses adjustedPrice

AllSystemIndexes (header_bool, test_server_bool)

AllSystemIndexes is the second new CREST feed required to do reliable job cost calculations.  Again, this is designed as a VLOOKUP() reference table.  Again, see the devblog Industry & 3rd Party Developers for more information on how to use these numbers.

I may reduce this feed to a single SystemIndex() call, for those who don't want/need a lookup of everything.

AllTeams (header_bool, verbose_bool, server_bool)

I am still not sure if I like this output yet, but I really wanted to have a Teams viewer in gdoc.  Right now, it returns one line for each team attribute, rather than trying to bulk them together like in-client.  Also, the groups thing is kinda unwieldy.  Expect this feed to be the first I update.  Also, if you're unclear if teams are worth tracking, I highly suggest checking out Scientist In EVE's Teams Review

Also, I make no guarantees about the timeRemaining value being absolutely correct.  Javascript is an asshole about trying to convert to/from UTC for absolute time differences.

AllTeamAuctions (header_bool, verbose_bool, test_server_bool)

Just like AllTeams, AllTeamAuctions is meant to give an out-of-game look at the available teams.  It also has the same bugs as AllTeams, and should be addressed in the same manner as AllTeams.  Though the CREST feed does give auction bidder information, the feed only reports the current winning bid, winning bid system, and when the auction will expire.  Zero-bid teams will show as zero with a blank system name.  Though without the names/activity values (Looking at you CCP_FoxFour), it's going to be a pain to correlate teams/auctions.