Using ESI with Google Sheets

I’ve been meaning to write this post for a while now, so here it finally is; How to use ESI, with google sheets.

Now, the unauthenticated endpoints are relatively easy. You’ll still need to write a little code for them, or steal it from somewhere else, but other than that, it should just work. Authenticated endpoints are another matter.

ESI uses Eve’s SSO for authentication, which means, at some point during the setup, you’ll need to handle the OAuth flow to get what’s known as a refresh token. This token is much like an XML key from the older API, allowing you to access the endpoint without any human intervention. (It’s actually used to get the access token, which allows you to do things for about 20 minutes. The refresh token can be used at will to create new access tokens)

Setup

Due to the way it works, this means you’re going to have to go to the developers site, and create a new application. Each application has a client id, and a secret, and that secret should be held closely and not revealed to the general public. So you need to create your own.

So, go to the site, log in, and click on ‘application’ up at the top. You can then click on ‘create new application’.

Give it a name and a description. I’d suggest something meaningful to you for the name, but the description isn’t important; you just need something there. Pick ‘Authentication & API Access‘ as the type.

Now you need permissions. These are what are known as ‘scopes’ in OAuth. They’re like the various things you can pick in the XML api, allowing access to various different things. What scopes you pick, is dependent on what you actually want to do with the login. When actually doing the login, you tell it which scopes you want. As long as they exist in the application definition you’re filling in now, you can get them. You don’t need to ask for them all.

For this example, I’m asking for the esi-universe.read_structures.v1 and esi-markets.structure_markets.v1 scopes. This allows me to pull the market data from a structure I have access to (and get its name). Just click them, and they should move over to the box on the right.

Now comes the ‘complicated’ bit. The callback url. When you use OAuth, you get sent to the login server, log in there, then get redirected back to the source. The callback url is where you’re sent. As you don’t have a webserver for this (probably) we’re going to use a tool called Postman to handle this step.

Fill in https://www.getpostman.com/oauth2/callback as the callback url. Click on ‘create new application’.

Finally, click on ‘view application’ next to your newly created one.

Postman

Postman is a chrome app which is designed for working with APIs. We’ll be using it to handle the initial authentication. Get it from its site. Once you’ve installed it, run it.

It should look something like this. On the authorization tab, change the drop down to ‘OAuth 2.0’. Then click the ‘get new access token’ button.

Auth URL: https://login.eveonline.com/oauth/authorize

Access Token URL: https://login.eveonline.com/oauth/token

Client ID: The ID from the application page you just created

Client Secret: the Secret Key from the application page you just created

Scope: the scopes you want, separated by spaces. So in this case:

esi-universe.read_structures.v1 esi-markets.structure_markets.v1

Finally, hit ‘request token’. This should send you to the login page for Eve. If it doesn’t work, make sure you have no extra spaces at the beginning or end of the various options.

Once that’s done, and you’re authorized, you should see a token in the existing tokens box.

This lists your access token (you can ignore this, tbh. you’ll be getting a new one.), and your refresh token (this is important, so take a note of it, along with your client id and your secret.)

That’s you finished with Postman, until you want a new token for something else.

Google Sheets

Google Sheets doesn’t handle JSON or OAuth natively. This is a bit of a pain, but the ability to add custom functions takes care of that nicely. Especially when someone else writes them for you ­čśë

For this example, we’ll create a new workbook.

On your new workbook, create a new sheet and call it config. Put the client id, the secret, and the refresh token into separate cells on it.

On the Data menu, pick ‘Named Ranges…’. This will open a sidebar. select the clientid cell, and click ‘add a range’. call it clientid, and hit done. Repeat with the secret, and the refresh token, calling them ‘secret’ and ‘refresh’.

you can then close the sidebar. This is just to make it easy to refer to them later.

Now, click on Tools->Script editor. This will open a new window. switch to it.

Delete the contents of Code.gs (it should be an example myFunction only. if not, you’re in uncharted waters)

Paste in the functions from my Github. I’d explain them all, but that would massively balloon this post, and you’d need to understand Javascript. (They’re relatively simple)

