Second part is the DM tool and data generation:
I started this project several times.
Some friends had the idea for a random-initiative-every-round for their homebrew Pathfinder 1E game. The challenge was keeping every round fresh, so players don't become disconnected during a round. If you're going early in the round, the next time you get to DO anything...next round. So maybe you check out something on your phone or laptop.
They used a google spreadsheet to run things. I ended up making my own version, with some fun formulas and some Apps Script magic.
My real contribution was to add an LCD that could clip to the DM screen (or on a stand, in front of the screen), a D1 Mini to provide the power and data to the LCD display. The D1 mini would connect to the internet, grabbing information from the Google Sheet.
The cost was about $5 for the LCD, $3 for the D1 mini; I used some jumper wires from earlier projects, and the 3D-printed case is probably 60 cents of PETG filament.
Did it work? Almost. I tested the D1 mini and the LCD which worked. I wrote AppsScript to pull information from the Google Sheet. I could not get the next step to work - either the D1 mini wasn't giving the new information to the LCD correctly, or the D1 mini wasn't grabbing the information from the Google Sheet correctly. (I'm guessing now that it was a problem with following redirects.)
This was shelved for a bit, until I bit the bullet and got some ESP32s. They were $5 each, and bigger boards. The debugging was useful, and I was able to use more libraries that I had seen others use in youtube coding videos. Still, no one had really done EXACTLY what I needed, so...here we are.
The shopping list:
- ESP32 (I used 30 pin, but 38 pin should be fine)
- LCD 1602 I2C
- 20" of AWG 22, 4 conductor
- USB-Micro to USB cable to connect to your computer; a USB power plug for later use
- Optional: Jumper wires to connect the LCD and ESP32 during testing
Tools needed:
- A computer with an Arduino environment
- Soldering iron, solder, flux
If you want the 3D printed cases:
- Custom ESP32 case (30-pin) - https://www.thingiverse.com/thing:7007734
- Custom LCD 1602 I2C case (includes stands) - https://www.thingiverse.com/thing:7007740
- 5 3M 20mm screws, one 3M nut
- 2 4-inch zip ties
The process breakdown:
Chapter 0: Setting up the Arduino Environment
Chapter 1: Assembling the Circuit
Chapter 2: Testing the LCD and ESP32
Chapter 3: The Basic Spreadsheet
Chapter 4: Apps Script Part 1, Publishing data
Chapter 5: Apps Script Part 2, Adding Functionality
Chapter 6: Using the Spreadsheet
Chapter 7: ESP32 Pulling from the Apps Script
Chapter 8: Printing the Case
Chapter 9: Final Assembly
Feel free to skip ahead to the stuff you don't know.
Chapter 0: Setting up the Arduino environment
I'm setting this up on a Mac today. I went to https://www.arduino.cc/en/software and downloaded the latest Arduino IDE. In this case, it was Arduino IDE 2.3.4. Double-click on the .dmg file, drag "Arduino IDE" into the Applications folder, where it installs the software. After it is done, go to the Applications folder and double-click the Arduino IDE. Click "Open" when OSX asks you, and allow Arduino IDE to have access to your Documents folder.
You'll need to add some libraries - click on the stack of books in the left-hand column.
LiquidCrystal I2C by Frank de Brabander (1.1.2 as of this project)
WiFi.h is part of the base Arduino IDE libraries
HTTPClient.h is part of the base Arduino IDE libraries
The IDE picked up on the USB port when I plugged in the ESP32, and I chose a generic "ESP32 Dev Board" for the board type. (Board was stamped with "ESP32 DEVKITV1".)
Chapter 1: Assembling the Circuit
From the LCD I2C to the ESP32:
- SCL connects to D22 – Serial clock for I2C bus
- SDA connects to D21 – Serial data for I2C bus
- VCC connects to VIN
- GRD connects to Ground
The ESP32 will get power from its Micro USB cable, and then supply power to the LCD over the VCC/VIN connection. The VIN pin isn't technically 5V, but it was enough to power the LCD.
I used some jumper wires to test things out, but if you feel ambitious, jump right into soldering them. (You'll need to print the cases first, if you are going into soldering.)
Chapter 2: Testing the LCD and ESP32
First, getting the LCD 1602 I2C to work with the ESP32. If you can't get this to work, nothing is going to happen.
The 1602 means - 16 columns, 2 rows. The "I2C" stands for integrated circuit, which is built into this particular component. You can do it separately, but I'm a hobbyist and trying to minimize the extra learning. Because it's the LCD 1602 I2C - you need to use the right library.
I ran into an error code "library LiquidCrystal I2C claims to run on avr architecture(s) and may be incompatible with your current board which runs on esp32 architecture(s)." It's fine, I was able to ignore it and everything still worked.
I needed to use lcd.init(), while some projects online called lcd.begin() - which produced a blocking error for my code.
The code I used as a test was this:
#include <LiquidCrystal_I2C.h>
// Set the LCD address to 0x27 for a 16 chars and 2 line display
LiquidCrystal_I2C lcd(0x27, 16, 2);
void setup()
{
// initialize the LCD
lcd.init();
// Turn on the blacklight and print a message.
lcd.backlight();
lcd.clear();
lcd.setCursor(0, 0);
lcd.print("R1 - Owlbear2");
lcd.setCursor(1, 1);
lcd.print("Next: Fletch");
}
This is hopefully straightforward - it's supposed to mimic the final product.
R1 = Round 1
Owlbear2 = the current initiative count
Next: Fletch = the next person on the initiative count
There were all just a bunch of print commands, but it meant my wiring was fine, and those components could work with each other.
Chapter 3: The basic spreadsheet
This is where the DM gets things to work. The ESP32 will pull information from this. There's a simple tab that just shows what will be displayed on the LCD. If you just want one sheet, you can change the Apps Script and the Arduino code to have it display things differently.
One of the horribly messy things about google sheets is that random functions are updated ALL THE TIME. You roll initiative, update a field...everything recalculates. When I started the project, there was a weird LAMBDA function call that could limit the RANDBETWEEN from triggering all of the time. Sadly, as of February 2025, it no longer works.
Create two tabs in the spreadsheet - one called Initiative, and the second one called Display.
I use 4 formulas on the Initiative tab, and then use Apps Script to carry the rest of the heavy load.
Cell B2: (Calculate the current PC/NPC, based on Resolved initiatives)
=if(isblank(index(filter(A6:A24,B6:B24=false),1)),"End of Round",index(filter(A6:A24,B6:B24=false),1))
Cell B3: (Calculate the next PC/NPC, based on Resolved initiatives)
=if(A6="Waiting","",if(B2="End of Round","",if(isblank(index(filter(A6:A24,B6:B24=false),2)),"End of Round",index(filter(A6:A24,B6:B24=false),2))))
Cell A6: (Sorts and filters the Names, based on their initiative - feeds B2 and B3)
=if(iserror((filter(sort(filter(F6:I24, NOT(ISBLANK(F6:F24)), J6:J24=true),4,false,3,true), {true, false, false, false}))),"Waiting",(filter(sort(filter(F6:I24, NOT(ISBLANK(F6:F24)), J6:J24=true),4,false,3,true), {true, false, false, false})))
Cells I6 [copied through I24]: (adds Initiative Modifier and Die roll, if their Roll checkbox is checked)
=if(J6,SUM(G6:H6),"")
In the Display tab (what Apps Script will pull) are a couple of simple concatenate functions so I pull less fields/do less manipulation in the Arduino code.
Cell A1: (Makes it easier to make a clean item for the ESP32 to pick up - Round and Current.)
=CONCATENATE("R",Initiative!B1,": ", Initiative!B2)
Cell A2: (Makes it easier to make a clean item for the ESP32 to pick up - Next.)
=CONCATENATE("Next: ", Initiative!B3)
Chapter 4: Apps Script Part 1, Publishing data
Disclaimer: this script works in April 2025. I cannot know how Google Sheets/Apps Script may change in future.
On your spreadsheet, go to Extensions > Apps Script.
Copy the following code, then include the spreadsheet ID for your initiative tracker.
When you look at your Google spreadsheet, it will look like this in the address bar:
https://docs.google.com/spreadsheets/d/xxxxxxx/edit?gid=0#gid=0
The xxxxxxxx is your spreadsheet ID. Make sure your display tab is called Display.
function doGet() {
const sheet = SpreadsheetApp.openById('[replace_with_spreadsheet_ID]').getSheetByName("Display");
const valueA1 = sheet.getRange("A1").getValue();
const valueA2 = sheet.getRange("A2").getValue();
const data = {
"A1": valueA1,
"A2": valueA2
};
return ContentService.createTextOutput(JSON.stringify(data))
.setMimeType(ContentService.MimeType.JSON);
}
- Click on the Disk/Save icon
- Click on New Deployment
- Click on the [GEAR] icon next to Select type, and choose Web App.
- Description doesn't matter, leave "Execute as" as your user account, and change "Who has access" to "Anyone"
- Click on Deploy, and when it requires you to authorize access to your data, you click on "Authorize access". Choose your account, click on "Advanced", then "Go to [Untitled project] (unsafe).
Finally, you'll click on "Allow". Yes, permissions are crazy. It allows the project to "See, edit, create, and delete all of your Google Sheets spreadsheets".
It will ease your mind a bit to read the code, I promise it's nothing crazy or complex.
Copy the deployment ID with the https://xxxxxxxx - we'll need that later in the actual Arduino code. However, if you want to test it, paste that https address in your browser. It should show some javascript output.
If you just want to see the Sheets -> ESP32 -> LCD code work, you can skip Chapter 5.
Chapter 5: Apps Script Part 2, adding functionality
This is where you add the missing functionality from the formulas in Chapter 3. Things like rolling initiative, and getting set up for the next round. Cut and paste the "function onEdit(e)" code block below, beneath the existing "function doGet()" code block from above. When you're done adding the code, click on the Disk/Save icon. You do not need to deploy again.
There's essentially several things that happen in the function onEdit:
GENERATE RANDOM INITIATIVE ROLLS:
- If a checkbox for a participant is checked (true) in column J, it generates a random number in column H
- If a checkbox for column J is unchecked, it removes the initiative roll
NEXT ROUND CHECKBOX - IF Checked, do these things:
- Increments the round number (found in cell A1)
- Copy checkboxes from initiatives from the last round into the "Last Round" column. (Allows you to easily tell who needs initiative rolls again.)
- Copy last round initiative into "Last Init" (Allows you to troubleshoot if a player says "Hey, I didn't go last round, did I?")
- Clear the Initiative Dice rolls
- Clear the Current Round's Initiative Checkboxes
- Clear the Resolved/Held/Readied checkboxes
- Uncheck the Next Round checkbox at the end
function onEdit(e) {
const sheet = e.source.getActiveSheet();
const range = e.range;
// GENERATE RANDOM INITIATIVE ROLLS
// Check if the edited cell is a checkbox
const checkboxColumn = 10; // Column J
const numberColumn = 8; // Column H
if (range.getColumn() === checkboxColumn && range.getValue() === true) {
// Generate a random number between 1 and 20 (adjust as needed)
const randomNumber = Math.floor(Math.random() * 20) + 1;
// Set the random number in the nearby cell (Column H)
sheet.getRange(range.getRow(), numberColumn).setValue(randomNumber);
} else if (range.getColumn() === checkboxColumn && range.getValue() === false) {
// Clear the number if the checkbox is unchecked
sheet.getRange(range.getRow(), numberColumn).clearContent();
}
// NEXT ROUND CHECKBOX
// Set your specific checkbox location
const checkboxCell = sheet.getRange("H1");
const isChecked = checkboxCell.getValue()
// Set the ranges to be cleared
const rollRange = sheet.getRange("H6:H24"); // Example: initiative rolls
const checkboxRange = sheet.getRange("J6:J24"); // Example: Checkboxes to uncheck
const previousroundRange = sheet.getRange("K6:K24"); //Example: Used to keep checks from round to round
const resolvedRange = sheet.getRange("B6:D24"); // Clear the resolved, held, and readied
const lastInRange = sheet.getRange("L6:L24"); // last round's initiative
const currentInit = sheet.getRange("I6:I24"); // Initiative
// Check if the edited cell is the specific checkbox
if (isChecked === true) {
// THEN Perform actions when the checkbox is checked
// INCREMENT ROUND NUMBER
const roundNumber = sheet.getRange("B1");
const incrementRound = roundNumber.getValue();
roundNumber.setValue(incrementRound + 1)
// Copy Initiative Checkboxes over to Last Round, for ease of use
const valuesToCopy = checkboxRange.getValues();
previousroundRange.setValues(valuesToCopy);
// Copy current initiative to Last Init, to help troubleshoot the round before
const initToCopy = currentInit.getValues();
lastInRange.setValues(initToCopy);
// Clear initiative rolls
rollRange.clearContent();
// Clear initiative checkboxes (setting to FALSE)
checkboxRange.clearContent();
// Clear the resolved/held/readied checkboxes (setting to FALSE)
resolvedRange.clearContent();
// Uncheck the Next Round checkbox
checkboxCell.setValue(false);
}
}
Chapter 6: Using the Spreadsheet
To start a round, click all of the PC/NPC "Roll" checkboxes that will have initiatives that round.
It will sort them in Column A by initiative (and then by Initiative Modifier if there's a tie). If you type ANYTHING in the A column under Order, where it wants to put an output...it will error. Just don't do it.
If you want to manually correct an initiative, it's fine - but keep in mind the Resolved checkboxes won't follow the people whose actions have already taken place. (Solution: adding in another participant, change their die roll to be where you want it to show up in the order.)
To update the Current/Next:
Click the Resolved checkbox to show that a "person" has gone that round. The hold and readied don't do anything - we didn't resolve how holding and readied should read. There's also a crazy game mechanic balance of holding your action that can mess with things. (Basically the chaos that can erupt for going at the end of the round, to then go early in the round because of a fast initiative.) As an example, grapple someone in Pathfinder 1E in round 1 and round 2 you maintain your grapple with a bonus because they didn't try to escape.
Next Round checkbox will advance to the next round and clear a bunch of checkboxes. Last Round column will help you see what "Roll" checkboxes should be included in the next round. The "Last Init" column can help if there's a question of what happened & when during the previous round.
Chapter 7: ESP32 pulling from the Script
This is where it all comes together. Hypothetically, you could run one more test Arduino script on the ESP32 to show that you can connect to the wireless network. If you've having issues, maybe test it with some sample Arduino code that can check the network/internet connection.
You'll need to hardcode your wifi network, wifi password, and the url from your Apps Script deployment. If you have all that, use the code below in the Arduino IDE software.
Some pain points:
- I needed to use lcd.init(), and some projects online called lcd.begin().
- I definitely needed the "follow redirects" part of the code (THIS IS IMPORTANT.)
- Troubleshooting: it definitely helps to learn how to look at the serial monitor, in the Arduino IDE.
I just pasted the code, attached the ESP32 to my computer, then clicked the Upload arrow. It will spend a lot of time compiling and much less time writing to the ESP32.
Chapter 8: Printing the Cases
Hardware:
5x 3M 20mm screws
1 3M nut
4 screws for the screen, 1 screw+nut to attach to the stand or screen attachment.
The cases were the most comfortable part of the process for me. In Chapter 1, while I didn't know the wiring, there were a bunch of simple wiring diagrams on the internet. Chapter 2, testing the ESP32 was cut-and-paste, but I had issues getting my environment set up. Going into Chapter 3, the Google sheet required me to figure out how to restrict randoms from being re-rolled all the time, and I had to get creative with the formulas. In Chapters 4, 5, and 7, when I couldn't figure out my errors, and explored the world of chatgpt coding.
But designing & printing the cases...that felt like the victory lap. I've done plenty of 3d modeling. I did not use a CAD software for it - maybe someday. I found some other designs from other projects, pulled out my digital calipers, and did a couple of prototypes.
Links to the originals:
The LCD casing was based off of this design by EngBuild:
1602 LCD Mount With Swivel Viewing Angle - https://www.thingiverse.com/thing:2372451
The ESP32 case was based off of this design by bkgoodman:
ESP32 DevKit V1 Case (Thick) - https://www.thingiverse.com/thing:4609596
Links to my remixes:
Custom ESP32 case (30-pin) - https://www.thingiverse.com/thing:7007734
Custom LCD 1602 I2C case (includes stands) - https://www.thingiverse.com/thing:7007740
I modified the LCD casing to fit the front of the screen better, and added a back cover so the DM wouldn't have to see the back of the LCD circuit board. There's a small hole in the back to reach the potentiator with a tiny phillips screwdriver, and the holes are tight enough that you don't need a nut to hold it in. I also added a tube+zip-tie gap, so it can hold the 5mm thickness of wiring...I didn't want the soldered wires to be the only thing keeping the connection together.
The LCD casing also had a stand, which I modified to fit a 3M screw and nut. And I added a second option to mount the LCD to the top of a DM screen. (2mm thickness - if it's too loose, you can add a spacer of some kind to tighten it up, or adjust the 2mm gap.)
For the ESP 32 case, I added some wiring gaps and another tube+zip-tie gap to make it prettier. I added a back to the case, so that the wires wouldn't be seen/damaged easily.
Chapter 9: Assembly
I did the initial wiring with jumper wires. For my final portion, I used about 20 inches of AWG 22, 4 conductor, stranded tinned copper - I plan on clipping the LCD case on top of the DM screen, and setting up the ESP32 case at the base of DM screen - allowing you to plug the ESP32 case into your laptop for power.
I started with the LCD soldering. Flux, add solder to the stranded wire, flux the LCD I2C pins. I am not an advanced soldering expert.
I soldered the ESP32 second, with more wire exposed out of the sleeve, pulling it through the other side of the case, soldering it to the ESP32, then pulling the wires back through so the ESP32 was snug.
I then used 4-inch zip ties to hold the cable sleeves snug to the cases - reducing tension on the soldering.
Overall, a fun project. I'm sad that it requires a hardcoded ESP32 for the wifi & google sheet page - makes it hard to just drop it into a game when you visit a friend's place.
Hope folks learned something from it!
Posts
No comments:
Post a Comment