Author Archives: Steve Ronuken

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)

Ore compression calculator

I’ve updated the compression calculator with a couple of new features, and stomped out a bug.

The bug was it had the wrong volume for some ores. (oops. it was hooked up to an old database.) That’s now sorted, and hooked up to the DB which updates when a new version comes out.

New Features:

Output values: You are told how many of each mineral you should get out in the end.

Ore selection: you can choose between all ores (the default) and highsec ores only.

 

Are other options for ore selection useful to you? Would you like to be able to specify nullsec only? Lowsec only? Be able to pick and choose, with max limits for certain kinds? So only 15 compressed spod, no arkanor but as much of others you want? Presets are simple from an interface perspective, full customization is somewhat more painful.

The backend, now that I’ve re-familiarized myself with it, is simple enough to update.

As always, the code is on my github respository and is can be freely used. However, it does require an external third-party library, which makes it a bit more painful to install. Especially if you’re on shared hosting (probably impossible then)

EDIT:

Nullsec only, and lowsec/nullsec (for the rig) filter added.

Bug stomped wrt Dark Ochre

CSM voting suggestions

I am, of course, suggesting that you put me (Steve Ronuken) first on your ballot. I want to be elected after all 😉

I would recommend you read the campaign posts, maybe do a little more research.

 

So, onto other candidates to consider:

Third party Candidates:

Highsec Candidates:

Looks like we’ve got people forming a part this time. Which is good. Gives them a chance to share the votes and get at least one of them elected.

Incumbents:

I’ve worked with these people before. They’re competent. (Don’t take not being here as not being competent. I just don’t want to overload people.) They’re less likely to need your vote, but it’s not a bad idea putting them further down.

 

Anyway, Vote when it opens. I’d recommend putting multiple people on your ballot (me at the top 😉 ) That way, you get the most value out of your vote. See a previous post on how the system works.

CSM Stuff – The MER changes

If you’ve been paying attention since the second CSM summit happened, you’ll probably have notice that there’s been some controversy kicked up by a change which is planned for the MER (Monthly Economic Report)

The short version is: Aryth asked for the figures to stop being on a regional basis, out in nullsec, because it exposed information which isn’t available anywhere else; the information being useful for keeping an eye on what people are doing, in a way that can’t be countered.

For a real world parallel, in war-time, people don’t publish their GDP figures.

Was this a self-interest request from Aryth? Yes, of course it was. But just because the person making it has their own interests in mind, doesn’t mean it’s inherently wrong.

When it came up, it was at the tail end of a session, and not extensively discussed. Aryth just asked that it be considered by CCP, and suggested that the data be presented in quadrants for nullsec, rather than regions. Lowering the granularity, rather than total removal.

I fully admit, when this came up, I didn’t put in any arguments against it. I can fully understand where Aryth was coming from, and I was working on the idea that it’d be reduced granularity, rather than total removal. Still allowing for trend analysis, but not letting specific groups be targeted. (Yes, I know there are sub-regional groups out there.)

I didn’t expect the level of backlash against it. (I did expect some. This is eve. Change the color of a couch and you get people saying the sky is falling.)

I have passed along to CCP that people have concerns about this. I’ve probably been a little more combative than I should have been, when asking people why they wanted the data. The why is important, because “Because I want it!” is a very bad argument. As is “Because we used to get it.” (Remember, it’s not me you have to convince. It’s CCP. Who have accepted that Aryth’s request has merit.)

I’m not ignoring you. I have passed on concerns to CCP. What I can’t do is tell you “Things won’t change.” Because the CSM cannot tell CCP what to do. Not one member, not the whole council. All we can do is collate and present the arguments.

CSM Stuff – The Voting System

You’ll have seen (probably) that the elections for CSM 12 are coming up in the near future (Voting starts on the 6th of March). I’m not going to go into details for what the CSM is (There are resources out there.), or who to vote for (Me, obviously 😉 )

What this post is about is the voting system itself. The elections use what’s known as the Wright Voting System. The key part of the system is that you don’t vote for a single candidate; instead you vote for a number of candidates, in the order you’d like to see them elected.

If your first candidate doesn’t get enough votes to get elected (we’ll get to this), then your entire vote gets transferred to the next on the list. If they get more votes than they need, then part of your vote transfers down. So if they get double the votes, then 50% of your vote transfers (and 50% of everyone else’s who voted for them. All going to the next person who voted)

