Sunday, July 20, 2014

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.

34 comments:

Unknown said...

There's a bug in the script -- getIndustryJobs() calls validateAPI() on personal API keys with the keyType="Account" ; the correct keyType should be "Character". Futhermore, you should probably set up some sort of error message if "Character" pulling fails, rather than just defaulting to "Corporation"... makes the ensuing error message very misleading.

Unknown said...

Good catch. I'll push that update in the next day or so. I also have to push a fix for getPOS().

I want to write more robust fail cases so I can push the app to be a full fledged gdoc add-in. So it's on the TODO.

Unknown said...

Hey!
So your guide has finally got me adding Crest / API stuff to my spreadsheet for the first time.
I haven't managed to get calls that require a corp API key to work yet, but from the above it sounds like you are working on it.

However, there seem to be some items missing from your AllItemPrices call, e.g. Parity or Optimized decryptors. Other newer items, such as the Prospect, is available for a vlookup.
I don't believe I'm doing anything wrong since all other things I've had a need for (T2 subcapitals, components) have worked just fine.

Any ideas?

Regards,
Simon

Unknown said...

1) Try pulling a fresh copy. I pushed two fixes last night for POS. Also, you may not have the access rights configured correctly. Verify that first.
2) you shouldn't need decryptors for job cost calculations
3) If there are items missing from the AllItemPrices, you should bug CCP_Nullarbor and CCP_FoxFour. I've been poking them pretty consistently about missing data.

Unknown said...
This comment has been removed by the author.
Unknown said...

Hey again,
Thanks for the quick reply :)
Yeah I'll have a go at the POS stuff tomorrow.
1) Done, got a new one from github.
2) Nah I'm using the avgprice for invention, as a stopgap until I fix why my price imports from eve-central keep getting messed up :) But that's another story.
3) How does one go about poking them about it, forums, ingame or elsewhere?

Cheers

Unknown said...

It looks like you're missing the metamaterials (nonlinear, photonic, plasmodic, and terahertz).

Unknown said...

This is a bug with the CREST feed. They are not being reported by CCP right now. Bugging CCP Nullarbor and CCP FoxFour about all the missing data

niko86 said...

Having troubles with the getPOS code keep getting errors like this:

Error: Exception: Request failed for https://api.testeveonline.com/account/APIKeyInfo.xml.aspx? returned code 403. Truncated server response: 2014-07-24 08:40:48 Key has expired... (use muteHttpExceptions option to examine full response) (line 556).

Thats with the test server boolean as false or not input at all. The key is fine and has the correct permissions. Is one key with all the permissions needed by this toolkit or one for each function the best way to go about giving permissions?

Unknown said...

Pull a fresh copy of the scripts. I fixed a couple of bugs on weds, like this one.

niko86 said...

I did before posting just to be sure. Issue still present now, github is showing the last modification date to the gdocs script is 3 days ago.

Unknown said...

I was sure I pushed this fix. I will investigate tonight. Expect it to be fixed by the weekend.

Reply to issue here with error screenshot (don't need to see API key) https://github.com/lockefox/CriusDev/issues/3

Unknown said...

Yeah I keep running into more of these.
Mostly T2 Capital components.
It's weird tho, several of these were in the list just a few hours ago, and now they are gone/unavailable again, while some still work.
I guess it's still very much a work in progress @ CCP and Crest.

Unknown said...

Hey,

Having some weird problems with getIndustryJobs... (Using 1 corp API key to report on the jobs of ~12 characters)
It worked fine for a day when I implemented it. I also made a copy of my manufacturing sheet for a friend, that one too worked just fine.
Yesterday however, my sheet started running into an Error with the getIndustryJobs call, even though the copy of the sheet I made, still worked perfectly fine.
I've tried removing the triggers entirely and adding them again. I've tried replacing the script (again) from github, or the copy, but still I get the same error:

Error: Request failed for https://api.eveonline.com/corp/Locations.xml.aspx? returned code 400. Truncated server response: 2014-08-01 05:32:31 Owner is not th... (use muteHttpExceptions option to examine full response) (line 670).

The end cuts out, but while googling I found the whole error message elsewhere (https://forums.eveonline.com/default.aspx?g=posts&m=4496356#post4496356)

Owner is not the owner of all itemIDs or a non-existant itemID was passed in. If you are not trying to scrape the API, please ensure your input are valid locations associated with the key owner.
2014-04-21 06:08:13


Any idea why this is happening, and oddly enough only one one sheet? It hasn't mattered if the sheets use the same API key, or a different one from the same account, or keys from separate accounts.

Thanks!

Unknown said...

Making a copy of my sheet with the error, produced the same error in the copy.
So I resorted to making a copy of the copy I made for my friend, and changing any contents that were different, back to what my original sheet had. And this seems to work, for now...

But if you have any ideas as to how this error could occur, it might help me prevent it in the future :)

Unknown said...

Step1) Make sure you have the very latest version of the script. I fixed a bug with the Locations call in getPOS last week. Either pulling a fresh copy off the master gdoc, or copy/paste from github.

