This is a technical post for designing party games in Google Drive Spreadsheets. For purposes of this post, “party game” will refer to a casual tabletop game that uses a large number of cards as the main component, such as Apples to Apples.
A key part of testing party games in a spreadsheet is generating a random deck of cards based on a list of content you’ve created. Party games require designers to test and edit seemingly endless combinations of content to make sure it all works together. It’s nice to have all of this content on one spreadsheet so that every test is as frictionless and short as possible. There are several different ways to accomplish this, but none of them allow you to quickly test large amounts of content without generating duplicates. I’ve been working on a few different party games lately. When creating party games, it’s useful to create all of your content in a spreadsheet so you can easily organize, edit and playtest the game without having to constantly create new prototype cards.
The two common strategies I’ve seen are below. Each assumes that you’ve given an ID number to each card in your game in the column adjacent to it.
• Generate a list of random numbers between 1 and your maximum ID number using the randbetween function. Use the vlookup function to grab each card by ID based on the random numbers. This strategy is quick, but generates a deck with duplicate results due to the randbetween random number generator.
• In a column next to your list of cards, generate a list of random numbers using the rand function. Sort the sheet by the column of random numbers. This strategy generates a deck without duplicate cards, but it’s not quick since it requires the extra steps of sorting the column each time.
Today I discovered a new strategy that is both quick and without duplicates:
• Start with the first strategy using the randbetween and vlookup functions. Now you have a randomized deck containing duplicates. In a new column, use the unique function, selecting the column with your randomized deck. Voilà! You now have a randomized column of cards without duplicates. Now, each time you refresh the sheet, you will have a new deck without having to sort (CMD+R / CTRL+R to refresh in Google Drive Spreadsheets). This means that each test can take a matter of seconds, depending on how involved your game is.
If you are using multiple types of card decks, this strategy has the problem that it generates the same deck combinations each time, e.g. if you generate the numbers 46, 2, 15, 34, it will pull each of those numbers of cards from each deck. You can either use multiple lists of random numbers to combat this, or you can use a combination of the index, randbetween, match and countif functions. If you’re interested to know more about that, feel free to send me a message.
That’s it! I’m sure some designers out there are already using this trick, but I never came across it in my research so I wanted to share. Enjoy, and may your playtests be short and frictionless!
Update: I since learned of a new genius function in Google Drive Spreadsheets: sort. This function sorts data dynamically – hallelujah! So you can do the following: Make a column of random numbers from rand. In an adjacent column, place all of your card IDs. Use the sort function to create a new column that sorts the card ID column according to the random numbers. Lastly, use the vlookup function to grab each card by ID.