There’s a quota to get elected. This is the total number of voters, divided by the number of seats plus one, plus one. So if you had 20,000 people voting, and 10 seats, that’s (20000/11)+1 or 1819 votes.

This does make things easier for large blocks, such as Goons, as they don’t need to make sure they split up their votes to make sure they get two people. You can just have people vote a set ballot, and let the system take care of it. If you have enough votes, they just transfer over.

It also makes things easier for more marginal candidates. If they arrange between themselves to have people who support them support someone else too, then the votes aren’t going to be totally wasted.

Here’s the key part: If there aren’t enough people hitting quota in the first round, you knock off the person with the fewest votes, transfer all the votes to the next person on the ballot, and start over. If there were people who only voted for the eliminated person, their vote is ignored, and the quota is recalculated, so you have to hit a slightly lower number.

Ideal world: you vote for the people you’d like to see on the CSM, in the order you’d like to see them elected. If lots of people agree with you, then your vote will help multiple people get elected. If a smaller number (but still enough) agree, then your vote gets one person on.

An example would be: There are five wormhole candidates. None of them would attract the votes to get elected. But if they all have everyone voting for them also vote for the others (in whatever their order they like) then whoever gets the fewest gets eliminated, and the votes redistributed. If there are enough votes to elect 2, and they all vote some combination of the list, then there are 2 wormhole CSM. It handles the organisation for you. 😀

This is a total pain in the ass to do manually. Thankfully, there’s no need to do so. There’s software to do it for you. CCP have made it available. This is how the election for CSM 11 would work out, with 10 places available. It’s the full audit log, so you can see how each round works out. (I’ve pre-eliminated Apothne, which makes a few differences.)

The only way to not be represented, with a STV system, is to not vote. (Or to be so marginal that there aren’t enough other people who think like you)

Memoirs – Flying

(Fiction! Yay!)

I guess this is the key feature of being a capsuleer. Piloting a ship, flying with just your mind.

I’ve been told the experience is different for everyone, so I can only speak for myself. It’s a hard thing to put into words, but I’ll try.

Have you ever driven? Where you don’t think about steering, you just do it? Where you’re one with the vehicle? And if you start thinking about it, everything goes to hell? That’s what flying is like for me. Achieving that state of mind where you just act, rather than thinking. There’s probably a word for it, but not one I know. I don’t think about going faster, or think “I should set speed to a hundred meters per second”; instead I just travel at the right feeling speed. Sure, it isn’t as precise, but it works for me. You get used to it, knowing how far away things are, and travelling there. Or twisting away from danger.

Some ships make me feel sluggish, others fast and nimble. I know some people find it hard to work with camera drones, your viewpoint no longer connected to your body, but it feels natural to me. When I want to look at something, I just do. While also knowing where I am, and what’s around me.

If I’m flying a long time, it takes a while to get used to my body again. I’ve seen some people who’ve spent long times in micro gravity have trouble getting used to the idea that things don’t stay where they put them. It’s similar, but on a bigger scale. Walking is harder than most people realise. And having to move yourself, to get a better view of something. Bit of a mind fuck, really. And kind of limiting.

Actually flying? There’s nothing like it. That feeling of power. Sure, there’s pain, when your ship gets shot to pieces around you. From an itch, to a deep burning pain. But you learn to wall it off. Keep thinking clearly, while your body is falling to pieces around you. The feeling of weakness, as someone drains your energy. Being stuck in molasses when you’re webbed. Explaining it is hard. There aren’t words for it. I guess, eventually, there may be. But when you’re a capsuleer, you just know. Though it makes some conversations hard to have. Imagine having to explain taste, to someone who’s only ever eaten the one food? It’s an overwhelming experience. But one I wouldn’t give up for anything.

Memoirs – Humanity

(Fiction, yay 😀 )

Am I still human? That’s an interesting question. I guess the technically correct answer is no. I’m not. This body isn’t born, it’s made. My brain isn’t grown, it’s forced into a state when my last body died. While it’s probably now a match for my original genetic structure, it didn’t use to be.

Technically, I’m an infomorph. A being of mind, that inhabits various bodies. That’s hardly human.

But I still think of myself as human. Not post human, as some capsuleers believe. Not better than the baseliners who surround me. Just different. Sure, I have plastic bones, plugs in my back and more metal in my head, but I still feel. I still bleed red blood.

 

Maybe if I spent more time in my capsule, I’d think differently. Maybe I’m wrong. But surely it’s how you feel, that determines your humanity?