Make your NPCs come alive, through consistent and fluid events that can shape the world that your players experience! We’re going to do that by taking the NPC Note Card File system that I demonstrated a few days ago, and creating an event generation system.
Don’t worry, if you haven’t coded before, I’ll step you through what you need to do, as well as give you the source code that you need to use in order to make this all work! The rest… using your imagination to take what this system gives you, and incorporate it into your campaign… well, that’s the fun part you get to do!
One of the things that fascinated me about the Rolodex of NPC note cards that Tony Bath and Professor MAR Barker kept for their respective campaigns was how they would update them each and every campaign year. They would sit down on some weekend with their cards and dice, and roll out who had died, gotten ill, had children, or perhaps received a promotion or demotion. I loved the concept – that as the PCs would grow and expand, they would see the NPCs around them have lives and happenings as well!
I automated a similar system in my short-lived Microsoft Access NPC database with scripting and a report. It worked, but as I mentioned before, trying to lug around an Access database, and maintain it, was just too much work! I wanted something on the cloud, preferably integrated with Google Drive and tools.
So, after digging around on the Internet, I found a bit of sample code that pointed me in the right direction. After some coding, I think I have a pretty good foundation on a system that will give me what I want.
I have a card file and a system of updating the NPCs or PCs.
I now have a system of reading that file and generating a series of events that I can use to affect the NPCS.
This tutorial will show you how to set up that event generation system. If you are inclined to coding, I’m sure you will be able to see many ways to improve this! OK, here we go!
1. Within Google Drive, in the same folder where you have your NPC/PC Card File, click the “+ New” button, select the “More” option and then select “Google Apps Script”. Delete all of the code that is in the new script file.
a. Click the Resources menu and select “Advanced Google Services…”
b. In the window, scroll down till you see a line “Google Sheets API”. To the right, click the “off” button so that it shows green “on”. Then… click the “Google Cloud Platform API Dashboard” link.
c. A new browser tab will open to the “Google Cloud Platform.” Click the “Enable APIs and Services” link.
d. Now in the search text box, type “sheet” – after a moment, a card with “Google Sheets API” should appear. Click the card.
e. A page that explains the Google Sheets API will be shown. Click the “Enable” button.
f. Click back to the Scripts tab. The “Advanced Google Services” window is probably still open. Click “OK”.
Whew… now that all the set up stuff is done, now let’s get to the meat of the tutorial.
2. Open this text file. Once open, copy all the code from it and paste it into the script file you created in Step 1.
3. Open up the NPC/PC Card File Google Sheet that you created. It will open in a new browser tab.
4. Back within the code file, you have to make two changes based on your NPC/PC sheet. First, you have to modify the highlighted parts of lines 2 and 3:
function createNPCEventsReport() {
var headers = Sheets.Spreadsheets.Values.get(‘1-abc‘, ‘A1:I1’);
var cards = Sheets.Spreadsheets.Values.get(‘1-abc‘, ‘A2:I999’);
To make changes, go back to your Sheet tab, look at the URL in the address and select your Sheet ID – it will be this part of the URL:
https://docs.google.com/spreadsheets/d/ABC123OoxcPX9q0xOOR44AY0rq53S3Wbi8TJs9988-ZYX/edit#gid=1099999999
Copy that ID and paste it in both places where I have the fake ID: 1-abc. Make sure you get rid of the fake ID. Keep it between the single quotes – don’t remove those!
5. Now, look at your Card file Sheet. Remember how I had you add fields? You might have added a few more. Do you have a field for gender or age? Did you keep the name field? And the location field?
If so, then you’re good to go!
If not, you’ll need to add a field for gender (I used a multiple choice for ‘Female’ and ‘Male’) and a field for age. You’ll need to add back the name and location fields as well. See the previous tutorial on how to edit the Card File form so that those columns/fields get added.
Your sheet may look something like this:
Now we’re going to update the code so that it will get all of the fields that you have in your sheet. In my example, I have fields represented by columns A to H. Row 1 is the header row. And Rows 2 to 999 are my data. So these two lines are correct.
function createNPCEventsReport() {
var headers = Sheets.Spreadsheets.Values.get(‘1-abc’, ‘A1:H1’);
var cards = Sheets.Spreadsheets.Values.get(‘1-abc’, ‘A2:H999’);
However, if you have more (or fewer) fields, you will have to adjust those accordingly. Maybe your last field is column J or M. Just make sure that name, age and gender are within the range you specify.
6. OK, we have just a couple of more edits to make in our code. We are going to update how the code picks up the fields in your form. Going back to my example sheet:
If timestamp (A) is column #1, then the name field (C) is column #3, location (D) is column #4, age (G) is column #7 and gender(H) is column #8.
Look at your sheet and note which column #s you have for name, location, age and gender.
Go to the script file and find lines #10 – 15.
In line 10, update this part cards.values[i][3] so that the highlighted value is the # of the column that is your location field MINUS 1! If your location column is column #4, then you don’t have to change anything – the script already says 3.
In line 13, update this part cards.values[i][2] so that the highlighted value is the # of the column that is your name field MINUS 1.
In line 14, update this part cards.values[i][6] so that the highlighted value is the # of the column that is your age field MINUS 1.
In line 15, update this part cards.values[i][7] so that the highlighted value is the # of the column that is your gender field MINUS 1.
Again, if all these already set correctly, then you don’t have to change anything! Why the minus 1? Because we’re using arrays – a coding construct – and arrays don’t start counting at 1, they start at 0. So if your name column is column #3, then in an array, it’s actually column #2. It’s a coding thing. If you see strange results later in in your script, it might be due to wrong field values here.
That’s the hardest part of this whole deal!
7. Click the Save button on the menu bar. It will ask you to give a name to this script – call it “NPC Event Generator”.
8. OK, we’re going to run our code and see what happens! Now, if you remember from the Card file tutorial, the first time you run code, Google is going to complain that it doesn’t know who made this script, that it’s possibly dangerous, etc.
Don’t worry about this – it’s normal behavior and unless you’re a registered developer using Google tools, you’ll always get this the first time you run the code. Just follow along to get through this part.
a. Click the Run menu, select “Run Function” and select “createNPCEventsReport”.
b. A message will pop up saying “Authorization Required” – click the “Review Permissions” button.
c. You might get asked to “Choose an Account” to review, if you have more than 1 Google account. Just pick the one that you are using for Google Drive.
d. You will see a warning window “This app isn’t verified” – click the “Advanced” link.
e. You might have to now scroll down a bit to find the “Go to NPC Event Generator (unsafe)” link. You do trust yourself, right? Go ahead and click the link.
f. Now Google explain to you what permissions this script needs. Click “Allow”.
Now… in your script window, you will see a notification that the script is running, and then it will go away…
SO WHAT HAPPENED?!?!
9. Go back to Google Drive. Go to the root folder of your drive and look for a document titled “Generated Events Wed Nov 14 2018” (or something similar, depending on the date you do this).
Open that file. Tada! This is the file that will get generated each time you run that script function. This will contain the generated events (if any) for your NPCs that are in your card file.
So what if the file is blank? Well, go back to your script window, click Run/Run Function/createNPCEventsReport again. Generate another report and see if the dice roll your way to create events. Not every NPC will have events happen every time this is run.
If you have more NPCs, your chances of seeing some with events will increase – so perhaps make more.
Here’s an example when I run against my card file for my Etinerra campaign.
So… how might I use this as part of my campaign? Well, as my players get close to the end of a campaign year, I would run this script. I would then look it over and see what I like, what makes sense, what doesn’t make sense.
In my example, Marshall Roehm’s wife is going to die! That would have some pretty serious effects on the good Marshall, and perhaps open an avenue to other actions by him, or a quest for the PCs. Perhaps it makes no sense that Anastasia would have a child or adopt one, so I might toss that out.
I would make notes on my NPC cards (using the Update link to open the card and edit the notes) and then perhaps keep this document in my campaign folder/binder to refer to during the year.
Now… a couple of notes about the script itself.
This is geared towards the seasons of my campaign: Spring, Summer, Autumn, Winter. You might have different approaches. If you’re the coding type, you can edit the places where I select/name the seasons.
My odds for death/illness and childbirth are roughly based on the statistics from Domesday and other medieval resources I dug up while I was putting together the original script for my Access database. You might want to change them! Similarly, I put the odds at 25% each season of an event happening to an NPC – so odds are that at least once a year, something interesting may happen, above/beyond death/illness/childbirth.
Feel free to muck about with the odds to suit your campaign.
Finally, adding additional event types is pretty easy – just make sure you follow how I’ve laid out each “case” for an event, and make sure you update the line that rolls the dice so that your new event is included (line 182): switch (rollD(16)){
So… did you make it through this and everything work? Let me know how it went!
Happy event generating and may your world be full of NPCs living their own lives and destinies!
Holy balls this is some great stuff! I'm super keen to mess around with google scripts and sheets now, I've never seen this before. Thanks for sharing!
You're welcome! If you come up with something cool, I hope you'll share.
I had a few fiddly bits give me issues. I also had to add the Google Scripts app so I could select it.
I got it working with both the Update script and the Event Script.
Interesting, I like the portability of it. I'll have to think on the event generator and what I might want to change.
I need to think about what fields I might add.
I like that one can export the list to CSV to do other things with the list.
Thanks for sharing!
Cool! I'm glad you were able to figure it out. If you do some cool mods, share, please? 🙂
PS. If you're available on Tuesday evenings, I'm starting a Roll20 game in my campaign world.