I am trying to find a guide on how to login from Excel to query ESI. I am fine with the ESI API side of things which is well described on the esi evetech net website. I have also set up my app at developers eveonline com.
The difficulty is how to login and manage the token exchange from excel prior to the queries.
It seems there used to be a guide on wiki zancha space, but the site is offline… Can anyone enlighten me please?
I have an excel SS for all my indie stuff that I used to use with data pulled out of api eveonline com and eve-central com. Unfortunately both are gone, good things cannot last forever…
I’m not sure of the capabilities of Excel (via custom functions or whatever that is) but one option would be to generate a refresh token outside of Excel, then just store that. This way you wouldn’t have to setup the whole OAuth flow within Excel. Probably would then be doable to check if your cached access token is expired, and refresh it with the stored refresh token if needed.
@Steve_Ronuken Made a blog post a while ago related to Google Sheets, however his Setup and Postman steps would be pretty much what you would have to do; of course storing the tokens in Excel versus Sheets.
This is precisely the part I need help with.
I can run queries that require no characterID from Excel, Postman can manually get me the tokens, but how to use them in Excel?
Alternatively is there a way to use the excel login modes or http headers that the Data → From Web supports? This seems infinitely easier.
Does anyone has the guide that was available at Wiki Zancha Space?
Thanks a lot. I managed to get my assets into an excel table.
Next problem is being able to read all the assets and not just 1000… I have seen a post from you on this:
It’s not really a big deal. You just check the X-Pages header, and loop n times, concatenating results to an array so you end up with one singular array of all the data.
How can I do this (1- Check X-Pages header and 2-concatenate)?
I have never been able to figure out how to get the page numbers from the header, but you can set this as a hard parameter in the query code. Depending on how you have the query setup you can add a custom column that acts as the page number, you just need to estimate how many pages there might be. I have found that 6 is an ok number of I have exceed this ever, for both character and corpID pulls.
So if you are retrieving the asset information from one character, this will add 6 rows with a page number of 1 through 6. From this you can invoke a modified call to retrieve the asset list. The main thing with this is even if the there is only 2 pages, pages 3, 4, 5, 6 do not exist, the return error is ignored so does not effect the functionality of the call.
This is the invoke function that I use
let
Source = (AccessToken, CharacterID, Page) =>
let
Source = Json.Document(Web.Contents(“https://esi.evetech.net/latest/characters/”& Number.ToText(CharacterID) &"/assets/?datasource=tranquility&page="& Number.ToText(Page) &"&token="& (AccessToken) &"&user_agent=XXXXXXX"))
in
Source
in
Source
This will return a list of itemsID and Quantity for each row (page) of the query which can be expanded and then manipulated to display what you want.
If you are still having an issue with the SSO esi tokens let me know I have a step by step on how to set this up for multiple characters.
