A Lifelong Quest

This is not exactly a technology post, and it’s not exactly a gaming post, and it’s not exactly a (personal) history post, but in a way it’s all of those things rolled into one.  Let me start by telling you a little story.

When I was somewhere in the neighborhood of 15 years old, our family got a new computer: a Commodore 64, which was, at that time, state of the art.  I always thought that we bought it specifically for me, but my father corrected me a few years back, telling me that he originally bought it for himself, but he couldn’t really figure out how to work it, so he figured he’d see if I had any better luck.  I did, as it turned out, and it was the beginning of my programming career.  I think that pretty much anything you do as a career (as opposed to just a job) has to start out with you doing something for fun.  Otherwise you’re just in it for the paycheck.

The first program I ever wrote (which was in BASIC) looked like this:

10 PRINT “MY NAME”
20 GOTO 10

The second program I ever wrote was a D&D character generator.

Now, I tell you this story to let you know exactly how long I’ve been trying to program a D&D character sheet.  My obsession has carried me across 35 years of technology, and it’s driven many of my decisions as to what to learn.  I quickly learned I had to give up on BASIC (too slow), so I taught myself assembly.¹  I dove very deep into the formula languages of first Lotus 1-2-3, then Excel, and now Google Sheets, so that I could do spreadsheet-based character sheets, and I taught myself VBA when that wasn’t enough, and now I’m almost sort of proficient in Javascript for the same reason.²  The first database I ever learned—dBase III, that would have been—I didn’t learn for the purpose of making character sheets, but it was the thought that it might be used for that purpose that drove me ever deeper into the language.  Same with SQL.  I’ve done very little GUI programming, but most of what little I have done—Delphi, and wxWindows, and Django, and Gantry—was mined for what it could teach me about how to make interfaces for D&D players.  I’ve written DSLs for dice-rolling, and extensions to Template Toolkit, and I even tried to write a “better” spreadsheet in Perl once, all so I could program the perfect character sheet.  If I ever get around to writing my SQL-language-extension, which will probably be done in Perl 6, one of the first things I’ll do with it is integrate classes with DB tables for aspects of D&D characters.

And, the sad part is, I’ve been doing this over and over again for 35 years, and it’s never worked properly.  There are a myriad of reasons for this.  A character sheet is a huge quantity of interrelated numbers with complex interdependencies, which make it almost perfect to render as a spreadsheet.  But the rules are just baroque and irregular enough to make it a breeze for the first 50% and practically impossible for the last 25%.  Contrariwise, the amount of dependent recalculation means that it’s a giant pain in the ass to do in a general programming language, unless you fancy trying to reinvent the spreadsheet wheel.³  The amount of data that needs to be stored, as well as the number of set operations necessary, mean that a database solution (such as SQL) is pretty attractive, for certain aspects.  But trying to do that much recalculation in a database language is even more terrifying than trying to do it in Perl or C++, and most of the parts Excel can’t handle, SQL is even worse at.

The thing that makes a database application or language really attractive, though, is the place where spreadsheets really fall down: separation of code and data.  If I write a program in a general language, I have code and then, elsewhere, I have data.⁴  In a database application, the line may be a bit blurrier, but the separation is there, and the proof is, I can give you updated code, and that doesn’t change your data a whit.  Not so with spreadsheets.  With those, the code and the data are one piece.  If I give you an updated spreadsheet, it comes with its own data (which is always blank).  But say you’ve already got a character sheet: it’s full of your data—you know, for your character.  Hell, the reason you wanted the upgrade in the first place was no doubt that you found a bug in my code, or maybe I just added a new feature that you really need.  But now there’s no way for you to migrate that data out of the old sheet and into the new.

