Monthly Archives: April 2017

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.