Dice Roll Macro in Google Sheets

Posted 22 Mar 2022 to Musings

Google Sheets is ubiquitous and easy to use. You can also create some wonderful and colourful character sheets easily for online play. But dice rolls are not as easy as RANDBETWEEN.

If you are running a game online, and your game system is not supported by a virtual table top, Google Sheets is a convenient option for setting up a character sheet. There are several advantages.

• It's free. If you have a browser, you can open a Google Sheet.
• Easy formulas. You can use simple spreadsheet formulas to calculate many stats and derived stats for your game system.
• You can share the doc with all the players. They will have access to it between sessions, and everyone can work concurrently.
• You can create a separate tab for each PC. But have all the information altogether in one document.
• You can easily paste images for NPCs or scenes into the doc.
• You can create extra tabs. For NPCs. For setting information. For a rules reference sheet. Whatever.
• Bright colours and wide font selection. You can really customize the experience to match the genre you are playing.
• You can use LOOKUP functions to automate many rule functions. For example, in D&D5e you can use a table that converts a stat of 15 to a bonus of +2. If you increase the stat to 16, your LOOKUP formula will recalculate the bonus to +3 automatically.

But how do you do dice rolls?

You might think that the RAND or RANDBETWEEN functions are the solution here. They're not. If you want to roll 1d20, you can use RANDBETWEEN(1,20) and it will *appear* to generate a random number on your screen. But this function runs locally on each person's browser. So you will all be looking at a different 1d20 result.

We want to have a dice roll that is consistent across all players concurrently.

Here is how you do it.

• You need to create a custom function.
• Click [Extensions] in the menu.
• Click [Apps Script].
• You will land in a code editing area. A new function template will be shown for a new function called "myFunction".
• For this example, rename the function to "Rolld20".
• Paste the formula below. This one rolls 1d20, but you can modify it to roll anything.
  
  

  		return Math.floor(Math.random()*20+1)
  		

• Click save (the disk icon).
• Close the Apps Script tab to return to your main spreadsheet.
• Put your cursor in a blank cell and type "=Rolld20()" and push enter.
• A random number appears! And this number will be consistent with all users who have the spreadsheet open.

You can create lots of functions for different dice types. If your game system has some more complex logic, such as roll several dice and keep the highest, then you can accommodate that too. You might notice a slight delay before the result appears, because the dice roll needs to be shared with all players, but its pretty negligible.

Optionally, you can make things more usable by using a checkbox. Then you modify your dice roll formula so it only rolls when the checkbox is ticked. Then you can easily reroll by unticking and reticking the checkbox.

=if(A1=true,Rolld20(),"")