Content of the article: "A Nicely Formatted Google Sheets Based DM Reference – With Player View!"
Hey, all! As is tradition, if you are in a campaign with Gwenneviere Walker, Nataly Dewpearl, and Geoff Pron'Ouncedgif, stop reading!
Earlier this summer I dipped my tow in the water of D&D the same way hundreds of others did: I got a group of friends together and started playing through the Lost Mines of Phandelver module. Since this was (and is – we're still going) my first experience as a DM, I was looking for a simple and streamlined way to keep track of all the various things that come into play. Months later, that's evolved into a multi-tabbed Google spreadsheet full of player information, an in-game "day planner" of sorts, collections of random tables I've found on this subreddit, et cetera. I'm proud of the work I put into it, but that on its own probably isn't enough for a post here, because it's nothing you haven't already either seen or put together for yourself. So what am I showing you?
Well, my players were asking every week for an update on their XP for leveling up, or what the specific effects were for the weather table I was rolling on, or other quick-reference stats. Since I was tracking all this information anyway, I thought it would be simple to share a version of it with them, but I ran into two limitations: I didn't want to simply share my whole spreadsheet with them (for obvious reasons), and I didn't want to have to constantly check to make sure a separate "Players Only" sheet was still up-to-date.
That led me to create this Player's Reference Sheet!
The only tab that should be visible pulls all of its data from other tabs within the same spreadsheet, which helps prevent linking back to my "DM Only" spreadsheet and spoilers, whether unintentional or not. If you will kindly click here, I'll walk you through what's going on, section-by-section.
- The fundamental mechanic behind everything on this sheet is the
IMPORTRANGEfunction. Basically this just uses the "Share" URL to import any arbitrarily large range of cells from another spreadsheet, live and in real-time. So when I update a cell on the DM Reference sheet that this links to, those same cells update on the Player View. This formatting was inspired by a post on DnDBehindTheScreen, which unfortunately I can't find again, but I think it looks nice. The
IMPORTRANGEfunction doesn't copy formatting, so the cells in the "Languages" section mark the languages with an "x" and uses Conditional Formatting to convert and non-empty cell to a grey bar because that's prettier and stuff.
- The XP Tracking section also pulls from a larger sheet on the DM Spreadsheet, where I keep track of every single encounter and story milestone. The total XP is then calculated automatically, and can be compared to upcoming level-up thresholds.
- To make things nice and pretty, these cells use the
SPARKLINEfunction to display a little progress bar. It simply uses the calculated Total XP as the input, the XP Threshold to its right as the maximum, and 0 as the minimum.
- This section looks at the range of cells in my "Custom Encounters" table on the XP sheet, and uses the command
=index(A:A,max(row(A:A)*(A:A<>"")))to display the bottom-most cell, followed by
=index(A:A,max(row(A:A)*(A:A<>""))-1)for the cell that is second to the bottom,
=index(A:A,max(row(A:A)*(A:A<>""))-2)for the cell above that, et cetera. I'll be honest, I don't fully understand what's happening in that function because I found it on a forum post but it does work, which is neat and mystifying. It also means that while clicking the checkboxes next to the pre-written XP gains that are part of the module still update the Total XP correctly, they won't show up on the Player View, but that's fine for me.
- These two cells look at the overall "day planner" tab, which I use to both keep a summary of what the players did each day and to plan out upcoming story points. For example, when Geoff killed Sildar Hallwinter (…yep), I wanted a scouting party of Lord's Alliance soldiers to come check up on things after he failed to check in for a couple of weeks. Now I've added a column of checkboxes that I click on as each day passes, and a simple
=countif(A2:A,TRUE)functions counts the days and displays it on the Player's View.
- The last section (but not the least section) is this awesome table of Reasonable Weather Effects which I found here. I roll on the table before each session, and now when I click the checkbox next to whichever weather comes up, the function
=IFERROR(VLOOKUP(TRUE,A1:D48,3,FALSE),"None")looks for that checked box and updates the cell on the Player's View accordingly. The
VLOOKUPfunction returns an error if the search condition can't be found, so the
IFERRORfunction helps keep things pretty.
The rest of the tabs on the DM sheet are mostly random tables, like the 20 bad omens from this post, the 50 Weird & Wonderful Taverns this DM shared, a constantly-added-to table of Wild Magic Effects I've been putting together from every source I can find, et cetera. I don't share those tables with my players, but any number or sections from any of them could be added with the functions above, if you would like.
Hopefully this helps some of you who are trying to keep all this information organized. My group plays online exclusively, which means we're all looking at computers anyway, so this computer-based solution works for us. Let me know what you think, or if I can clear anything up!
- Party Loot Sheet & XP Tracker in Google Sheets (version 2!)
- I made a Party Inventory Google Sheet, feel free to use! (includes DM Editing, basic sorting, “Favorites” and “Equipped” tags, automatic item descriptions from an attached, editable database, smartphone functionality) It’s janky and a work in progress, but it works well for my table!
- Character Tracker Spreadsheet
© Post "A Nicely Formatted Google Sheets Based DM Reference – With Player View!" for game Dungeons & Dragons Online.
Top 7 NEW Games of June 2020
Quite a few exciting games are releasing for PC, PS4, Xbox One, and Nintendo in June. Here's what to keep an eye on.
Top 10 NEW Open World Games of 2020
Video games with open worlds continue to roll out in 2020 on PC, PS4, Xbox One, Nintendo Switch, and beyond. Here are some to look forward to!
Top 10 Best New Upcoming Games 2020-2021
The best selection of games which will be released in 2020 and 2021 for PS4, PS5, Xbox One, Xbox Series X, Google Stadia and PC - and you can watch in amazing UHD 4K and 60FPS with latest updates about all of the games in this list!