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

New post for the new forums.

For all the spreadsheets in space people out there:

Details are in the readme

Enjoy.

Discord Server: GESI

Link to old post:

11 Likes

Just a quick update.

Multi character auth is nearing completion. That and a general rework of some other things should be done within next few weeks.

Cheers!

Hi,

i have done everything but still got this using the Auth function:

{ā€œerrorā€:ā€œinvalid_requestā€,ā€œerror_descriptionā€:ā€œThe callback URI doesnā€™t match the value stored for this clientā€}

What did i wrong?

Regards

1 Like

Sounds like something is messed up with the callback URL from the dev site.

Make sure it is https://script.google.com/macros/d/{SCRIPT_ID}/usercallback where the SCRIPT_ID comes from File -> Project Properties and copy the Script ID in the script editor.

If this doesnā€™t work. Try going to the GESI dropdown and click Reset Auth. Then try reauthorizing your character.

If you wish convo me in game and we can debug this if neither of those work.

Hi Blacksmoke16,

Thanks for sharing the script and auth stuffs.
I donā€™t know anything about coding so no idea how the whole stuff is working.
But following your instructions in readme it works !

However, i would like to request for something new to import orders from a single citadel.
Currently it imports only up to 5000 orders ( only the first page).
Is it possible to modify the function so that it imports all the pages available; instead of only the first one ?

Cheers and thanks again

Chani

Thanks for this work!

@Chani_El_zrya

This is a known issue on my end. It will be solved in Version 2 which should be out in a week or so.

A temp work around would be:

  1. Calling the orders endpoint.
  2. Copy paste the data to another sheet.
  3. On 57, change the URL of the endpoint to "url": "/markets/structures/{structure_id}/?page=2".
  4. Then call it again (you may have to cut/paste or delete re type the function to get it to update).
  5. Next copy paste that to another sheet, set page=3 and repeat.
  6. Parse/analyze that data you need from your pasted sections.

The solution for version 2 will most likely be a page parameter that defaults to 1 if none is given. Then in your call could specify what page you want to load. Other option would be to load in ALL the data and return it, but this wonā€™t work to well as youā€™d be probably hitting some Google Sheet limitations on request execution time.

Keep an eye out on this thread for the release :slight_smile:

BREAKING CHANGES

This is a complete rewrite of the script. Chances are you will not be able to just copy paste this version in and have it work. Please reread the readme for instructions on how to set it up again.

New features

  1. Ability to authorize and use multiple characters.
  2. Easier to create/edit endpoints in future.
  3. More endpoints! Includes all endpoints in the following groups:
    • Alliance
    • Assets
    • Bookmarks
    • Calendar
    • Contracts
    • Industry
    • PI
    • Skills
    • Wallet
    • Wars
  4. As well as all endpoints from previous version.

For information on the endpoints included, check the code starting at line 250. The annotations above each function explain what it does and what is required etc.

Another good source of information is the ESI Site itself.

Also due to the amount of work that went into this I probably forgot something or didnā€™t test something enough. If you find any issues, or just want to have a new endpoint added, create an issue on the github.

Enjoy!

EDIT: Pagination isnā€™t done yet. That will be released soonish.

2 Likes

2.1.0 September 2, 2017


  • Add ability to select what page of an endpoint it should fetch
  • Add region orders endpoint
  • Add region item history endpoint

When importing all orders from a structure is it possible to seperate sell and buy orders ? Lots of hassle to filter imported data in a spreadsheet when they are not sorted before.

@Samuel_Sachs

No, Iā€™m just providing the raw data.

Filtering it afterwards shouldnā€™t be much of an issue as you can just key off of the is_buy_order column using the filter function, like so:

=FILTER(A1:K,E17:E = FALSE)

Where A1:K is all of the data, and E1:E is the is_buy_order column. This would show you all non buy order orders. This could prob also be nested in a sort function to display the data you need.

2.2.0 September 9, 2017


  • Add Sovereignty endpoints
  • Add page param to characterBlueprints endpoint

Note: I donā€™t have enough blueprints to test to paging, so if it doesnā€™t work, let me know.

2.3.0 September 17, 2017


  • Consolidate some logic
  • Fix bug with allianceNames endpoint
  • Fix bug with characterSkills endpoint

2.3.1 September 18, 2017


  • Allow for more than 100 IDs in allianceNames endpoint

3.0.0 October 29, 2017


Breaking Changes

I took the time to refactor the multi character feature to include corporation_id for each character. This allows the script to know the corporation_id of each authed character so that you wonā€™t have to include corporation_id param for each corp endpoint.

Because of this, this version is not compatible with the previous ones. Notice the new format for the CHARACTERS array. New format is as follows:

CHARACTERS = [
  {
    character_name:  'character1',
    corporation_id: 123
  },
  {
    character_name:  'character2',
    corporation_id: 456
  }
];

New Features:

Be sure to reauth your accounts in order to make use of the new endpoints.

  • characterMining
  • corporation moon mining endpoitns
  • corporationNames
  • corporationStructures
  • corporationMembers
  • corporationMemberLimit
  • corporationMemberTracking
  • corporationStandings
  • corporationIndustryJobs
  • corporationAssets

Bug Fixes

  • Fix issue with allianceNames endpoint that broke when a empty cell was part of the input array

As usual, any issues/trouble feel free to get in touch with me in game or on discord.

NOTE: I do not have a way to test the corp moon mining endpoints atm. It should work but if anyone has issues with it, let me know.

1 Like

3.0.1 October 29, 2017


Bug Fixes

  • Fix undefined planet_id

Thank you for creating and sharing this - it works well.
The one method I was looking for is to return Region pricing data for a specific itemID (or list of IDs).
Closest I have found is ItemHistory(). [ MarketPrices() does not have any params. ]

Does the ESI support returning only a specific item or would I need to create methods to extract individual items? Iā€™ve looked through the ESI docs but did not see something this specific.

Thanks again for doing the heavy lifting.

Iā€™m pretty sure my regionOrders function can do that. There is an type_id param that will return all orders in that region for that type_id.

EDIT: I should clarify this would only give you orders which you would then have to parse to get lowest sell/highest buy etc.

Possibly checkout naudiā€™s script. His is more market focused and might have some more useful things.

Thanks mate, Nuadi had exactly what i needed.

For anyone else looking for market specific data see: https://github.com/nuadi/googlecrestscript

2 Likes

3.1.0 November 2, 2017


New Features

  • structure
  • strucutures
  • corporationWalletTransactions
  • characterNames

Bug Fixes

  • Fix second_party_id column not being populated in corp/char walletJournal endpoints
  • Add from_id parameter to wallet endpoints