Kimberlee Johnson

❣️ Remind volunteers when and where to be with Twilio, Airtable, and Zapier

Helping COVID-19 community response teams coordinate neighbors helping neighbors. This post was originally shared on Dev.to.

Automating volunteer reminder texts for COVID-19 community response teams

If we follow Mr. Rogers’ advice to look for the helpers right now, we will find them. Team Rubicon has distributed hundreds of thousands of masks and gloves to healthcare workers in Colorado. In New York City, Backpacks for the Street has shared 1,200 COVID-19 prevention kits with residents experiencing homelessness. Yesterday, TogetherSF volunteers delivered 35,000 steaks to Bay Area food banks.

Organizing these efforts takes a lot of communication, and it can be challenging to stay on top of it all, especially as a resource-constrained nonprofit. It would be impossible to manually text every volunteer who signs up for a shift to remind them when and where to be, but Airtable, Twilio, and Zapier can automate that for us.

Setting up an Airtable Base

David Rose asks Alexis Rose if it is a spreadsheet

Airtable is a database spreadsheet hybrid that helps with all things organization. The nonprofit I work with uses Airtable to collect volunteer sign-ups and to match them with volunteering opportunities, so I dived into their existing system to see how I could work with it.

After looking at their setup and reading up on Airtable’s docs and community forums, I learned I would need to change a few things to get automated text reminders working.

Have a look at the Volunteer Coordination Base template, and I’ll walk you through what’s special.

The Events tab (or table) lists out the volunteering opportunities that come our way, including who asked for help (typically somebody in the Needs table), the kind of help they're asking for, and when and where we've scheduled a time to support.

The first change I made was to make the When field in Events more granular, adding in a timestamp. It took me longer than I’d like to admit to spot the “Include a time field” toggle in Airtable, but can I really be blamed -- what is time these days?

Time Toggle within Airtable

While there is a Date type for columns in Airtable, there is no separate Time type, so I needed to come up with a workaround. I knew I needed to send a text reminder n hours before an event, so a column that calculated the number of hours until an event cold fit my use case. I added in an Hours Until Event formula column to sort it out with DATETIME_DIFF({When}, NOW(), 'hours').

Hours Until Event column in Airtable setup

The nonprofit I worked with already included a Matched Volunteers column to list out the volunteers that would be at an event. Next up I needed to get all of those volunteers’ phone numbers into the Events table too. I used a Lookup column type to grab the phone numbers from Volunteers Matched.

Volunteer Phone Numbers column in Airtable

This next part might spare you the time I spent debugging later (Again, what is time, anyway?). We need to convert our numbers into E.164 format so that we can text them. I created a new Formatted Phone Numbers column with a formula that added the necessary +1 for this US-based nonprofit (it’s really +{CountryCode}), replaced all of the nonnumeric characters with empty strings using SUBSTITUTE, and made sure all of the volunteers’ numbers would be in a comma separated list using ARRAYJOIN: SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ARRAYJOIN({Volunteer Phone Numbers}, ","),"(","+1"),")",""), "-","")

Formatted phone number column setup

With all that in place, I set up an Airtable View to filter for records where Hours Until Event hits the point when I need to send a text, so fewer than four hours before. I also added a filter to make sure we’re only looking at events that haven’t happened yet, where their Hours Until Event hasn’t dropped below zero. I called this View Volunteer Reminder Texts.

Airtable View setup

Airtable should be good to go from here! Time to leave spreadsheets behind for a minute and head over to Twilio.

Setting up Twilio

Phone with blinking heart nose

Developers use Twilio to programmatically send and receive calls and texts, but the limit really does not exist. Chloe Condon and I once used it to build a Mean Girls’ day bot, and Twilio Champions get up to all kinds of projects.

From my experience with Twilio, I knew it could handle texting this nonprofit’s volunteers. Here’s where to sign up for an account if you don’t have one already. You will also need a Twilio phone number, which you can set up from your Console.

Putting it all together with Zapier

Zapier automates connecting different apps together (you can sign up here).

Those apps include Airtable and Twilio. I clicked “Connect these Apps” and had my Airtable API key and Twilio API key both handy for the setup process.

Once the accounts were linked, I specified that when a new record popped up in an Airtable View, specifically Volunteer Reminder Texts, Zapier should tell Twilio to text those volunteers. Since my Hours Until Event column automatically calculates, new records are added to the View automatically, meaning the texts will be automatic too.

And, since Zapier pulls in all the data from the View, it also automatically pulls in the specific When and Where for each record, so your texts will be personalized for every event.

Zapier setup

You should see whatever you specify in Zapier show up in your test text, and, if you turn on the Zap, they’ll show up again four hours before the event too:

Screenshot of text

After the event

And that was it! There are lots of things I could do to improve, and I know there's loads more I can do with Twilio and Airtable. We’re considering automated feedback surveys once an event is done, for example.

I’m excited to explore all the options. I thought these automated reminders would involve a lot more time and work, and I was surprised to find a practically no code solution. This was another lesson for me that it’s always good to start looking at systems already in place, meeting your users, in my case volunteer organizers, where they’re at, and understanding their needs before jumping into building something from scratch. I love when existing tools free up time to be a helper in other ways.

Resources

And, a note! The cover photo for this post is courtesy fnnch and ABC News.