Google Sheets – Updating from a Menu

Something which has been a problem people have run into, is that google sheets have a problem with automatically updating figures from custom functions. You’ll often find functions which suggest having a useless reference to a cell which you can then use to trigger the updates.

This is obviously somewhat suboptimal. So as a potential solution for you good people, I give you the menu based price list.

Google Sheets allow you to have custom functions, but you can’t write from them, only return data. However, app script called from a menu option can write to its hearts desire. The downside is that you can’t pass it any parameters. So this means you must have the input data stored somewhere it can retrieve by name, and somewhere to output; named range is one option, but I’m taking the lazy option and using sheets with specific names.

So, with the link above, you can see how to do it. There’s an example sheet you can copy.

As a little explanation:

The onOpen function adds menu in. It should be possible to update the data from here, but I ran into a bug there, so it’s purely on refresh.


The updatePrices function is pretty much the same as the one for use with a custom function, with a couple of key differences. First, you get the two sheets which are important. Clear the output sheet so we just append to it.

Get the region id from the cell A1 of the typeid sheet.

Now it just runs through the first column, from row 2 (so it ignores the region) to the last row. Deduping the list comes next. Sure you could get the data twice, but this is more efficient.

Then it runs through the typelist 100 ids at a time, getting the json, running through it, and appending the results to the sheet.

Memoirs: Death

For most capsuleers, Death is a constant companion. If we’re not dying, we’re killing. If we’re not killing, we’re trading in the implements of death. If we’re not trading, we’re manufacturing new and improved ways of killing people.

Funny occupations for someone who’s functionally immortal, don’t you think?

I guess there’s some truth to the common perception that capsuleers are sociopaths who don’t care about baseliners. I mean I’ve personally caused the deaths of tens of thousands, if not hundreds. I don’t think I’ve hit the millions, but that may be wishful thinking on my part. Of course, they all had it coming (Except possibly my crew. But they’re well compensated). You take the coin from a pirate, you trade in slaves, and you give up any rights you have to life.

These days, I tend to keep a few steps removed from it all. A Merchant of Death, rather than a deathdealer. Are my hands any cleaner? Probably not. Still doesn’t interrupt my sleep. Only thing which does, is thoughts of my own death. Like with the H4-RP4 Kyonoke outbreak. Sure I had a backup, but that’s not the same. At least not for this me.

I guess it comes down to keeping your friends close, but your enemies closer. Death’s the greatest fear of a capsuleer. True Death, that is. So we flirt with it. Hold it close, wear it like a cloak, spreading it where ever we go. There are times that I think on the religions of my childhood, and fear the day it all comes crashing down.

Memoirs: Kyonoke Inquest

The inquest at the H4-RP4 facility. Kyonoke. That wasn’t a good time. I couldn’t not go, not when I received the invitation. If I hadn’t received one, I’d have kicked up whatever trouble I could, until I got one; But I have to admit, I was still terrified. I mean, I live in a bubble. My immune system isn’t the best, even against normal baseliner disease. And yes, I probably could have jumped out if I were infected, as long as it hadn’t progressed too far. Or reverted to a backup.

Backups aren’t something I like to talk about. Feels too much like dying. No “explode, then open my eyes elsewhere.” Knowing that another you went on, lived life, then died, before you you woke up, without any memories of that time. Thankfully, up to that point, I’d never had to use one. So yeah, terrified. But not quite enough to stop me going. I may not be the best Son of Matar out there but when my people are suffering and I have a chance to use what influence I have to help them, or at least stop it spreading to others, I didn’t really have a choice.

So with my backup up to date, and a clone body prepared, I set out. Preparations were fairly minimal. Embedded recording system. Best immune system I could get into it in the time I had. Features structured to gain some respect. A beard; greying of course, to simulate a patriarchal feel. A paunch, for prosperity, and to be less threatening. Tall, but not too tall. That kind of thing.

Other than a Cheetah, a fairly barebones crew on it, and a hold-out, that was me. Oh, and the research staff back home. I can’t provide the same resources as a State, but I’m not without some resources.

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)


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 as the callback url. Click on ‘create new application’.

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


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:

Access Token URL:

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 (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



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)


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.


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.