Random table format wars

In general I am wary of introducing technological prosthetics into tabletop roleplaying games. Often, I think such tools are patches over needless rules complexity. I would generally prefer to adjust the rules so that they can stand efficiently without machine crutches. However, one place where I relax my attitude in this matter is the combination of results from several random tables. Occasionally I appreciate the slow, ritual process of rolling dice, consulting a table manually, noting a result, repeating this process over and over again, and then finally reconciling the complete set of results. However, most of the time reducing a 20 minute operation to one that takes mere seconds is a trade I will take. So, what tools exist for automating the juxtaposition of multiple random tables? Ultimately, I will advocate using a simple spreadsheet, which I argue has benefits in terms of both flexibility and simplicity over many other more specialized solutions, but first I will survey the alternatives.

To my knowledge, the other major players are Abulafia, Chartopia, and Last Gasp Choose Your Own Generator. Abulafia is basically a wiki that supports syntax for random tables. Abulafia’s major benefits are the ability to reference tables uploaded by others and web sharing. Chartopia is a specialized web app for random tables that seems like a work in progress. Both use their own format for encoding data. Last Gasp will generate standalone JavaScript tables that you can store entirely in a bookmarklet but requires some external solution to manage and recombine lists of table data. All of these are good tools but none of them quite scratch the itch of blending results from multiple tables in a flexible and easy to manage manner, at least for me. Chartopia may have emergent potential from social media participation, but so far lacks the user base and requisite functionality, such as intuitive hash tagging. Chartopia supports uploading tables as CSV files, but the process of creating a full generator ended up feeling cumbersome to me.

Using a spreadsheet, once you know a couple simple tricks, is flexible and quick, and the social media aspect seems easily handled with Google Sheets, Dropbox, Google Plus, Twitter, Reddit, or whatever. The main benefit for me of using a spreadsheet though is encapsulation. All the other solutions I have experimented with involve a mess of files, links, or other elements, which end up difficult to organize or share. In contrast, the method I outline here keeps all the subtables together in a single spreadsheet workbook file, with one column per table in the second sheet. This ends up interoperating easily with the most natural format for storing random tables, which is a plain text file having one result per line.

Basic Excel random tables

These instructions assume that you have basic knowledge of spreadsheet concepts, such as cell addresses and formulas. That said, to make this accessible, I try to be as explicit as possible, and apologize in advance for boring those with spreadsheet experience. Spreadsheet files (the files with xls or xlsx extensions for Excel) are called workbooks, and each workbook can contain multiple sheets. Beyond these basics, all you need to know is how to name a region of cells and how to use one, admittedly ugly, function to select a result randomly from a named region. To create a multi-table generator:

  1. Create a workbook
  2. Enter the table data into the workbook’s second sheet, one table per colum
  3. Name the column regions so you can reference them by name
  4. On the first sheet, reference the named regions using the ugly function

As a basic example, I will automate Telecanter’s excellent Magic Item Spur, the content of which is released under a creative commons license. This is a roll all the dice generator, meaning there are six tables corresponding to the classic handful of polyhedrals: 1d4, 1d6, 1d8, 1d10, 1d12, and 1d20. (The d10 table here is basically just the number, but I have left it exactly as Telecanter originally specified, for clarity of example.)

First, switch to Sheet2 within the workbook and enter the data. Check the lower left tabs to access the different sheets within the workbook (you may need to click the + button to create a second sheet). I left the sheet names Sheet1 and Sheet2 in this example so they match the Excel defaults. I use the first row for column names, but this is just superficial:

Excel example – enter random tables

Next, name the ranges for each column so that you can refer to them by name elsewhere. To name a region, highlight the column data and the type the name in the upper left cell address box—the text in the blue box below. Do this for all columns (there are six in this example).

Excel example – name region

Finally, switch to Sheet1, which will contain the randomly generated result. For each table, draw a result randomly using the following function, replacing TableName with whatever you named each column region:

=INDEX(TableName,RANDBETWEEN(1,ROWS(TableName)),1)

You can organize Sheet1 visually in whatever way you want. I have the generated content in cells B2 through B7 here, with labels to the left and credits below.

The formula in cell B2 (which resolves to distance in the result) is:

=INDEX(range,RANDBETWEEN(1,ROWS(range)),1)

The formula in cell B3 (which resolves to weapon in the result) is:

=INDEX(type,RANDBETWEEN(1,ROWS(type)),1)

And so forth.

Excel example – Sheet1 generator with formula

That’s it.

Now, every time you recalculate the sheet, you will get a new result. In Excel, press F9 to recalculate the sheet. In Google Sheets, reload the page.

Share, or embed in blog post

Upload this to Google Sheets, and you can share it with others, or embed the result in a blog post or other web page using the following iframe code.

<iframe src=”https://docs.google.com/spreadsheets/d/1am-3-CfNcIV4ICCr6jysJckVr03iWBphdeIaeSOtEBY/edit” width=”600″ height=”600″></iframe>

Just update the URL. Every time you reload the page that includes the iframe code, you will get a new result, as you will see below. (Try reloading this page.) I have highlighted the random content area in red to make it clear for this example.

Edit 2018-09-18: I find the embedded iframe presents strangely on mobile browsers, at lease Safari and Chrome for iOS. So maybe link to the spreadsheet rather than use embedding. Click here for the spreadsheet mentioned in the above paragraph.

Use on phone or tablet

This approach is portable, both in the software compatibility sense and in the will work on your phone sense. I tested these sheets on the Google Sheets, Numbers, and Excel apps for iOS. They all work, even when offline, though there are some minor differences in the user interface, and the free version of Excel for iOS prevents saving. Generally, to generate a new result using a phone or tablet spreadsheet, close and reopen the document. The Google Sheets app seems to be the most full-featured and flexible, but really as a simple viewer any of these works.

Opening this file unmodified in the Google Sheets app on my phone presents this interface, which is just about perfect for use during play:

User interface on a phone

Deleting or editing names for regions

Once you have created one of these, it is relatively straightforward to make a copy the workbook file and edit the copy to make another generator out of different tables. To do this, you may want to delete or rename existing region names. I explain how to do this next.

In Excel, you can delete existing names for ranges using Insert > Name > Define Name, highlighting the name, and then clicking the minus button.

Excel – region name menu

Excel – delete region name

In Google Sheets (access via Google Drive on the web), the relevant menu is Data > Named ranges… and at the time of this writing looks like:

Google Sheets – Delete region name

(Just do a web search for “named range” and your spreadsheet software of choice if using another tool.)


Comparing solutions

Okay, now that you have seen a simple proof of concept example, how does this compare to the other solutions?

  1. It functions without the network
  2. It only relies on standard spreadsheet functions
  3. Easy to share by uploading to Google Sheets
  4. Easy to organize using folders in Google Drive
  5. Easy to copy one-per-line text files into columns
  6. Keeps a collection of related tables together in one file
  7. No reliance on domain-specific language
  8. No reliance on continued existence of whatever cloud service
  9. Easy to embed in a blog post using iframe (will regenerate on each page load)

That seems like a pretty compelling list of advantages to me. A while back, I considered and decided against using a spreadsheet, but I have now changed my opinion. For a few more examples:

  • The Vornheim aristocrat generator is one of the classic examples for me of this kind of multiple table generators. Here is an automated excel version (content used with permission). Buy Vornheim; it’s great.
  • Random Devilspawn from this post (note how easy it was to embed the generator at the bottom of that post as well).

The other solutions are mostly unitaskers, unlike Excel, which is a useful tool in its own right. And keeping all the subtables nicely contained in a single workbook file is extremely helpful, especially in comparison to the mess of files I have to automate, say, some of the chapters from Seclusium (such as this chapter that is basically a magic item generator).


