[9.2.0 - version 48] GESI - Google Sheets ESI Add-On - Now an EVE Online Partner!

9.0.0 aka version 42 March 26, 2022

Version 9.0.0 deprecates the concept of Sheet Storage in favor of a custom properties store implementation. GESI now supports ~1,400 authenticated characters out of the box. This update also makes GESI 100% compliant with the possible refresh token rotation cut over in the future as outlined here. Previously sheet storage would NOT have handled that correctly.

Changes

  • (breaking) Changed how authentication data is stored (again)

    • Sheet Storage concept has been removed
    • Access token is now stored in cache instead of with the rest of the auth data
      • Accounted for 92% of the total size of auth data, moving that allows for more characters to be authed in general
  • (breaking) Function arguments are now validated to be of the proper type.

    • E.g. an error will be raised if you give a string to a function expecting a number, or if a function expecting an array of strings, does not contain only strings.
    • May need to ensure you’re passing properly type arguments
  • NPM dependencies have been bumped to address some security vulnerabilities

    • Nothing was relevant to GESI

Since the logic related to auth has changed, you will need to reauthorize your characters. Be sure to reset your sheet beforehand as well. From within your sheet Extensions => GESI => Reset.

Due to this issue, if you experience Your Login session has expired when trying to authorize a character, see this FAQ item.

9.1.0 aka version 43 March 30, 2022


Additions

  • GESI now has a good amount of proper unit test coverage on its core logic
  • Validates the aud JWT claim is the required value

Fixes

  • Properly handle falsey arguments to optional parameters
    • Handles the context of like =corporations_corporation_wallets_division_transactions(2,,"Blacksmoke16") throwing an error saying from_id should be a number but got string
1 Like

9.1.1 aka version 44 April 1, 2022


Fixes

  • Do not exclude comments when pushing to GAS
    • Restores the @customfunction and jsdoc comments to each function

9.2.0 aka version 48 July 13, 2022


ESI Updates

Some of us were using eve_search as a general search function for data that is not related to a character.
In this case, you cannot use characters_character_search in its place.
This highlighted my ignorance of all the functions included in ESI.
If any of your sheets are still broken by this update, I suggest searching for a function that starts with the word “universe” for something that will replicate your functionality. Be aware that the function parameters probably will differ.

To be clear characters_character_search is the same as eve_search, but just authenticated. It actually allows you to search for more things than you could before. Given GESI required you to have at least 1 character authenticated anyway, the migration should be pretty straightforward.

Also going to link The ESI API is a shared resource, do not abuse it - EVE: Developers here as it’s still applicable.

Point taken. After failing to make it work, I gave up and tried something else.
I have a lot of respect for you and your work, and appreciate your quick reply.

The link to the blog makes me worry that I my sheets might be grabbing too much data.
I am using markets_region_orders and markets_region_history to get sales info for each item that I want to sell in a given area. The function parameters limit what is returned to a single item in a single region. Then I use google’s SQL to return the one number I want. But, on a 50 item sell list, that amounts to 50 tables. I am assuming that all that data is still downloaded even though the query pares it down to one number per table.
Is this problem? I have searched through all my sheets and I believe that everywhere else I am only requesting a very small amount of data, I only request it in one place, and do the calculations in the sheets.

No, you’re doing things correctly. The blog is specific to the search endpoint. I.e. using it to discover things you don’t already have. E.g. search for a, then ab, then abc and so on.

The only issue you may run into doing what you’re doing is Google Sheet’s HTTP request limit of 25k/day. What you could do is write a custom function that handles returning the data you want, but only fetching/parsing the order data once. But if it’s working fast enough for you and you aren’t having any problems, probably not worth it.

I’m trying to use GESI to pull market data from a private station in null sec. I have access to the station and do market work there but it doesn’t belong to my corp. I am getting a Market Access Denied error when using markets_structures_structure. Is there a workaround or is this impossible.

By access, do you mean you are able to dock there? That’s a requirement to access its orders via the API.

Yes, I can dock there and use the market.

And are you sure you’re using the right ID? And the right character? And station I assume you mean a structure, not an NPC corp station or something?

Well I’m glad you asked that. Turns out I had a few issues. I did not have the right station id. It is an alliance owned structure. And I did not have quotes around my name in the function. Now it works fine.

It did lead me to another thought though. That function doesn’t seem to have an option to search for a single type_id. I’m trying to collect a short list of item prices that I can link to other sheets. With a long list like this it seems to me that the order of the list is likely to change as items come in and drop out of the market. How could I either search for a specific item in this station? Or failing that, how can I be sure that I’m getting the right data from this long list if the order is likely to change in the future?

I am by no means adept at spreadsheets so I’m sorry if this is easily found or answered elsewhere. I wouldn’t even know what to search for. I’ve tried using markets_region_orders but it won’t pull from alliance stations.

Correct. ESI doesn’t provide a way to do that so you’ll need to parse all the data and filter down to the type(s) you want. Given the list is for a specific structure, if you wanted the lowest sell value I think you could use MINIFS - Google Docs Editors Help to find the lowest price given non sell orders and your desired type ID. Something like =MINIFS(G2:G, I2:I, 8419,B2:B,false). Could then have another sheet where you have the type names/IDs you care about, use this function to grab the related prices for that type, then can VLOOKUP the price from there where needed.

Does anyone have any idea why all my function calls using markets_region_history( ) stopped working a few weeks ago? I get a “Timeout contacting tranquility” error for all of them.

See ESI Market History Endpoint Down? - #10 by Pychick and Requests to /markets/{region_id}/history/ fail · Issue #1338 · esi/esi-issues · GitHub.

tl;dr Not a GESI issue.