Automating Wedding RSVPs with Python and Google Docs¶

This notebook originally appeared as a post on the blog Offhand Remarks.

If you want to dive right in to setting up your own system, check out the User's Guide below.

This past year I got married, and it was a blast. After months and months of planning, we lucked out and nothing major went wrong on the day of (despite some last minute rain forecasts for our outdoor venue). During the planning process, we ran into all of the usual challenges. To make matters worse, I was living in CA at the time while my fiancée was in NJ, and we were both busy with school (astrophysics Ph.D. for me, M.D. for her). Unsurprisingly, we looked for every opportunity to make the planning easier. As one example, I made an automated, online RSVP system for our wedding website. After some initial set-up, we were able to sit back and watch our master guest list (stored on our Google Drive) track the RSVP responses of our guests. And better yet, the whole thing was free.

There are a lot of options out there for online RSVP forms, but nearly all of them charge some sort of fee. So, I ended up combining a bunch of free services, and using some Python to glue them together, to build our RSVP system. In this post, I'm going to outline the main steps involved in the hope that it might make wedding planning just a bit easier for some future couple.

Our system relies on Google Drive for tracking RSVPs, and I've provided example (and anonymized) files here.

Using the JotForm service¶

To start, we need to set up the online form. There are a lot of options. My wife and I ended up choosing JotForm. When setting out to find the right service, we had a couple of constraints in mind. First, we wanted it to be free. Second, the form needed to be able to support conditional logic, e.g., the ability to show specific fields based on the name input by the guest. This was crucial for us, as we wanted to be able to tailor the RSVP form for those guests with and without plus ones. And finally, we needed to be able to output the form submissions to Google Drive since my wife and I maintained our master guest list as a Google doc spreadsheet. JotForm meets all of these requirements (and offers a nice user experience to boot!).

JotForm has a free tier with 100 form submissions, and it offers several ways to increase that limit (e.g., social media follows, etc). We ended up with 200 form submissions for free, which was more than enough for us.

Let's dive into the form. We'll use some Star Wars inspired names to explore the available functionality. Try entering "Luke Skywalker" into the form below, and watch the yes/no RSVP options appear. Now, try entering "Leia" into the first name box. Not only will the entry for Leia appear but also the entry for her plus one (Han Solo). Using these two types of entries, we were able to support guest RSVPs with and without accompanying plus ones. JotForm allows a lot of customization regarding the condition logic. For example, you can set up the form so guests can also use the plus one's name — try inputing "Han" into the first name box.

So, after creating a new form on JotForm, we added a new entry block to the form for each guest on our guest list and attached conditional logic to each such that the correct options were shown when the "First Name" and "Last Name" fields were filled in. Since we had roughly 150 invited guests, this was by far the most time consuming step of this whole process. I didn't find any good way of automating this step, but that would be worth exploring if you happened to have many more invited guests than we did.

Finally, JotForm allows you to link the form to your Google account, and will output the submission to a spreadsheet in your Google Drive. Unfortunately, the conditional logic used above makes the output table nearly unreadable. Each row in the spreadsheet corresponds to a different submission, and each column corresponds to a guest's name. With hundreds of invited guests, there are a lot of empty cells in need of consolidation. More on this in the next section.

Formatting the JotForm submissions with Python¶

To overcome the deficiencies of the JotForm output format, I wrote a short Python script to grab the Google spreadsheet output by JotForm, clean and re-format the submissions, and re-upload the results to our own master guest list. The code can be found on my GitHub. It relies on a handy little software package called df2gspread that makes managing Google spreadsheets in Python particularly easy using pandas. To get started, you'll need to setup your Google API credentials in order to query Google Drive from Python. The process is straightforward, and instructions can be found here.

Let's take a look at the format of the Google spreadsheet output by JotForm. The spreadsheet is available for browsing here. We'll load the spreadsheet into a pandas DataFrame using the unique identifier assigned to every Google spreadsheet (the long string of numbers and letters in the spreadsheet URL).

In [2]:

from df2gspread import gspread2df as g2d

ID1 = "1_hoBn8_0U9kurUFrr3sv6HAR_TCt2GlRL547G4ZZI7w"
raw_rsvps = g2d.download(ID1, 'Sheet1', col_names=True, row_names=False, start_cell='A1')

Let's take a peek at the first 10 rows of the raw RSVPs (note that you can scroll left to right in the output below to see all of the columns).

In [3]:

raw_rsvps.head(n=10)

Out[3]:

	Submission Date	First Name	Last Name	No Label	Nick Cain	Caroline Holt	...	Special Food Requests?	IP	Submission ID	Edit Link
0	2017-03-26 19:48:26	Nick	Cain		can't wait!	can't wait!	...	Vegetarian	173.11.99.37	366384906739153034	Edit Submission
1	2017-03-31 12:16:02	Rebecca	Matthews				...		71.225.51.127	366789762721516867	Edit Submission
2	2017-04-01 13:09:24	Kelly	Hensen				...		74.66.78.125	366879364521148201	Edit Submission
3	2017-04-01 20:11:50	Margaret	Gorman	can't wait!			...		71.230.123.114	366904710411283913	Edit Submission
4	2017-04-01 20:58:16	Amanda	Moss				...		98.221.238.205	366907496502264954	Edit Submission
5	2017-04-02 14:31:20	Brian	Prescott				...	Gluten free\nPescatarian (Both)	72.69.146.176	366970680671402658	Edit Submission
6	2017-04-02 17:37:40	Joelle	Shaughnessy	can't wait!			...		174.104.60.153	366981860351654678	Edit Submission
7	2017-04-03 6:01:02	Garrett	Evans	regretfully, can't make it			...		8.26.58.9	3670264629855203485	Edit Submission
8	2017-04-03 6:01:58	Elizabeth	Evans				...		8.26.58.9	3670265189859391972	Edit Submission
9	2017-04-03 6:02:36	Rose	Evans				...		8.26.58.9	3670265569853785331	Edit Submission