Now start multiplying that problem.  If you’re a D&D player, you probably have lots of characters.  And how many people are using this spreadsheet thingy anyways?  My very first fully functional Excel spreadsheet was only used for one character each by 3 players (i.e. the 3 players in that particular campaign I was running)—and myself as the GM, of course—and it was a nightmare every time I updated the sheet.  A D&D character is not a huge amount of data, especially not when compared to big data or even the database of a middling-sized business, but it’s also pretty much nothing but data.  You don’t want to have to re-enter all of it every time I fix a bug.  To use the appropriate technobabble, this is a separation of concerns issue, and more specifically having to do with the separation of code vs data.  Of course, it’s quite fashionable these days (among technogeeks, anyway) to argue that code and data are the same thing, but I can only suppose that the people making those arguments never had to release code updates to users.⁵  I only had three users and I was going crazy trying to figure out how to separate my code from my data.

(To delve a bit deeper into the technical side of the problem, what I really want is for someone to invent a spreadsheet that’s actually just an interface into a database.  The spreadsheet programmer “ties” certain cells to certain columns of certain tables in the database, and the spreadsheet user is only allowed to enter data into those specific cells.  There could be multiple rows in the spreadsheet, corresponding to multiple rows in the table, and it would be easy to add a new one.  Sorting or filtering the rows wouldn’t affect the underlying data.  The database back-end might need some tweaking as well—what if the user enters a formula into a data cell instead of a constant?—but ideally it could use a standard datastore such as MySQL.  Somebody get on inventing this right away, please.  I don’t ask for any financial consideration for the idea ... just make sure I’m your first beta tester.)

But the problems with realizing the perfect computerized character sheet aren’t all technical.  A lot of it has to do with house rules.  If you’re not familiar with D&D, this may not make sense.  You may think house rules are simple little things, like getting cash when you land on Free Parking in Monopoly.  But RPGs (of which D&D is the grandaddy of them all) have a whole different relationship to house rules.  House rules can change anything, at any time, and the rulebooks actively encourage you to use them.  “GM fiat” is a well-entrenched concept, and that includes pretty much everything involved in character creation.  2nd edition D&D said only humans could be paladins, but many GMs threw that rule out.  3rd edition said multiclassed characters had to take an experience point penalty, but a lot of groups never enforced that.  What if a GM wants to change the value of some bonus granted by some feature? what if they want to raise the maxima for something? or lift the restrictions on something else?  What if they want to change the frequency of something, like feats gained, or ability score increases?

The complexity—but, more importantly, the prevalence—of house rules is death on a character sheet program.  In a fundamental way, programming is codifying rules, and if the rules aren’t fixed ...  Even when I’m noodling around with designing a character sheet that will only be useful for me and my friends, I still hit this problem, because we don’t all agree on what the house rules should be, and we’re constantly changing our minds.  Imagine how much more difficult it is to come up with something that will be useful to all gamers: there’s a reason that D&D has been around for over 40 years and no one has yet solved this problem.  Oh, sure: there are lots of attempts out there, some done with spreadsheets, some as database front-ends, and some as general programs.  But this is not a solved problem, by any means, and all of them have some area where they fall down.  Again, the prevalence of house rules in roleplaying is a crucial thing here, because it means that you can’t just say, “well, I’ll just make a program that works as long as you’re not using any house rules at all, and that’ll be better than nothing,” because now your userbase is about 4 or 5 people.  It’s hardly worth the effort.

So it’s not an easy problem, although I often feel like that’s a pretty feeble excuse for why I’ve been working on what is essentially the same program for 35 years and never managed to finish it.  But I’m feeling pretty good about my latest approach, so, if you’ll indulge me in a bit (more) technobabble, I’ll tell you basically how it works.

