I’ve been working on a character keeper for Offworlders to demonstrate using Google Sheets for online character keepers. In the Basics post, we ended up with something like this: https://goo.gl/JR1zS1. If you haven’t been following along, you may want to start at the Introduction post.
This sheet has space set aside for most of the information that a player would want to record for their character. You’ll notice, however, that most of that information will have to be entered manually, or at least copied and pasted over from the digital document. Most of the time, when you copy from a PDF, you’ll also encounter a tricky hurdle for spreadsheets: line breaks. When you paste it into your spreadsheet, if you don’t enter it directly into a single cell, it will spill over into the next ones, and if you do enter it into the cell directly (through the formula bar), you will still have to go back and delete those line breaks, which is a hassle!
In this article we will look at some ways to reduce manual input of character info to increase the ease-of-use of the keeper and decrease the length of character creation. Feel free to follow along with your keeper, or start with the basic one I’m working on and go from there!
I’m going to go top-to-bottom while covering these features. The first function I want to cover is data validation. Basically, for our purposes, this allows us to create a drop-down list for folks to use to pick from a list. For example, in our Offworlders keepers, we can list the Classes we want to have available. In my sheet, I have left an open box next to my Class heading. If you right-click a cell, at the very bottom of the list you’ll find the data validation option. Go ahead and click that, and a SCARY INTIMIDATING WINDOW will pop up. Don’t worry! I’ll break it down for you:
Speaking of ranges, there’s one more shortcut we can use here. First I will highlight my cells with the Class names in the Data sheet. Then, I will right-click, and choose Define named range. I’ll call this range “classes”.
Now I want you to imagine a truly terrifying scenario...a player shows up to your game of Offworlders and *GASP* they haven’t read the rules yet! Okay, maybe not so scary for a game as easy to pick-up-and-play as Offworlders, but nonetheless...the first mechanics-related choice they have to make is revealed as a list for them. They don’t need to go read through the rules to know that these are the Classes they can choose from.
When @greininghaus polled some of our Gauntlet Slackers about the best features of online keepers, one of the most commented-on was the IMAGE function. You see, there are two ways to get an image onto a sheet: clicking Insert>Image from the taskbar or using the IMAGE function. What’s great about the function is that it secures the image within the cell boundaries and maintains a predetermined size. Let’s leverage that capability to change up our Look heading.
First let’s find an image on the internet we’d like to use and copy its URL. Let’s use this piece by Olivia Gulin for example: https://images2.imgbox.com/33/df/iCEREKaC_o.png
Next, clicking on the Look cell will show in the formula bar (that’s the one with the “fx” symbol next to it) just the text we have put in: “Look”. We’re going to delete that and replace it with this:
Yay! A...tiny picture. That’s because by default the image will be resized to fit in the cell while maintaining aspect ratio. Let’s make this row a little bigger so that we can see our image clearer. For now I will resize it to five rows’ height.
Well, that works just fine for us, the illuminated folks who now know how the IMAGE function works. But we don’t want our future players to have to read all of these articles just to use the sheet. How can we make this easier for them? I’ll add a second row beneath the image cell and indicate that the URL should be entered there. Then I’ll edit the function to say:
Next let’s look at the Attributes. You’ll see here that I have chosen to abbreviate these to make them fit together more easily. In doing so, I have sacrificed some clarity, but we can alleviate that by adding a note. Right click on one of these abbreviations and choose Insert note. You can add as much information as you’d like in this note, but I’m content with just defining “STR” as “Strength”. Hovering over a cell with a note, as indicated by the marker in the corner, will show the note in a window.
Moving on, we have Skills. You know what to do there. Abilities, however, may prove to be a little more complicated. There’s a couple of problems to tackle here.
Firstly, different Classes have different Abilities, so it’s not so simple to create a drop-down list for them. There is a solution, but it will require us to learn some new functions. Before we do, let’s set up a table with each of the Ability names in our Data sheet, next to our Class list. It will end up looking something like this:
Now our second problem comes into play. As a GM and a player, I can’t be bothered to just memorize exactly what the Hardy or Brute Abilities do, so a rules reference here is handy. We can use our INDEX/MATCH skills here to similar effect! You’ll want to add the description of the Abilities on the same row as their names, in the same order you’ve listed them, like so:
Okay, now you may be thinking, “Geez Mike, that’s a lot of work to save not a lot of time!” Unless you’re a programmer, in which case you’re probably really jazzed right about now. But you may still have a point. In Gerrit’s slack thread, @GauntletRPG pointed out the downside with these intricate and advanced keepers with all of their automated bits and bobs: they’re prone to falling apart under even light stress. For example, if you want to use an alternate class or playbook, or copy over cells to other parts of the sheet.
Additionally, if there are Abilities that require you to make a choice within them or aren’t otherwise symmetrical (good luck following these steps for Masks’ playbooks, for example), trying to edit those into the cell’s text will require you to copy the cell’s contents, paste them back into the same cell as “values only”, and edit from there. That’s a little bit beyond the scope of technological aptitude that I expect from players using my keepers. If you find yourself in that situation, I may suggest that you just leave your abilities and descriptions in a table format on a separate sheet in your keeper. Players can then just copy the contents of those cells into their character sheets. If necessary, you may need to ask them to paste the text into the formula bar to maintain your formatting.
Now, you’ll notice that those INDEX/MATCH formulas that don’t have Abilities chosen to reference yet return an unsightly #N/A error message. If you want to hide that, you can wrap your formula with an IFERROR function, like this:
If your eyes haven’t fully glazed over just yet, there’s one more feature I would like to share with you that’s relatively new to Google Sheets: checkboxes! To insert a checkbox into a cell, highlight it and click Insert>Checkbox from the taskbar. Check out how I’ve used these for our Advancement heading:
*As a footnote, I want to mention how handy locking cell references can be when it’s time to copy and paste your cells for new characters’ sheets, etc. Especially since I forgot to do so in the screenshots for these formulas. Let’s say I’m copying over the INDEX/MATCH formula for my first Ability slot down to the next one. We move down two rows, so Sheets automatically adjusts the reference down two rows as well: C4 becomes C6. Handy, except for when it isn’t. So we can lock the reference to that cell so that when it gets copied over it doesn’t move. You lock a cell reference by putting a $ symbol in front of it. So, if we change the reference to $C$4, when we copy it over it will still reference C4. But, what about when we want to copy over this whole sheet to the next column for our other characters? We don’t want to be referencing C4, but rather H4, and so on. In that case, we will change the reference to C$4 - the 4 stays the same, but the column automatically adjusts. Also, all of our named ranges will stay the same. Again, this is usually a good thing, but you may need to change some of those formulas to reflect those changes (references to “character1” come to mind).