Use Slack, Google Sheets, and Regex Magic to set up a Guest List for Events
For Understated Events, we were deciding a way to efficiently manage our comped guest list. We’re a low budget operation (and by low budget I mean we straight-up don’t have money), so services like Gjests and zkipster aren’t an option. Initially, we were writing down our guest list on paper, which was ridiculously inefficient when a crowd was rushing in and we had no quick way of searching if someone was on the list.
What we needed was:
- Digital guest list, for simple searching
- Easy to add guests
- Accountability on behalf of who is adding people
So, I did some researching online and found that there was a way to integrate Slack with Google Sheets using apps on the Slack platform. How it works is if a message goes through on Slack, you can add it as a row on Sheets. A bit spammy for our chat channel, but it does encourage accountability for who you add… and there’s always an option to mute a channel. 😉
I used JD Maresco’s Stand-ups Tutorial here as a guide, and then adapted it so it worked as a guest list.
1. Send a message in Slack.
Use ic: [Name] for an Industry Comp, add: [Name] for a friend.
You can also add the number of +1’s they have, but it’s optional.
Syntax: ic:/add: [Name] (+ [Number +1's]) (; [Name] (+ [Number +1's]))
2. Use Guest List. Mark if they’ve been checked in on the # Checked In Column. For every +1, they check in, I write the number down. If it’s just them alone, I fill in Y, but use it however you’d like.
3. Enjoy statistics.
Lets make it happen!
Initialize Google Sheets
NOTE: If you like the spreadsheet the way it is and want to save time, make a copy of my spreadsheet that is already set up using File > Make a copy. Copy link here.
Guest List Timestamp, User, Industry( Y/ N), Guest Name,# of+ 1' s,# Checked In Stats User List,# Guests 0, TOTAL…docs.google.com
Now skip to step 3.
1. Freeze top row.
2. Fill in column categories & named ranges. Data > Named Ranges. This allows us to reference the columns easier when we are writing the script.
3. Deploy your script to get the Web App URL. Tools > Script Editor
4. Publish > Deploy as Web App using these settings:
Execute the app as: Me
Who has access to the app: Anyone, even anonymous
5. Save the Current web app URL to your clipboard.
- Install Outgoing WebHooks to your Slack channel. This app looks for keywords in messages, and then sends data out. Your link should be: https://[your-team-name].slack.com/apps/A0F7VRG6Q-outgoing-webhooks
2. Set up Integration settings (bottom of the page)
- Channel: #guestlist If you don’t have one set up yet, there’s an option to “create a new channel”.
- Trigger words: add:, ic: (ic: stands for industry comp whereas add: is just used for friends/family)
- URL(s): [Paste your Web App URL here]
3. Save the generated Token to your clipboard.
4. (Optional) If you’d like to get fancy, you can see how I set up the rest of the settings:
5. Save your settings!
Back to the Script
1. Paste this into your Google Sheets.
2. Edit these parameters:
- [Slack Token] (Line 9): What you copy and pasted from above.
- [Spreadsheet ID] (Line 46): Go to the sheets URL and copy and paste from the URL.
3. Deploy the script as a web app like before. It should be working now!
I’ll be adding more features later, but hopefully this is helpful for now. I’m looking to add something where you can check how many people on the guest list using a trigger word cause you know I love numerical data.
Hope this helps save some money! Feel free to reach out if anyone has questions or improvements.