First, after a long hiatus from the spreadsheet angle, I’m back to it, but this time using Google Sheets.  Although I’ve already hit the complexity wall⁶ with ‘Sheets, it took much longer to get to than with Excel.⁷  Plus it has a number of things I never had with Excel:⁸ you can sort and filter in array formulae, and you have both unique and join.  Much more intelligent handling of array formulae is the biggest win for me with Google Sheets; in many other areas (particularly cell formatting) it still trails Excel, to my annoyance.  But it mainly means that I never have to program extensions, as I did with Excel.  Plus, when I do decide to use some extensions (mainly to make complex/repetitive tasks easier), I get to program in Javascript, which is almost a tolerable languaage, as opposed to VBA, which is decidedly not.  I still have the code/data problem, but I’ve come up with a moderately clever solution there: all my “input cells” (which I color-code for ease-of-use) don’t start out blank, but rather with formulae that pull data from a special tab called “LoadData,” which is itself blank.  Then there’s another tab called “SaveData,” which contains a bunch of formulae that pull all the data from the input cells: every input cell has a corresponding row on the “SaveData” tab.  When you want to upgrade your sheet, you can rename the existing sheet, grab a new (blank) copy of the upgraded sheet, go to “SaveData” on the old sheet, select-all, copy, go to “LoadData” in the new sheet, then paste values.⁹  (And again: I coded up a little Javascript extension for the sheet that will do all that for you, but you still could do it manually if you needed to for any reason.)  Now, this isn’t perfect: the biggest downside is that, if you happen to know what you’re doing and you actually stick a formula into an input cell, that’s going to get lost—that is, it’ll silently revert to the actual current value—when you upgrade your sheet.  But that’s moderately rare, and it works pretty awesomely for the 95% of other cases where you need to transfer your data.  I still miss the ability to do database ops (e.g. SQL),¹⁰ and I absolutely miss the ability to make classes and do inheritance, but so far I haven’t found any problem that I can’t solve with enough applications of match and offset, hidden columns, and tabs full of temporary results.  (To be fair, I’ve postponed solving several problems, and I have a lot of “insert arbitrary bonus here” input cells, but those actually help out in the presence of house rules, so I don’t mind ’em.)

So I feel like I’m closer now than I ever have been before.  Sure, this one will only work for D&D, and only for one edition of D&D,¹¹ but if I can make it work for pretty much any such character, that’ll still be the closest to fulfilling my dream that I’ve achieved thus far.  I’ve got a lot more testing to do before I can make that claim, and several more character types to flesh out (I haven’t done very much with spellcasters at all, and monks are alwyays a giant pain in the ass), but it looks promising, and I’m starting to get just a little bit excited about it.  Which is why I wanted to share it with you.  And also because it’s been consuming a fair amount of my free time lately, so I thought it might be good to get some details out there for posterity.  Maybe one day, if you’re a D&D player, you’ll be using a version of my character sheet on your laptop at the gaming table.

Or maybe I’ll still be working on it in the nursing home.  Either way, it should be fun.

__________

1 For the 6510, this would have been.  Although I didn’t really have any concept of that at the time; in fact, I really only know it now because Wikipedia just told me so.
2 That is, because Javascript is how you write extensions for Google Sheets, just as VBA was how you wrote them for Excel.
3 Which, as I mentioned, I actually tried to do once.  I didn’t fancy it.
4 Let’s pretend that where “elsewhere” is is not really important for a moment.  The truth, of course, is that it’s vitally important.  But these are not the droids you’re looking for.
5 Which is not unheard of.  A lot of code out there in the world doesn’t really have data entered by a user, and quite a chunk of it doesn’t even have “users” at all.  And a lot of programmers work exclusively on such code.  For those folks, this is an interesting philosophical debate as opposed to a self-obvious truth.
6 By which I mean the point at which a spreadsheet fails to recalculate certain cells for no apparent reason.  Generally if you just delete the formula and re-enter it, then everything works.  But it’s nearly always intermittent, and thus useless to complain about or report.  Every spreadsheet I’ve ever worked with has a complexity wall, and the character sheet app always manages to hit it eventually.
7 To be fair to Excel, that was a decade or two ago.  It might be better now.  But I bet it’s not.
8 Again, it’s possible that Excel may have one or more of these features by now.
9 Well, except that Google Sheets currently has a bit of a bug with trying to paste values from one sheet to another.  But there’s a simple workaround, which is again a perfect reason to have a little code extension to do the steps for you.
10 Google Sheets has a query function that sort of lets you do pseudo-SQL on your data tables, but you can only refer to columns by letter, not name, so I consider it fairly useless.
11 Specifically, 5e, which I’ve talked about before on this blog.