Step2) If step1 doesn't work, add me to the editors list. If you add locke.renard@gmail.com to the collaborators list, I will drop in for troubleshooting

Unknown said...

Thanks for the reply.
Yeah I had tried pulling fresh copies several times, including deleting the scrip entirely and making a fresh one with fresh code.
Only way I could get it to work was to replace the gdoc itself with the one I originally copied.

If the problems shows up again in the current gdoc, I'll be sure to get in touch with ya, thanks!

Gaio said...

Does any one when loading AllSystemIndexes get strange numbers?
To Aderken for example i get for all indexes 0.001. And if i go to https://www.fuzzwork.co.uk/blueprint/ i get diferent numbers

Can some one help me?

Unknown said...

Hey there,
It seems to work fine for me, this is Aderkan atm.
Aderkan || 0.0164 || 0.0203 || 0.0365 || 0.0208 || 0.0259 || 0.0010.
Do you have other indexes which do show properly?

Gaio said...

I didn't have the last updaded of the script. But some were working well other don´t. Now is working like it should.
Thanks for the reply and thanks for your work in helping us all that dont have the knowledge to create something like this.

For me it help me a lot in my math.

Unknown said...

It's not my work :)
But I also appreciate John Purcell's work as well!

Unknown said...

Thanks for you work, dude! I really like to have another way to work without having the importXML issues.
There is one thing I don't get: I inserted my corporation's full API in the API page, selected it in Control and still nothing shows up. What am I doing wrong?
TIA
I really love to see my fuel status without being online.
o/

Unknown said...

As of now the table from AllItemPrices gives me wrong numbers
E.g.: Dominix AdjustedPrice 87,535,047.88 (in game value 135M and some.....)

The System Indixes are spot on tough

(i've alread sent an ingame mail to lockefox)

Unknown said...

This is a feature of the feed and not a bug with of the tool. The "adjusted" price should be 30d moving average to avoid gaming... the current disparity is due to the recent spike in tritanium prices which will take a couple weeks to wash out in the adjusted prices.

If you believe this is an actual bug, I encourage you to file a bug report with CCP under Fourm/website.

Unknown said...

Hi, thanks for the work you have put into providing these tools!

I believe that I have added the script into my google sheet correctly, however, I am unable to pull the corporation data, =getpos for example.

I’m not really sure where to place my corp API key, on the example google sheet; I have copied all the tabs into my own sheet. However on the =getpos function I see that it references an “APIKeys!” tab.

Any help of getting this to work would be greatly appreciated.

Unknown said...

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

your call should look like:
=getPOS(####,"stringofkeyjargon",true,true,false)

My example sheet uses a common cell reference for the #### and "stringofkeyjargon" which you will need to replicate with your own API keys. Also note, getPOS will require a corp level API key which can only be generated by a director.

Unknown said...

Hello,

can please you explain how the Control and APIKeys Tabs exactly working?

THX!

Unknown said...

Hello again John!
I was just wondering if there's any chance you can update the script so it functions again after CCP messed with CREST? :) Mainly interested in getting AllitemPrices and AllSystemIndexes. I changed the base url from http to https since the former is no longer supported, but that did not seem to be enough, so perhaps something else changed (see below).

From CCP Foxfour:

"Few things changed today, really sorry for the lack of notice or proper communication. Things have been a bit hectic, which shouldn't stop this kind of communication. Will try and get a dev blog out quickly tomorrow.

HTTP has been disabled on public CREST. So you will have to use HTTPS. The SSL configuration has also been updated and now supports TLS 1.2 but has dropped support for SSL 3. There was then also some crazy outages last night and during the day today which should now be sorted."

Would be greately appreciated, thanks!

Unknown said...

Just need to change the CREST paths from HTTP to HTTPS, rest should be automagic

https://github.com/lockefox/CriusDev/blob/master/gdoc_scripts.js

Unknown said...

Were there more than two instances of HTTP? If so I must have missed some :)

The one you linked is working fine however, excellent. Thanks!

Unknown said...

Should just be the two CREST ones. I didn't mess with the eve-central links because I didn't get a chance to debug that yet.

Unknown said...

One thing, I'm not finding an option to separate prices by market hub. Does the CREST API support that?

Gaio said...

Hello,

For some reason AllSystemIndexes (TRUE,false) stopped working, i tried to go to the original sheet but the sheet it's not working. Did something changed with the last update (Parallax) that i know?
Regards, Thanks

Unknown said...

Hello the =AllItemPrices (true, false) and =AllSystemIndexes (true, false) both stopped working is there a fix to this?

Post a Comment