Finally, on a sheet without data you care about (as this is a new workbook, sheet 1 works) use

=getCitadel(1023164547009)

 

and it should (eventually) populate with all the orders from that citadel. get the citadel id from a bookmark, asset id, or market order.

 

I need to do more work on this, as it’s not storing the access token anywhere (there’s an issue with the sheet doing updates on cells from custom functions)

Using ESI with Google Sheets by Fuzzwork Enterprises is licensed under a Creative Commons Attribution 4.0 International License.

  • Calaheim

    Will you make scripts so that do what your xml based doc scripts do. And could you do a tutorial for total newbs to coding so they can make it work.

  • Crystal4276

    Hi Steve,

    Thanks you so much for making this available.

    I have tried to use it. So i managed to pull order from citadel i’m able to dock in Null.
    Only issue is that the number of order is limited to 5000.

    I don’t understand why it can’t retrieve all orders.
    Did you observe that too ?

  • Nhilas

    Hey Steve,

    First of all thank you so much for all the work you do, you’ve helped me so much both in EvE and irl, as I’ve discovered I have quite the hard-on for data, haha.

    I was wondering if it’s possible to extract all orders from a citadel? I’m mostly interested in finding the total market volume for any item, currently I see I can get the volume for the cheapest order. Also, how often does the sheet refresh?

    Again thank you for your work! I voted for you, happy you got in CSM XII!

    • Nhilas

      Derp I now saw that you actually do pull “all” market orders from a citadel. I’m still curious though, is there any way to pull the historical volume from a citadel?

  • Tom dos

    Hi Steve,
    the url in the code.gs does not link to your article. Docs instead Sheet…
    ….hope it works for me and i can do Planetary stuff again…
    Thank you for the developement !

  • Tom dos

    I get this error code…(citadel number changed to 999x)

    Fehler bei der Anfrage f├╝r https://esi.tech.ccp.is/latest/markets/structures/999999/. Folgender Code wurde zur├╝ckgegeben: 403. Gek├╝rzte Serverantwort: {“error”: “token not valid for scope(s): esi-markets.structure_markets.v1”}. Verwenden Sie “muteHttpExceptions”, um die vollst├Ąndige Antwort zu lesen. (Zeile 79).

    How do i get the citadel number exactly ? From in game ? Or do i have to do another call ?

  • Tom dos

    Would i have todo all the ESI Stuff in code.gs ? Like:

    function getCharWallet(character_id) {

    var config=getSetup();

    config=getAccessToken(config);

    var url = ‘https://esi.tech.ccp.is/latest/characters/’+character_id+’/wallets/’;
    var parameters = {method : “get”, headers : {‘Authorization’:’Bearer ‘+ config.access_token}};

    var jsonFeed = UrlFetchApp.fetch(url, parameters).getContentText();
    var json = JSON.parse(jsonFeed);
    var wallet=[];
    wallet.push([‘balance’,’wallet_id’])
    if(json) {
    for(i in json) {
    var wallet=[json[i].balance,
    json[i].wallet_id,
    ];
    wallet.push(wallet);
    }
    }
    return wallet;
    }

  • GTD

    I found it much easier to use GESI from https://forums.eveonline.com/default.aspx?g=posts&m=6878586
    Doesn’t require installing Postman.

  • Christian Heine

    Is there a way in Linux-python (using Raspberry) to create refresh tokens?
    I have copied and modified a working code, only that it requires manual pasting in of the code after authentication. Some way to handle the login automatically in python?
    See link for code: https://www.reddit.com/r/evetech/comments/6i8oc6/esipy_and_auth_simple_requests/

  • Michel Arendt

    I’m using the code on one sheet on Google Sheets and it’s working fine! However, tried to create another sheet but the same code doesn’t work… it returns the following error:

    Request failed for https://login.eveonline.com/oauth/token?grant_type=refresh_token&refresh_token=Iqoh…DKUy returned code 400. Truncated server response: {“error”:”invalid_client”,”error_description”:”Unknown client”} (use muteHttpExceptions option to examine full response) (line 47, file “Code”)

    Any ideas? It’s all setup as my other sheet.