nuadi / googlecrestscript Goto Github PK
View Code? Open in Web Editor NEWA Google Sheets script designed to contact the EVE Online CREST/ESI endpoints
A Google Sheets script designed to contact the EVE Online CREST/ESI endpoints
Every time I use the getMarketHistory command its pulling date for two days ago. I am unsure if its my end, google's, EVE's or the code.
Thank you for this awesome Script
https://docs.google.com/spreadsheets/d/1aa1qpiJhC7jSVQRzoR_lUmGjQbHpRreAYoPBrk6zSmA/edit?usp=sharing
It was observed through development of the getMarketGroup(s|Items) functions that the CacheService cannot handle the entire JSON return for some of the endpoints. This is an unfortunate restriction placed by Google.
Therefore, a Cachinator function should be built to manage pagination of large JSON returns so that they can be saved into the CacheService successfully to enable caching of JSON results for low-velocity endpoints such as the getMarketItems or getMarketGroups functions which rarely change.
The benefit of this will be reduced impact on UrlFetchApp calls; however, it could lead to exceeding the CacheService call quota - something I've already encountered. This issue stands as a reminder to research this further and implement a solution that can be easily reverted if necessary.
I mean CREST, eve marketdata and eve central.
Hi Nuadi, I'm having some issues with the Adjusted Price / Estimated Item Value.
I understand the EIV of an item is the Adjusted Price of the minerals required to build it (at an ME of 0%).
This is more or less spot on for sub capital ships (to a hundred ISK or so due to the small delay from what the script pulls and what you see live in-game), however when calculating for a capital hull the value pulled is far lower than what it displays in game.
The EIV for an Apostle showed at 1,234,315,785 ISK yesterday in-game, but the value pulled from the script was 742,446,815.71 ISK for the HULL and 891,329,055.51 ISK for the minerals required to build the hull.
Am I missing a step in the calculations for capital hulls? The same formula applied to Sub Capital Hulls seems accurate.
The formula I'm using for calculating job costs is:
EIVINDEX - ROLE + TAX * QTY
Or:
=((((12343157853.5741129918403%)-((1234315785*3.5741129918403%)5%))+((12343157853.5741129918403%)*0.2%)*1))
Cheers
Sub
This function should return all items found on the market and their corresponding ID.
This will help build master VLookUp tables for item IDs.
Implement a new custom function that returns a multi-dimensional array (matrix) containing all data for a given region and item ID.
Optional parameters that will be added with iterations
Add two new functions that utilize the getHistoryAdv
function to return the average daily volume/orders based on an input argument of the number of days to calculate.
getHistoryAdv gives us a list of prices and volume at which an item was sold.
If you visit the same sheet a different day, the list does not automatically update. This means that the previous day's data is available on the server but not added to the google sheet using the getHistoryAdv function, unless I manually change one of the parameters.
It would be very useful to add a refresh parameter to it, which i can then tie to everything else I'm refreshing by referencing a particular cell.
I'm getting the following issue today:
"TypeError: Cannot call method "getchildren" of null. (line 121).
I've not messed with the script in any way personally, and certainly nothing has changed my end in the past week, when it's been working fine.
Add a version check to the onOpen function that pings the Github repo for a version file, and compare the current version to that inside the script. If a change in version is detected, present the user with a dialog or possibly a toast to indicate a new version is available.
Add a function that uses the market prices endpoint to pull the adjusted price, which is used primarily in industrial tax calculations. This function should only need an item ID.
Since this endpoint is only updated at UTC midnight, it should be cached for a duration until that time to eliminate the need for repeat endpoint contact over a given day. Might be able to get this out of the header - maybe.
Add a function that returns every region and its corresponding ID.
This will help facilitate VLookUp tables for region IDs.
This is so frustrating...
Right, so this error is affecting all functions. It is a limitation that I cannot see nor design around. The current implementation attempts to use a LockService call to serialize UrlFetchApp calls so that GCS does not overload CREST and result in a HTTP 5xx error. However, in spreadsheets with excess of 100+ function calls, it is possible this error could emerge.
I am working on an alternate to LockService that will utilize a sheet within the current spreadsheet to manage a local semaphore repository. The goal is to remove the need to use LockService and use the SpreadsheetApp instead, which does not appear to have any quota for use.
The semaphore repository will simply be a sheet where column A has the value lock
in a cell with a corresponding boolean value in the cell beside it. When a script attempts to run, it will search this sheet for a lock that is set to FALSE, attempt to set it to TRUE, and then proceed. If the script cannot find a lock set to FALSE, it will sleep for 100 milliseconds and then retry.
It will be possible to set multiple locks. This should enable the script to run in parallel as it normally does from separate cells. In addition, a side effect of this will be that users can control the number of parallel function calls so that in the case of large sheets they can reduce their overall function velocity to reduce hitting the UrlFetchApp quota. I think. We'll see.
Hi, following on from our discussion in here: (#11) I've adopted the "getStationMarketPrice" function for loading market prices. At first this worked fine, but now it returns the values as 0 every time. I've tested across three different Google accounts and haven't had the daily quota error once.
Even when attempting to call a single item (it doesn't matter which item I chose) compared to a list of items with the function just repeated for each ID (rather than using the list function) it still results in "0". Not sure if I'm doing something wrong or the Eve servers are throwing the data back as 0 or not. Thanks
Edit: I should also add the "countstationvolume" and "getAverageDailyVolume" are also returning 0's.
It would be nice if this script allowed the caller to pass an integer volume threshold to getMarketPrice so the small volumes could be ignored. This is useful when trying to find the actual market price of high-volume items (like Planetary Interaction goods) that people try to influence with orders of 50 or 100. Thanks!
This feature will enable you to specify a minimum volume to consider when processing the orders.
For example, you have 10 million units of Tritanium and would like to sell it all at a single station using buy orders. You make a getMarketPrice() call for buy orders and specify an optional buyVolume parameter set to 10 million. A price will be returned showing you the average aggregate price as long as this minimum volume is available.
Conversely, for sell orders it will only return a price if a minimum volume is available, and again will be a weighted average price.
Implement a function that black-boxes the CacheService calls to centralize management and reduce service overload through a single Utilities.sleep() call.
I would like to add a feature request to be able to pull daily volume information for the item.
Can we please get the order ids displayed in the output? It will be necessary to reference the unique numbers that Eve assigns to each order, in order to do complex analysis.
Crest is going to get a bullet in it's head sooner or later, either through natural sunsetting or breakage via wontfix
bugs. There was a bug in the industry/systems
endpoint this release on both ESI and Crest that broke a lot of people's build spreadsheets. The ESI bug is already fixed and live but the crest fix won't go in until the next downtime. Frankly I'm surprised Crest is getting fixed at all.
Can we get this toolset using ESI as much as is currently possible?
getOrders gives the following error:
Reference error: "filters" is not defined. (Line 753).
Issue in version 8a.
The getMarketPrice function has a strict requirement that a station ID be included in the argument list. This was fine in most use cases, but a new requirement has emerged where the regional price is desired.
Therefore, refactor getMarketPrice into two new functions: getRegionPrice and getStationPrice. They will both utilize getOrdersAdv in the same way but help identify what arguments are needed and what type of price is expected in the return.
i'm receiving the error for all cells containing the GetItemPriceList function. Has something changed in Google Sheets? I was not getting this error prior to today.
The GetItemPrice function is working fine....only the GetItemPriceList seems to be broken.
Thank you.
I wrote these functions this morning for a user who needed the information. I'm creating this ticket so that I have the code and can properly test/document these functions later.
function getItemInfo(itemId, refresh)
{
var itemEndpoint = 'https://crest-tq.eveonline.com/inventory/types/' + itemId + '/';
var itemData = JSON.parse(fetchUrl(itemEndpoint));
return itemData;
}
/** @customfunction */
function getItemVolume(itemId, refresh)
{
var itemData = getItemInfo(itemId, refresh);
return itemData['volume'];
}
This error was first brought up by another user using their own script here: https://www.reddit.com/r/evetech/comments/42nadd/crest_unavailable_error/
I was able to replicate the error in my script, so I'm creating this to simply track development of a solution.
The reponse from a cell is the following:
Address unavailable: https://public-crest.eveonline.com/market/10000032/orders/sell/?type=https://public-crest.eveonline.com/types/2514/
Which is false, the address is not truly unavailable. Based on my work with a python program, my theory is that it's instead an empty response from CREST.
Regardless, I plan to implement a retry subsystem.
Hi
I would know if there is an way to get all RegionID and StationID sorted in one list/sheet.
I have been looking at the web to see of i could find some help in what data i can pull and how to do it, b/c i am real new to Crest, have in the past been using eve central, and want to build a new market trade sheet, and found your guide, very help full, many thanks in advance.
Best Regards
J J
Add a function that takes a group ID and returns only the items found on the market in that group.
The UrlFetchApp and CacheService calls are sources of function failure. Google's error message for the rate limit being hit includes a suggestion to use Utilities.sleep(1000) between calls. However, this is not an iron-clad requirement based on the function's current use of 600 milliseconds for UrlFetchApp with little or no errors.
In addition, the rate limit for CacheService appears to be different than that of UrlFetchApp, but Google does not publish their rate limits. The current use in GCS is only 300 milliseconds for this service.
Therefore, a dynamic sleep system should be implemented that can be used to optimize the sleep system to determine the optimal duration between service calls. This should be a new method, much like profileGetMarketPrice, that exercises the getMarketPrice method to determine the sleep times required.
The getPrice method currently assumes that all price information can be obtained from the first page of the JSON market data. This is a very weak assumption, and should be removed to support multi-page results and processing.
https://gist.github.com/fuzzysteve/820cd2573d87a1d20d87 may be of interest, for how to get the oauth stuff handled a little more 'natively'
In function last var =1 and i setup trigger for function one of 5 min , but price in google docs don`t move
but then i manual replaces last var in func and price updating ... How do what updating was automatic?
=getMarketPrice(15729;10000002;60003760;"sell";1)
Hi, I receive a lot of times the error in object, and of course anything didn't work. I use function getMarketItems with 1000 as parameter. I had around 100 rows for check buy/sell price and volume. How can I fix?
Thanks a lot for your job,
Erik
List getRegion|StationMarketPrice there are examples of station and region function calls, but they have swapped headings (getRegionMarketPrice Example, getStationMarketPrice Example)
Add a function that returns all market groups on the market and their corresponding group ID.
"message: Request failed for https://crest-tq.eveonline.com/market/10000002/orders/sell/?type=https://crest-tq.eveonline.com/types/34/ returned code 403. Truncated server response: {""message"": ""type query parameter was not of right Uri type"", ""isLocalized"": true, ""key"": ""typeNotRight"", ""exceptionType"": ""ForbiddenError""} (use muteHttpExceptions option to examine full response)
name: Exception
fileName: Code
lineNumber: 519
stack: at Code:519 (fetchUrl)
at Code:85 (getMarketJson)
at Code:144 (getMarketPrice)
"
From my understanding its saying i am not calling the correct type id? has the mineral ids changed
Hi Nuadi, is it possible for you to add the cost index for reactions please?
eg. =getcostindex("system","reaction")
Thanks
Sub
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.