Adding Functionality

Though the above example provides all the functionality most generators require, spreadsheets have almost unlimited flexibility, and you can make any particular generator as complicated as you want, if your time is worthless. I tried to keep the example above as simple as possible for ease of use and portability, but there are a few other functions that might be worth the effort.

RANDBETWEEN (for dice values)

For slightly more complex generators, two other functions may be useful. The first is RANDBETWEEN(), which can serve as a basic dice roll. For example, =RANDBETWEEN(1,6) yields 1d6. =RANDBETWEEN(1,6)+RANDBETWEEN(1,6) yields 2d6 (and so forth). Use this function to instantiate values for elements such as number of monsters appearing.

CONCATENATE (for joining two random results into one cell)

To join two or more elements so they occupy a single cell, use CONCATENATE(). For a simple example:

=CONCATENATE(“Number appearing: “, RANDBETWEEN(1,6))

I use this in my Devilspawn generator to join the number of attendants and the description of attendants into a single result (check the contents of cell B10 in the first sheet).

12 thoughts on “Random table format wars

    1. Brendan Post author

      @Scott

      What doesn’t work for you?

      All the spreadsheet functions should be identical on Windows.

      Probably easiest to download one of the example workbooks as an xlsx file from the Google Sheets links and open it using Windows Excel.

      That is, go here:

      https://docs.google.com/spreadsheets/d/1am-3-CfNcIV4ICCr6jysJckVr03iWBphdeIaeSOtEBY/edit

      And then: File > Download as > Microsoft Excel (.xlsx)

      From there, you can change the contents of the random tables and tweak the references on Sheet1 until it fits whatever set of tables you want to automate.

      The menu to access the name manager may be different in Windows, but that is not critical functionality to begin with.

      Or, you could just add it to your own Google Drive and do everything online using Google Sheets. That has the benefit of working similarly on different operating systems. I prefer creating the workbooks in Excel, but Google Sheets should work too.

      I would be glad to answer any specific questions you have.

      Reply
  1. Brian H Kaufman

    Thanks for posting this! Working on using this with equipment packs to make a character generator for my house rules.

    Reply
  2. Ancalagon TheBlack

    This is very helpful, thank you. Now if I could figure out the “if then” functionalities – so if your first table is “race” and you get dwarf, then your height range would be different than if you got elf…

    Reply
    1. Brendan Post author

      @Ancalagon

      Here is one way (for determining height in inches based on race using arbitrary values I just made up):

      =IF(EXACT(race,"dwarf"), RANDBETWEEN(48,60), IF(EXACT(race,"elf"), RANDBETWEEN(60,66), "Error: race unknown"))

      This relies on a cell with label race, but you could also just use an absolute cell address, such as A1.

      Unfortunately, I don’t think there is a way to do a case statement in excel, so you either need to do a nested IF like this or use a one of the table lookup functions (VLOOKUP, HLOOKUP, or LOOKUP). The second approach is probably the way to go for ease of interpretation and maintainability.

      Reply
  3. Chartopia (@d12dev)

    Glenn from Chartopia here. Since your July post, the Chartopia editor has undergone some significant changes that should make it a lot easier to make generators. Less ‘cumbersome ‘ as you put it. I’m curious as to your mentioning of hash tagging, and what your expectation is of such a feature. It would be great if you got in contact so we can make a list of all your recommendations and feature requests (the docs page on Chartopia is quite comprehensive now, if you need an idea of the features). We want to make Chartopia the best random gen tool out there, so feedback is really appreciated. (You can now embed Chartopia charts into your website)

    Reply
    1. Brendan Post author

      @Chartopia

      I haven’t forgotten about this but I also haven’t had a chance to look at it again systematically. I do plan to check out the new features and potentially update this post at some point, however.

      Reply
  4. OSCAR W DIXON

    I want to use this to create a shop, is there a way to set likely hood of certain items being chosen?

    Reply

Leave a Reply