#!/usr/bin/env python
# coding: utf-8

# # Automating Wedding RSVPs with Python and Google Docs

# *This notebook originally appeared as a [post](http://nickhand.github.io/blog/pages/2018/01/10/automating-wedding-rsvps/) on the blog [Offhand Remarks](http://nickhand.github.io/blog).*

# *If you want to dive right in to setting up your own system, check out the [User's Guide](#A-User's-Guide) below.*

# <!-- PELICAN_BEGIN_SUMMARY -->
# 
# This past year I got married, and it was a [blast](https://www.flickr.com/gp/146306741@N04/kifqtW). 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. 
# 
# <!-- PELICAN_END_SUMMARY -->
# 
# Our system relies on Google Drive for tracking RSVPs, and I've provided example (and anonymized) files [here](https://drive.google.com/drive/folders/1iR1Hhu3yrkYCArQzwJRd0hk5prcX_XJz?usp=sharing).

# ## 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](https://www.jotform.com). 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.

# <br/>

# 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](https://github.com/nickhand/automated-wedding-rsvps). It relies on a handy little software package called [``df2gspread``](http://df2gspread.readthedocs.io) that makes managing Google spreadsheets in Python particularly easy using [pandas](http://pandas.pydata.org/pandas-docs/stable/). 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](http://df2gspread.readthedocs.io/en/latest/overview.html#access-credentials).
# 
# Let's take a look at the format of the Google spreadsheet output by JotForm. The spreadsheet is available for browsing [here](https://docs.google.com/spreadsheets/d/1_hoBn8_0U9kurUFrr3sv6HAR_TCt2GlRL547G4ZZI7w/edit?usp=sharing). 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)


# 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](https://github.com/nickhand/automated-wedding-rsvps) 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](https://docs.google.com/spreadsheets/d/1iEAfk1cVG_PwhcwKJxUeLycUIsRhsAsp9e4xCk2qLtM/edit#gid=899966356). 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)


# 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](https://drive.google.com/open?id=1iEAfk1cVG_PwhcwKJxUeLycUIsRhsAsp9e4xCk2qLtM). Here's a screenshot of the spreadsheet.

# <img src="/blog/images/master-guest-list.jpg" alt='[img: Guest List Format]'>

# 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](https://www.anaconda.com/download)). 
# 
# The necessary steps are:
# 
# 1. Set up a [JotForm](https://www.jotform.com) account and clone the Wedding RSVP template form discussed in this post. The template is available [here](https://www.jotform.com/form-templates/wedding-rsvp-template).
# 2. 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.
# 3. Add the "Google Drive" integration for your form and take note of the unique identifier of the output spreadsheet. 
# 4. Copy the "master" guest list spreadsheet template (available [here](https://drive.google.com/open?id=1iEAfk1cVG_PwhcwKJxUeLycUIsRhsAsp9e4xCk2qLtM)) 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. 
# 5. Setup Google Drive API credentials, following the instructions [here](http://df2gspread.readthedocs.io/en/latest/overview.html#access-credentials).
# 6. Download the ``compute_rsvps.py`` script from [my GitHub](https://github.com/nickhand/automated-wedding-rsvps), and make sure the dependencies are also installed (following the README on my GitHub).
# 7. 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). 
# 8. Run the ``compute_rsvps.py`` whenever a new submission is received. 
# 9. 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](http://nickhand.github.io/blog/downloads/notebooks/WeddingRSVPs.ipynb)
# this notebook, or see a static view
# [on nbviewer](http://nbviewer.jupyter.org/url/nickhand.github.io/blog/downloads/notebooks/WeddingRSVPs.ipynb).*