Sometimes I just really want to hack stuff together, especially when I’m doing something and just know there has to be a better (often more difficult, but ultimately rewarding) way.
So maybe that’s why, instead of working on the hundred-and-one things I need to do before my upcoming wedding, I decided to create a live wedding RSVP dashboard.
It took me a little bit to think through the logistics of this undertaking and how I could end up seeing what I wanted. We are using a site called AnRSVP for guests to submit their RSVPs (and for us to track them). I had settled on AnRSVP mainly because it only allowed people actually invited to RSVP. I could also make it match our wedding website theme and put in nicknames that guests might also go by. The only downside is that it has ads if you use the free version, but what’s a penny-pinching bride to do!
AnRSVP tracks RSVPs for you and will even give you a nice little statistics summary, but I didn’t want to have to login and check it constantly just to see an overview of who’d replied. I signed up for email notifications every time someone did RSVP, but obviously I couldn’t keep very good track of how many people had said they could come over a period of a couple weeks in my head.
I began to think, what if I could track the emails? And then send that information to a dashboard? But how? The emails weren’t just plain text – I’d need to parse them somehow, but that seemed way over my head, not to mention too time consuming.
The low-hanging fruit option was just to track every time I received an email from AnRSVP. This would not tell me whether the person said yes or no but it would give me an interesting overview of how many RSVPs came in over the month-ish period of time from their receipt to the deadline.
I still wanted to track actual responses too, though, so I ended up devising a method I was quite proud of. It doesn’t take hardly any time to implement but it sure did take me a long time to figure out.
Overall the things I’m going to cover are:
- Using IFTTT to stream a data point to an Initial State dashboard every time an email with a specific subject is received
- Using a combination of IFTTT and Google Sheets to parse email content, retrieve specific sheet data and stream that data to the same Initial State dashboard as above
- Using Initial State’s dashboard configuration tools to make my RSVP dashboard look exactly like I want it (it really doesn’t help your ego having everyone tell you that you can have everything just the way you want almost daily when you’re getting married)
Streaming Every Time an Email is Received
As you’ll soon realize, the secret sauce to most of this is IFTTT. I’m a little obsessed with it (their DO button app + SmartThings is pretty fun), and they happen to have a Gmail integration.
Note: Initial State does not yet have an official IFTTT integration (we’re working on it!); however, you can use the Maker channel to stream to Initial State just as effectively.
If you didn’t already know, If This Then That is a service that lets you setup triggers between different services (like Gmail). They call each set of triggers and actions a “recipe”. To stream something every time an email is received, follow these steps:
- Create an IFTTT account
- Create a Recipe
- Click on “this” then search for and select “Gmail” (you may be required to connect your Gmail account if you’ve never done it before)
- Select “New email in inbox from”
- Now enter the email address. For AnRSVP it’s “firstname.lastname@example.org” (this is also the email address that your account confirmation comes to, but by the time you’re receiving RSVPs you should only be getting RSVP emails). Click the “Create Trigger” button.
- Click on “that”, then search for and select the “Maker” channel
- Select “Make a web request”
- This is the part where we stream to Initial State. In the URL box, we are sending to the Initial State API. Be sure to put your Initial State access key after “accessKey=” and change your bucket key to whatever you want. I set mine to “rsvp”. The Method is “POST”, and Content Type is “application/json”. The Body is where we specify what we want the signal name to look like in our Initial State dashboard and which value to stream. Since we’re only streaming when something happens, we don’t need any values from the email. “key” is the name of the signal and “value” is the data we want sent. In this case, I made my key “:mailbox_with_mail:RSVP” and the value the chapel emoji “:wedding:”.
- Create the recipe!
- The last step is to create our Initial State bucket (it holds our data according to bucket key) so we can actually see the data coming in. You can do this a couple ways, but the easiest is just to create one directly from the bucket shelf. Click the +cloud icon at the top of your bucket shelf to create a new bucket. Be sure to name the bucket, then check the “Configure Endpoint Keys” checkbox to change the bucket key. Make this the same bucket key that you used in Step 8 (in my example, “rsvp”).
Sweet! You can test this out by sending a fake RSVP and just change buckets or signal name to get rid of the test.
What this basically allowed me to see is how frequently people RSVPed from the time they should have received their invite to the RSVP deadline (approximately a 25 day window).
Above is the histogram view of how many emails I received a day. Hovering over a certain bar would show me the time period and number of occurrences. It’s also a nice visual representation of what went on. We actually got an RSVP almost every day, but you can see that they trickled down closer to the deadline. The “62 Events” up at the top also immediately told me how many RSVPs total I had received. Now, each “RSVP” in this sense was really each invite, but we sent out 119…Just over 52% of our RSVPs were in before the deadline. And people wonder why weddings are stressful.
Retrieving Information from the Email
Watching RSVPs come in on the dashboard is cool and all, but I wanted to know numbers. The main issue I was encountering was that multiple people and their responses were often sent in one email (if they were part of the same invitation grouping). There could be mixed answers (No’s and Yes’s) as well, making this task even more complicated.
I spent a good deal of time looking into email parsers or how to manually do the parsing myself with Python. All proved to be either too inaccurate or too difficult.
I started looking at the IFTTT action integrations for inspiration. I knew that I could send the body of an email using the Gmail trigger, but it wouldn’t do me any good if I couldn’t extract specific words from it.
Then I saw the Google Sheets integration and read that I could set up IFTTT to append information from an email (i.e. subject, date/time, body) to the bottom of a Google Sheets spreadsheet. This sounded promising since I was fairly confident that I could find a formula to separate out the email body. This ended up being a bit harder than anticipated, but since I’ve figured it out already, it’ll be easy for you!
I then had to get that information out of Google Sheets and into Initial State. Thankfully, Google has an awesome API that let me pull data directly from my private spreadsheet using Python. Then it was just a matter of streaming it to Initial State!
So here is the basic workflow – an email comes into my connected Gmail account -> my Gmail filter labels the email as “RSVP” -> IFTTT triggers the “Add row to spreadsheet” action -> the time stamp and email body are appended to the designated spreadsheet -> a series of separations and calculations are performed on the body to produce Number Attending, Number Not Attending, and Number Responded -> a Python script using Google’s API retrieves this data and streams it to Initial State.
IFTTT Recipe to Append Email Body to a Google Sheets Spreadsheet
This first step is optional, but in my opinion it’s a bit “safer” and/or easier to keep track of. I’m talking about using Gmail filters to label incoming RSVP messages. To create a new filter, just click on the gear icon in the upper right corner of your Gmail page and select “Settings”. Click on “Filters and Blocked Addresses” and then “Create a new filter”. I made my filter easy – for emails with the subject “A person RSVPed” (click continue after entering that), apply the label “RSVP”. Voila!
Alrighty, we already went over the basics of getting to creating a recipe in IFTTT above, so we’ll skip straight to what we need to put it together:
- Create a new recipe and click on “this”, then search for and select “Gmail”
- This time choose “New email in inbox labeled” (unless you skipped labeling – then just pick “New email in inbox from” again)
- Enter the label you’re using (“RSVP” in my example) and create the trigger
- Click on “that”, then search for and select the “Google Drive” channel (you may be required to connect your Google Drive account if you’ve never done it before)
- Select “Add row to spreadsheet”
- Now fill out the Action fields. Name your spreadsheet whatever you’d like – IFTTT will create a new spreadsheet for you if one with that name doesn’t already exist. The Formatted row section comes pre-populated with every option, but I only wanted the timestamp and body (“ReceivedAt” and “BodyPlain”). Since we already have a recipe streaming immediately after an email comes in, the timestamp isn’t super important. I just liked having it so I included it. IFTTT will create a folder in your drive automatically unless you change the Drive folder path.
- Create the recipe!
- Optional: enter a test RSVP on AnRSVP to get an email to test your new workflow!
Some Google Sheets Formula Magic
Here comes the slightly confusing but totally awesome part. Once IFTTT appends one of those emails to your spreadsheet, it’ll look something like this:
We want to take out the “Celebrate in Person” part so that we know Bob Smith is attending. To do this, I added a sheet (I wanted to be extra safe and make sure I didn’t mess up IFTTT’s appending) and used this new sheet for all of my parsing.
My parsing involved RegEx, or Regular Expressions. I knew nothing about them before I tried using them, but I soon learned that they are some sort of ancient code for performing black magic. But if I can figure it out on my own, surely you can copy-paste my hard work! 😉
After messing around with various forms of RegExExtract inside of Sheets, I read in a forum somewhere that my application might actually need a third party script that combined RegExExtract with some other regular expressions. This script is called RegExExtractAllMatches. Since it’s not built into Google Sheets, you have to add it manually (which is way easier than it sounds).
Simply click on “Tools” in the Sheets menu bar and then select “Script editor…” You’ll be taken to a new window with a large blank workspace. Just copy-paste what I have below and click save to add the new RegEx function:
Head on back to that extra sheet you made in your RSVP spreadsheet (in my case, Sheet2). In the very first cell we want to enter the formula =RegExExtractAllMatches(Sheet1!B1,“(?:Person|Spirit)”). What we are doing is using RegExExtractAllMatches to find every occurrence of the words “Person” or “Spirit” (which represent a yes, Celebrate in Person, or no, Celebrate in Spirit, in my case – I was trying to be cute). Since I sent out approximately 110 invitations (which, if you’ll remember, is how the RSVPs were grouped), I copied this formula down to row 130 or so.
You don’t want to put anything in Columns B, C, etc., because our formula is extracting every mention of “Person” or “Spirit” and will populate those columns with those values. So, for instance, an invite with 3 people on it would fill 3 columns with either “Person” or “Spirit” based on their response.
Now we are going to create a third sheet that counts the response as a 1 (for attending) or a 0 (for not) and then vice versa. This way we can have a running tally of how many yes’s and how many no’s we’ve received.
Under “Not Attending”, I placed the formula =IFERROR(COUNTIF(Sheet2!A1:H1,“Spirit”)). This counts every time the word “Spirit” shows up in row 1.
I then copied both of these formulas down to row 130.
The final piece in our Google Sheets masterpiece is one final sheet. This sheet will hold our final numbers and be where we stream our values from.
I had 3 columns: “Total Attending”, “Total Not Attending”, and “Total Responded”. Their values came from super simple formulas for adding up all that hard work we did earlier. “Total Attending” summed up the count in Sheet3 for “Attending”: =SUM(Sheet3!A2:A150). “Total Not Attending” summed up the count in Sheet3 for “Not Attending”: =SUM(Sheet3!B2:B150), and “Total Responded” just added up those two: =SUM(A2,B2).
And that’s all of our Google Sheets magic! Time to get this valuable data into Initial State.
Streaming from Google Sheets to a Dashboard
Deep down I knew that Google had to have a way to fetch data from Sheets. And, not only was I right, but it’s soooooooo easy.
Go to their API page here: https://developers.google.com/sheets/quickstart/python#step_1_turn_on_the_api_name
As you’ll notice, you can use a variety of different languages. I picked Python because Initial State has a Python library for streaming.
All you need to do is follow Google’s instructions exactly. The only time I encountered a problem was in Step 2 – I had to substitute easy_install for pip.
The sample script should give you a bit of an idea about what the API can do, and it’s pretty easy to edit for our purposes!
Before we go customizing anything though, we need to install the Initial State streamer (if you haven’t already):
- Create an Access Key in your Initial State account (can do on the “Manage My Account” page under “Streaming Access Keys”). If you don’t have an account you can sign up here! (it’s free)
- Make sure you have an internet connection.
- Type this command into the command line:
\curl -sSL https://get.initialstate.com/python -o - | sudo bash
- Follow the prompts – if you say “Y” to the “Create an example script?” prompt, then you can designate where you’d like the script and what you’d like to name it. Your Initial State username and password will also be requested so that it can autofill your Access Key. If you say “n” then a script won’t be created, but the streamer will be ready for use.
- Either use your example script to get your Access Key or create one from scratch and start streaming!
Now we can edit our script. Here is what my script looked like after I edited the example:
On line 5 you’ll notice that we’ve added the line to import the Initial State Streamer (from ISStreamer.Streamer import Streamer).
You’ll also notice that on line 25 we initialize the streamer with our bucket_key (“rsvp” if you followed my example) and access_key (found on your account page). We don’t include the bucket_name because our bucket was already created by the first IFTTT recipe.
Now scroll aaaaaall the way down to line 70. We need to put in our Spreadsheet Id – this Id is just the series of letters and numbers after the /d/ and before the next / in your spreadsheet URL:
Line 72, with “rangeName”, is where we put where the cells we’re looking for can be found. On my spreadsheet, I wanted data from A2, B2, and C2 on Sheet4, so my range was Sheet4!A2:C2.
The else statement that starts on line 79 is where the magic happens. Any “print” statements just print to the command line (I use this for debug) and any “streamer.log” statements send data to Initial State.
Row corresponds to cell A2, row to cell B2 and row to cell C2. I also made sure to name them accordingly as “Attending”, “Not Attending” and “Responded”.
Finally, on line 91 we sleep for 900 seconds or 15 minutes.
I saved this script as “readsheet.py”. If I was more concerned with running it frequently, I would have set it up on something like a Raspberry Pi, but since I didn’t really need it polling that frequently, I just ran it on my laptop. Make sure you’re in the same directory as your file, and type this in your command line:
If you’ve got numbers in your spreadsheet already, you should see them print out! Remember, this only runs every 15 minutes, so changes may take that long to show up.
Checking out the Dashboard
Head on over to Initial State to see your data!
I’m using the timeline to only view the time between when invites went out and when RSVPs were due (ah, only 50% RSVP’d by the deadline? Classic). “Progress to Estimated Headcount” was a custom tile I created to gauge how many people had responded “Not Attending” against how many we needed not to come (awful, I know, but vendors need head counts and I had to give numbers).
I was also able to share the dashboard publicly so that my family/fiance could check it for themselves.
Now, what on earth can we take from this tutorial aside from IFTTT lets us stream anything, Google Sheets makes a great email parser, and Initial State has cool dashboards? Maybe RSVP ON TIME. PLEASE. I HAVE PEOPLE TO ANSWER TO AND LOTS OF MONEY ON THE LINE. OK. THANKS.