Build an App to Send SMS Surveys with Twilio + Airtable on Standard Library
Tags: twilio, node, webdev, javascript
Published: 2019-09-24 20:46:29

---
title: Build an App to Send SMS Surveys with Twilio + Airtable on Standard Library
published: True
canonical_url: https://medium.com/@brimm_reaper/build-an-app-to-send-sms-surveys-with-twilio-airtable-on-standard-library-ef5be1cd4f0b
description: Build an SMS Survey App that Immediately Captures Audience Feedback and Questions with Airtable and Twilio. 

tags: Twilio, nodejs, WebDev, Javascript
---

[We live in an era of collaboration and teamwork.](https://medium.com/@brimm_reaper/build-an-app-to-send-sms-surveys-with-twilio-airtable-on-standard-library-ef5be1cd4f0b) Meaning that even the most straightforward task in this day and age (think ordering lunch, for example) is often brought before a committee vote. Thankfully, ours is also an era of great technological achievement, and there is perhaps no better way to arrive at a consensus than through the use of polling and surveys. Today we are going to see how we can use [Build on Standard Library](https://build.stdlib.com/) to deploy a quick and easy-to-use polling app that uses your [Twilio](https://twilio.com/) number to dispatch SMS messages to your group, and then logs their replies in one convenient [Airtable](https://airtable.com/) Base.

![What our Airtable Base will look like upon completion!](https://miro.medium.com/max/4192/1*805LddiSsiMftzqZ3Aw0Bw.png)

#What You’ll Need Beforehand#

1x Airtable account — https://www.airtable.com
1x Twilio account — https://www.twilio.com
1x Standard Library Account — https://www.stdlib.com

#Step 1: Setting Up Airtable#
The first thing that we want to do is set up our Airtable Base so that it is ready to begin receiving data. If you are a new user, sign up for an account by [visiting this link](https://www.airtable.com/), and then [click here to add our Base template to your Airtable Workspace](https://airtable.com/addBaseFromShare/shrf3W1JpexdDzfio). You should see a Base that looks like the above screenshot, but without the polarizing question already populated. As you can see, our base has three tables, two of which require some input from you, and one that will be entirely populated by Standard Library. Let’s take a closer look at what each of these tables is responsible for tracking.

**Questions** —This table tracks your current and past questions. Questions go in column one, and the other columns will be handled by our application

**Contacts** — This will be a table of individuals who are a part of your group. The only required field here is the phone number(*country code* + *area code* + *phone number*. 14155309876 for example). The name field is optional

**Replies** — This table will be entirely populated by our application. It stores the returned text message, along with who sent the reply, and which question the reply was meant for

For the sake of this tutorial, you will need to add at least one number to the contacts table (I would recommend testing with your own cell phone number) and one question to the questions table (feel free to continue the GOAT debate if you would like). Now that the Base is set up, let’s start our workflow on Standard Library!

#Step 2: Build Your Workflow on Standard Library#

If you haven’t done so yet, get yourself a [Standard Library account](https://www.stdlib.com/) (it’s free!) and head on over to https://build.stdlib.com to begin building out your workflow. For this project we will want to trigger our survey by visiting a URL, so underneath **When This Event Happens** choose **HTTP or Webhook** as your event, and proceed to put in the following options:

**When This Event Happens**

HTTP or Webhook → HTTP Request is sent to Project Endpoint → **send-survey**

**This Workflow Will be Triggered**

- Airtable → Select Rows by querying a Base

- Airtable → Select Rows by querying a Base

- Twilio → Send a message

![You are now ready to click Create Workflow!](https://miro.medium.com/max/6444/1*ZpPd8B2sce7vTH9KISlv2Q.png)You are now ready to click Create Workflow!

#Step 3: Link Your Resources#

The next order of business is linking your Twilio and Airtable accounts with an Identity on Standard Library. For those unfamiliar, linking a resource on Standard Library allows you to securely set up your accounts once, and then makes them available to you across all of your workflows. Let’s start by linking an Airtable account and choosing a Base. Click on the **Link Resource** button to be presented with the following screen:
![](https://miro.medium.com/max/2448/1*NnpJHr00HbBHavBtMZqHfA.png)If you have previously linked Airtable Bases, they will appear here.

If this is your first time linking your Airtable account, click the **Add New Account** button and input a display name on the following screen. You will also need to [retrieve your API key from Airtable](https://www.airtable.com/account) and include that here as well. It should look something like this:

![](https://miro.medium.com/max/2416/1*m6SIpjaX1pi54nU50pqpvQ.png)Click on Finish and proceed to choose your Base!

Now you will arrive at a screen presenting you with all of your Bases. Choose the one that you added earlier from our template called **Twilio Survey**, and then click **Finish [Link Base].**


![](https://miro.medium.com/max/2432/1*zo3ip9A8L9bKDYYRsme0lw.png)An inventory of all your Airtable Bases.

That takes care of Airtable, so now you can proceed to link a Twilio number to your project. The process will look very similar to the Airtable process at first, and you will see the same pop-up screen asking you to **Add New Account** or **Link New Resource,** depending on whether or not you have used Twilio on Standard Library in the past. If you see a number that you would like to use for this project here, click on the green **Choose** button and move on to the next step.

If you do not have a number linked yet, proceed to click either **Add New Account** or **Link New Resource.** After the loading screen finishes, you should see something like the following:

![](https://miro.medium.com/max/2444/1*1QE5VVbrUAI5icBU_gWF3A.png)Twilio numbers purchased on Standard Library will show up here.

**Note:** If you have numbers that you have purchased on Twilio outside of Standard Library, you will notice that they are absent from this screen. This is the result how Twilio Connect apps work. To read more about Twilio Connect apps and sub-accounts, [click here](https://support.twilio.com/hc/en-us/articles/223135007-What-is-Twilio-Connect-).

You can now either select a previously linked number that you have purchased through Standard Library, or purchase a new number to use for this project. Once you have done that, click on the blue **Finish [Link Phone Number]** button, and you will see the following:

![](https://miro.medium.com/max/3232/1*B-2ZMliAIS2qEYzT8PRlcA.png) With your **Identity Generated** you can now click on **Next** button. 

#Part 4: Set Up Your Workflow#
The following screen is where we will configure the workflow APIs that we selected earlier. We will start with our initial query. Where the interface asks for a **‘table’** fill in **‘Contacts’**. Leave all other fields blank, since we want the query to return all of the numbers in the table. Your window should now look like this:

![](https://miro.medium.com/max/3172/1*BTD2zzRDu3swURkc-0zcLg.png) Our first query to grab all of the numbers in our Contacts table.

Now click on the six dots next to the second row of our workflow, that says **Airtable → Select Rows by querying a Base.** You will see a new blank query. Fill it in with the following:

- table → **Questions**

- where → key: **wasSent** → select: is **NULL**

- Click on the **blue plus sign** next to **Add a new AND clause to this KeyQL Query operation**

In this new box, enter: where → key: **Status** → select: **is equal to** → type in: **Pending**

![](https://miro.medium.com/max/3100/1*zkyoErVBucg7ERgD3EcLYw.png)Searching Questions for an eligible question.

Next, click on the six dots to the left of **Twilio** at the top of our dialog box. We have the option to enter up to four values here, but we only need two. In the to: field enter:

`${result.step1.selectQueryResult.rows[0].fields.Number}`

Then, in the body: field enter:

`${result.step2.selectQueryResult.rows[0].fields.Question}`

![](https://miro.medium.com/max/3128/1*73YuS0svGZpUDDGNPzv_rw.png)Set up the Twilio workflow like this.

Click on the green **Run with Test Event** button at the bottom of the dialog box and you should receive a text with the question that you entered into Airtable. If you did, then it is working!

While it is possible to do most of what we need to do in the Build interface, we will need to make some customizations to our code to store the results of multiple queries (what if our Contacts table has more than one number?). In order to do all of these things, we will need to briefly dive under the hood of Build by switching the **Developer Mode** button to **On.** ![](https://miro.medium.com/max/3128/1*qyP-TxAkMlJmsCDVDw4org.png)

Here you have a behind the scenes look at what all your hard work has been producing. This is the code that has been generated by our workflow. Replace everything inside of here with the following snippet.

{% gist https://gist.github.com/k-2tha-brimm/2111da5e65c3e2519e599c81fee87f0e.js %}

**Note:** Please be aware that toggling **Developer Mode** to **Off** will cause all of your changes to be lost, so best to keep it activated until we have shipped the project.

The first half of this will allow us to grab 1. all of the numbers that we have included in the **Contacts** table, and 2. the question that we want to send from the **Questions** table. Note that the criteria for selecting a question is that the **wasSent** column is **unchecked**(null), and that the **Status** column reads **Pending.** In the event that there are two or more questions that match this query, only the most recently added one will be sent.

This second half of this code allows us to perform the Send a message action from our linked Twilio account to every user that we have inserted into our Contacts table, and then changes the wasSent value for the question to true. Now click on the green **Run with Test Event** button again, and you should receive a message with your first question!

![](https://miro.medium.com/max/1440/1*TBIH_pu-wucStS88EA2HJA.png)

If you received the text message, and if the **wasSent** column in your table was updated to **true** (that is to say, the column now has a green check!), then proceed to click on the blue **Next** button. On this final page you will name your project (name it **twilio-survey**) and then go ahead and click on the blue **Alright, Ship It!** button. You will receive a message informing you that you are awesome. Well done!

It is important to note before moving ahead to the next section that in order to conduct our survey going forward, *you will need to ping the URL that is being generated during this step*. If you recall, when we began setting up our workflow we decided that **HTTP Request is sent to Project Endpoint** would be the event that triggers these actions. Meaning that if you ever want to conduct another survey, you will need to ping the URL generated by this workflow. It will look like this:

`https://<Your-Username>.api.stdlib.com/twilio-survey@dev/send-message/`

Where <Your-Username> is replaced with your Standard Library account name. **twilio-survey** is the name of our project, and **send-message** is the name of the endpoint that we set up when we determined our event.

#Part 5: Storing Replies#

We now need some way to track replies. This will require setting up a new event in our project to insert SMS messages to our Twilio number into our Airtable Base. Navigate back into your project by clicking on the **dev (click to manage)** link on your project’s homepage:

![](https://miro.medium.com/max/6248/1*Rb2iTPszw9P1lJfvwbZP6g.png) 

Find the box that enables you to add new events. It will be located just under the previous workflow that you created. You will see greyed out text that reads **Event Source** and **When this Event happens…**

For this workflow, we want to choose:

- Twilio → sms.received

Your **Integrations** section should now look like this:

![](https://miro.medium.com/max/6248/1*35rRmxYGiLHPv4mxrYYs-Q.png)

After you have clicked on [+] Add New Workflow, set your workflow up as follows on the dialog page:

- Airtable → Select Rows by querying a Base

- Airtable → Select Rows by querying a Base

- Airtable → Insert a row into a Base

Proceed by clicking **Next**, and you should see the previously linked resources. Feel free to simply click **Next**. Here, we will once again be taking the plunge into **Developer Mode**. Toggle this to **On**, and paste the following snippet into the editable portion of the box:

{% gist https://gist.github.com/k-2tha-brimm/1abcaffd5e5a7d3f395e4b7150c3fda0.js %}

Let’s cover quickly what we are doing here.

1. We are querying the Base to determine which question is currently active

2. We are finding the user associated with the responding phone number

3. We are creating a new record in the **Replies** table, and linking this reply to the previously selected question and user, respectively

Testing this code will result in an error, however, so we need to make one change to the test event.

![](https://miro.medium.com/max/3044/1*jl5TmP292K2vvEZh5OPPGA.png)

If you click the gear icon next to the Run with Test Environment button, you will see something that looks like this:

![](https://miro.medium.com/max/3024/1*N3RPW3eTAmX2bBUh5_wE1g.png) Here, we need to update our From phone number.

Our workflow is attempting to find a user that has the number associated with the **‘From’** key inside this event. Change this value **(“+15555555555”)**, to a number that is present in your **Contacts** table (i.e. your phone number with a preceding “+”, like so: **“+18155451993”.** The “+” and the country code are required here). Leave all the other values as they are, and attempt to run the test again. If you now see the message **“Hello from Twilio!”** in your **Replies** table, then congrats! You’re all finished. Proceed to click on **Next**, and finally **Ship It!**

![](https://miro.medium.com/max/1432/1*tf1ANObCwLloqNsoR2hoUA.png)


#You’re All Set!

That is it! Sit back and watch the replies come rolling in. As we mentioned earlier in this tutorial, ensure that there is only one question that carries a Status of Pending, with a wasSent value of true, as the first question to meet both of these criteria will be the one that replies are logged to. When you feel that a question has ample replies, simply change the Status field for that question to Finished, and then proceed to add a new question. Navigate to your URL endpoint to deliver the new question. Happy polling!

Kevin Brimmerman is a Software Engineer at Standard Library. Outside of work he is an avid runner and a die-hard Chicago sports fan. Go Cubs!