This content has been archived. It may no longer be relevant
While this may seem kinda odd to some of you folks, this definitely falls under the category of “Gaming Tools” so I thought I would post about it here. I promise – there’s more artwork on the way. In the meantime…
I found a fairly simple way to create a random generator in Google sheets and I thought I would write it up so that others could use it for gaming tools.
For the purposes of this tutorial we’re going to generate a very simple description of an NPC consisting of a race, an eye color, and a hair color. I like to make it feel more ‘natural’ by altering the sentence structure of the things I generate, so we will do that too. It takes almost no additional effort once you know how.
Step 1 – The Lists
The primary element of the generator is a series of lists. These are the base components for whatever you want to randomize.
In this little example, the lists are Column A – Hair Colors, Column B – Eye Colors, and lastly Column C – Races.
Next, you need a simple bit of code to select an item from each of these lists. Fortunately, I’ve copied that for you here:
This is the code copied directly from cell A6 of this sheet. It looks through the list from A8 down through the last the end of the sheet for an item.
Step 2 – Picking from the Lists – the Breakdown
What follows is a simple breakdown of the elements of the code that selects from the lists you created in the previous step.
(counta(A8:A) – This part tells the sheet how many items are in the list. That way you can make the list as long or as short as you like. You’ll notice that the eye color list is shorter than the other two, but the code needs no adjustment (aside from the letter for the column).
randbetween(1,(counta(A8:A)) – With the portion from above embedded, this portion is the part that actually picks the individual item.
=index(A8:A, randbetween(1,(counta(A8:A)))) – With the above portion embedded, this portion is the part that creates the list that the previous portion picks from.
I add the code to select an item from each list under the label I’ve created on row 5, after changing the cell addresses appropriately. So they break out like this:
- A6: =index(A8:A, randbetween(1,(counta(A8:A))))
- B6: =index(B8:B, randbetween(1,(counta(B8:B))))
- C6: =index(C8:C, randbetween(1,(counta(C8:C))))
Step 3 – The Sentences – Another List
This list uses some different code. I have labels on G5-7. The sentences break down like this:
- H5: =”There is a ” & C6 & ” here with ” & A6 & ” hair and you can see their ” & B6 & ” eyes from here.”
- H6: =”You see a ” & A6 & ” haired ” & C6 & ” with ” & B6 & ” eyes.”
- H7: = “A ” & B6 & ” eyed ” & C6 & ” stands here with ” & A6 & ” eyes.”
As is probably evident, the bold elements are the items that were selected in the previous step. We patch those elements together with some text. The format is simple: “Some text within quotes with spacing as needed “ + the & on either side of the selected items.
Step 4 – The Finale
As you can see, this creates a list of sentences with different structures. And since we can randomly select items from a list, we just add the same general code to C1: =index(H5:H, randbetween(1,(counta(H5:H)))) and we select from the different sentence structures. That way things don’t get repetitive.
And that’s it! You’ve randomly generated a description of an NPC. Obviously this is a super simple version of what you can accomplish with just two lines of ‘code’. With a little time and effort, you can create a good deal more depth to your description, or really, anything you want to generate.
If you would like to see this sheet (and/or make a copy for yourself) you can find it here.
Or, if you would like to see a slightly more in depth generator (along with other generators on other sheets) feel free to check out the public version of a sheet I created a while ago and update occasionally. You can find that one here.