10 rows × 120 columns

With 120 columns, this format leaves a lot to be desired. In any given row, nearly all of the columns are empty, except for those corresponding to the guest that submitted the form. The information in the table begs to be consolidated, and with just a few lines of Python, we can turn this format into something much more useful.

The compute_rsvps.py script on my GitHub takes the raw JotForm output and consolidates the information into a usable format. An example spreadsheet output by the script is available as the "RSVP Submissions" sheet of this spreadsheet. Let's load this spreadsheet into a pandas DataFrame and take a look.

In [7]:

ID2 = "1iEAfk1cVG_PwhcwKJxUeLycUIsRhsAsp9e4xCk2qLtM"
cleaned_rsvps = g2d.download(ID2, 'RSVP Submissions', col_names=True, row_names=False, start_cell='A6')

In [9]:

cleaned_rsvps.head(n=10)

Out[9]:

	Submission Date	Name	Yes	No	Special Food Requests?
0	2017-03-26 19:48:26	Nick Cain	1		Vegetarian
1	2017-03-26 19:48:26	Caroline Holt	1		Vegetarian
2	2017-03-31 12:16:02	Rebecca Matthews	1
3	2017-03-31 12:16:02	Jake Smith	1
4	2017-04-01 13:09:24	Kelly Henson	1
5	2017-04-01 13:09:24	Merry Henson	1
6	2017-04-01 20:11:50	Margaret Gorman	1
7	2017-04-01 20:58:16	Amanda Moss		1
8	2017-04-01 20:58:16	Ryan Espinoza		1
9	2017-04-02 14:31:20	Brian Prescott	1		Gluten free\nPescatarian (Both)

This format is looking much better. We've stripped out all of the unnecessary information, leaving only the submission date, guest name, RSVP, and any special requests or comments. While we could stop here, my wife and I wanted to be extra lazy and have our "master" guest list automatically updated with these RSVP responses. We'll tackle that in the next section.

Updating a master RSVP list¶

Managing the guest list for a wedding is notoriously difficult. After a few iterations, my wife and I found a system that worked for us. We created a "master" guest list using a spreadsheet on Google Drive that not only included guest names but also added "Expected RSVP" and "Actual RSVP" columns. We then used our Python script to update the "Actual RSVP" column. As RSVPs started to roll in, this format allowed us to quickly see how realistic our total guest count estimate was (and update our vendors accordingly).

Our (anonymized) guest list spreadsheet is available as the "RSVP List" sheet of this spreadsheet. Here's a screenshot of the spreadsheet.

We found the summary statistics on the right hand side of this spreadsheet to be very useful throughout the planning process. Even better, the Python script only updates the individual cells of the "Actual RSVP" column (column D above) for those guests who RSVP'ed online. So, if you are like us, and have to use traditional hard-copy RSVPs for some of your guests, you'll be able to manually update the "Actual RSVP" column for those guests without losing that information.

And that's it! In practice, we embedded our JotForm RSVP form on our Squarespace website, and when JotForm alerted us via email when a new submission was received, I simply ran the compute_rsvps.py script on my laptop and our guest list was instantly updated with the new responses.

A User's Guide¶

To set up a version of this system for yourself, there are a few steps required. You'll need a local Python installation on your laptop (I recommend using Anaconda).

The necessary steps are:

Set up a JotForm account and clone the Wedding RSVP template form discussed in this post. The template is available here.
For each guest on your guest list, add conditional logic to the form to show the desired Yes/No fields based on the "First Name" and "Last Name" input values.
Add the "Google Drive" integration for your form and take note of the unique identifier of the output spreadsheet.
Copy the "master" guest list spreadsheet template (available here) to your own Google Drive, and input your guest list on the "RSVP List" sheet. Take note of the unique identifier of the cloned spreadsheet.
Setup Google Drive API credentials, following the instructions here.
Download the compute_rsvps.py script from my GitHub, and make sure the dependencies are also installed (following the README on my GitHub).
At the bottom of the compute_rsvps.py script, update the "YES" and "NO" variables with the RSVP messages you used on your form. Also, update the "JOTFORM" variable with the identifier of the spreadsheet output by JotForm (see step #3), and update the "UPLOAD" variable with the identifier of the final spreadsheet that holds the master guest list (see step #4).
Run the compute_rsvps.py whenever a new submission is received.
Sit back and relax! (Or plan the rest of your wedding.)

My wife and I found this system to be very helpful for managing RSVPs, and we hope it can help simplify the wedding planning process for others. Leave a comment below if you have any questions!

Thanks for reading!

This post was written entirely in the Jupyter notebook. You can download this notebook, or see a static view on nbviewer.