nuadi/googlecrestscript

A Google Sheets script designed to contact the EVE Online CREST/ESI endpoints

googlecrestscript's Issues

getMarketHistory two days old data

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

Build a Cachinator

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.

Hey nuadi, is it me, or are all EVE API's often down/unresponsive?

I mean CREST, eve marketdata and eve central.

EIV / Adjusted Price

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

Add getMarketItems function

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 getMarket(itemId, regionId) method

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

• orderCount : specifies the number of orders to display from the fetch
• sort : an integer value representing the column to sort, negative for descending and positive for ascending

Add a getAverageDailyVolume and getAverageDailyOrders functions

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.

[Request] getHistoryAdv needs a refresh parameter

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.

Getchildren error?

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

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 getAdjustedPrice function

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 getRegions function

Add a function that returns every region and its corresponding ID.

This will help facilitate VLookUp tables for region IDs.

Service invoked too many times in a short time: lockBean rateMax. Try Utilities.sleep(1000) between calls. (line 0).

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.

Values returned are "0"

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.

(Feature Request) Pass volume threshold

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!

(Feature Request) Get number of sell/buy orders for item in station.

Implement minimum volume business logic

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.

Black-box the CacheService calls

Implement a function that black-boxes the CacheService calls to centralize management and reduce service overload through a single Utilities.sleep() call.

Feature Request:Historical Volume Information

I would like to add a feature request to be able to pull daily volume information for the item.

Request: order id in getOrders function

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.

(Feature Request) Possibility to read sell or buy order count with getMarketHistory.

Update for ESI

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 error

getOrders gives the following error:

Reference error: "filters" is not defined. (Line 753).

Issue in version 8a.

Refactor getMarketPrice into getRegionPrice and getStationPrice functions

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.

"Internal error executing the custom function" error

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.

Add getItemInfo and getItemVolume functions

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

Periodically receive "Address unavailable" response

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.

I is thewre an easy way to get all stationID.

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 getGroupItems function

Add a function that takes a group ID and returns only the items found on the market in that group.

Optimize service call sleep system

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.

Implement multi-page support

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.

Alternate for how to do the oauth setup.

https://gist.github.com/fuzzysteve/820cd2573d87a1d20d87 may be of interest, for how to get the oauth stuff handled a little more 'natively'

Don`t updating prices. What i do wrong?

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)

Service invoked too many times for one day: urlfetch

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

(Feature Request) Possibility to narrow getMarketHistory to a station.

Swapped heading in gdocs example sheet

List getRegion|StationMarketPrice there are examples of station and region function calls, but they have swapped headings (getRegionMarketPrice Example, getStationMarketPrice Example)

Add getMarketGroups function

Add a function that returns all market groups on the market and their corresponding group ID.

getting an error and im not sure how to fix it

"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

Cost Index for Reactions

Hi Nuadi, is it possible for you to add the cost index for reactions please?
eg. =getcostindex("system","reaction")

Thanks
Sub