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.
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:
- Open the spreadsheet
- Go to File -> Make a Copy
- Set the name of your copy (do not share with collaborators)
- 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.
- Get plain-text code from the GitHub repo
- In your spreadsheet, go to Tools -> Script Editor...
- This opens a new window. Select "Blank Project" from the initialization prompt
- Copy the raw code into code.js space
- Set the name of the project
- Save the changes
- 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.
- 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