Writing to Airtable from your Twilio app

Time to read: 4 minutes

April 28, 2020
Written by
Tilde Thurium
Twilion
Reviewed by
Lizzie Siegle
Twilion
Sam Agnew
Twilion

Writing to Airtable from a Twilio serverless function

Airtable is an awesome product. It’s like a cross between a spreadsheet and a database, but with a lot more flexibility for creating dynamic views of your data. In this post you’ll learn how to write to Airtable from a Twilio application. The advantages of doing so are as follows:

  • Rapid prototyping. If you need to persistently store data, it’s easier to create an Airtable base than to set up a proper database.
  • Maintenance flexibility. Folks who don’t know how to code can easily maintain and extend the data.
  • Seamless authentication. Sure, you could use Google Sheets as a lightweight database, but authenticating with Google Sheets API can be tricky.

Today we’ll be building a Net Promoter Score (NPS) app that writes data from SMS messages to Airtable. NPS is a calculated value summarizing how a group of people feel about something -- a brand, an event, whatever. That said, this code is just a starting point. Feel free to customize it for whatever your use case might be.

Prerequisites

Airtable 101

In order to use Airtable, you need to be familiar with a few key concepts. If you’re already an Airtable whiz, feel free to skip to the “Writing to Airtable with a serverless function” section.

  • Bases. According to Airtable documentation, “A base is a database that contains all of the information you need for a specific project.” 
  • Bases are made up of one or more tables. If we’re going with the spreadsheet metaphor, tables are like different tabs.
  • Records are units of data, analogous to a row on a spreadsheet.
  • Information in records is stored in fields, which are kind of like a column on a spreadsheet.
  • Workspaces are folders where you can organize your bases so that other folks can collaborate with you.        

To create a new base you can:

I’ve created a base called “Net Promoter Score” that we’ll be working with today. You can view it here and create your own copy with the “Copy base” button on the right-hand side.

This base contains an “Input” table that has 4 fields:

  • Number: a unique, automatically incremented field type. The first field in a base is the primary field and must be unique. It’s kind of like a relational database’s primary key.
  • Score: a number field, representing how good (or bad!) the survey responders feel.
  • Reason: a long text field allowing the survey responders to provide more context on why they feel that way.
  • Promoter or Detractor: a formula field performing an intermediate calculation that helps us get to our final NPS score. A rollup calculation at the bottom of this field gives us our final NPS score.

Screenshot of an Airtable base using the schema described in the previous paragraph. There's one field. Number=1, Score=10, Reason="The host was wearing the coolest bow tie I'd ever seen." The calculated NPS value is 100.

Writing to Airtable with a serverless function

For this project we'll use a Twilio Function for writing data to the Airtable base. Open the Functions configuration dashboard. Generate an Airtable API key and add it as an environment variable named AIRTABLE_API_KEY.

Be careful not to commit your Airtable API key to code that’s going up on GitHub, or share it with anybody. If you do they can gain full access to your Airtable account.

Go to the Airtable API landing page. You’ll see links to all your bases. Click through on the link to the “Net Promoter Score” base.

Screenshot of Airtable API's landing page. There are links to 2 bases: "Cute Animals" and "Net Promoter Score."

On this page, you’ll see some (nifty!) auto-generated API documentation. We’ll need the unique ID of the Net Promoter Score base, so copy that to your clipboard.

Screenshot of auto-generated Airtable API documentation for the Net Promoter Score base. The section that says "The ID of this base is apXXXXXXXXXXXX" is what we're after.

Go back to the Twilio functions dashboard. Add the Airtabse base ID as an environment variable called AIRTABLE_BASE_ID.

While you’re at it, add the Airtable Node.js SDK to the Dependencies section of the Functions configuration dashboard. Here we’re using version ^0.8.1.

Screenshot of the Twilio Functions configuration dashboard. There are 2 environment variables, AIRTABLE_API_KEY and AIRTABLE_BASE_ID. There are 2 dependencies, airtable version ^0.8.1, and twilio version 3.29.2.

Let’s write a Function that, given a string and a number, writes a new row to our NPS base. Using the “Blank” template, create a new Function called `writeNPS`. Give it a path, `/write-nps`. Copy the following code into it:

const airtable = require("airtable");
const twilio = require("twilio");

exports.handler = function (context, event, callback) {
 const base = new airtable({
   apiKey: context.AIRTABLE_API_KEY,
 }).base(context.AIRTABLE_BASE_ID);
 base("Input").create(
   [
     {
       fields: {
         Reason: event.reason,
         Score: parseInt(event.numericalScore),
       },
     },
   ],
   function (error, records) {
     if (error) {
       console.error(error);
       callback(error);
       return;
     } else {
       callback(null, "Success!");
     }
   }
 );
};

Uncheck the box that says “Check for valid Twilio signature” so we can test this function by running local commands. Copy your function URL into the code below and run it from from the command line:

curl --request POST 'https://glitter-maltese-4792.twil.io/write-nps?numericalScore=7&reason=You%20had%20impeccable%20can%C3%A1pes.' \
--header 'Content-Type: application/x-www-form-urlencoded'
Success!

Now we’ve added another record to the Airtable base. Hooray!

Screenshot of the Net Promoter Score airtable base. Now there is a new row. Number=2, Score=7, Reason="You had impeccable canapes." Calculated NPS score is 50.

What is even happening in this function? Let’s break it down.

  • First, we instantiate the Airtable base.
  • Then we call the `create` method, to create a new row. Although we’re only creating one row here, this method accepts a list of objects if you want to bulk create.
  • The second argument to create is a function allowing us to do some error handling and call the `callback` to let Twilio know our function is done executing.

Warning: as I found out the hard way, Airtable creates empty rows for failed requests.

me: makes an error during Airtable API request
Airtable: HEY EVERYONE LOOK AT THIS FAILED REQUEST

Animated GIF of Bart Simpson dumping a cake that says "at least you tried" into the trash can.

Type issues are another gotcha. In the previous code sample we needed to convert strings to numbers for the Score field. If not, the request will fail because the Score field uses the “Number” data type. Airtable offers automatic type coercion that you can enable if you want to live dangerously. Then again, we’re using JavaScript here which already has a bunch of weird type coercion edge cases so YOLO.

To enable type conversion, pass the typecast parameter in to the create method like so:

 base("Input").create(
   [
     {
       fields: {
         Reason: event.reason,
         Score: event.numericalScore,
       },
     },
   ],
   { typecast: true },
   function (error, records) {
   ...

To test this you can run the same cURL command you ran previously since the inputs to the function haven’t changed.

Airtable also has methods for updating and deleting records, though they aren’t super applicable for our use case. Check out the Airtable API documentation for details.

Adding a Studio flow for Net Promoter Score

Twilio Studio is the fastest way to get multi-step messaging flows up and running. To save time, you can copy my flow by importing the following JSON into Studio. Before importing you’ll need to replace the example.com URL with the URL of the function you just wrote.

{
  "description": "NPS Survey",
  "states": [
    {
      "name": "Trigger",
      "type": "trigger",
      "transitions": [
        {
          "next": "NPS-Numerical-Score",
          "event": "incomingMessage"
        },
        {
          "event": "incomingCall"
        },
        {
          "event": "incomingRequest"
        }
      ],
      "properties": {
        "offset": {
          "x": 0,
          "y": 0
        }
      }
    },
    {
      "name": "NPS-Numerical-Score",
      "type": "send-and-wait-for-reply",
      "transitions": [
        {
          "next": "NPS-Text-Reason",
          "event": "incomingMessage"
        },
        {
          "event": "timeout"
        },
        {
          "event": "deliveryFailure"
        }
      ],
      "properties": {
        "offset": {
          "x": -140,
          "y": 250
        },
        "service": "{{trigger.message.InstanceSid}}",
        "channel": "{{trigger.message.ChannelSid}}",
        "from": "{{flow.channel.address}}",
        "body": "On a scale of 1-10, how likely are you to recommend this event to a friend?",
        "timeout": 3600
      }
    },
    {
      "name": "send-thanks",
      "type": "send-message",
      "transitions": [
        {
          "event": "sent"
        },
        {
          "event": "failed"
        }
      ],
      "properties": {
        "offset": {
          "x": -110,
          "y": 930
        },
        "service": "{{trigger.message.InstanceSid}}",
        "channel": "{{trigger.message.ChannelSid}}",
        "from": "{{flow.channel.address}}",
        "to": "{{contact.channel.address}}",
        "body": "Thank you so much for the feedback, and most importantly for participating in the event!"
      }
    },
    {
      "name": "NPS-Text-Reason",
      "type": "send-and-wait-for-reply",
      "transitions": [
        {
          "next": "write-data",
          "event": "incomingMessage"
        },
        {
          "event": "timeout"
        },
        {
          "event": "deliveryFailure"
        }
      ],
      "properties": {
        "offset": {
          "x": -130,
          "y": 510
        },
        "service": "{{trigger.message.InstanceSid}}",
        "channel": "{{trigger.message.ChannelSid}}",
        "from": "{{flow.channel.address}}",
        "body": "What's your primary reason for giving that score?",
        "timeout": 3600
      }
    },
    {
      "name": "write-data",
      "type": "make-http-request",
      "transitions": [
        {
          "next": "send-thanks",
          "event": "success"
        },
        {
          "event": "failed"
        }
      ],
      "properties": {
        "offset": {
          "x": -120,
          "y": 710
        },
        "method": "POST",
        "content_type": "application/x-www-form-urlencoded;charset=utf-8",
        "parameters": [
          {
            "value": "{{widgets.NPS-Numerical-Score.inbound.Body}}",
            "key": "numericalScore"
          },
          {
            "value": "{{widgets.NPS-Text-Reason.inbound.Body}}",
            "key": "reason"
          }
        ],
        "url": "http://example.com"
      }
    }
  ],
  "initial_state": "Trigger",
  "flags": {
    "allow_concurrent_calls": true
  }
}

After you’ve imported the Studio flow, hook it up to your Twilio phone number.

Screenshot of Twilio phone number configuration. Under "Messaging", "Webhooks, TwiML Bins, Functions, Studio, or "Proxy" is selected. When a message comes in, "Studio Flow" and "NPS Survey" are selected.

Text your number anything to get started. Answer the questions. When you’re finished, watch your data appear in the Airtable base.

Conclusion: writing to an Airtable base from your Twilio app

In this post, you’ve learned to:

  • Write data to an Airtable base with a serverless function
  • Import a Twilio Studio flow that performs a NPS survey

If you want to learn more about Airtable, check out this post about how to read Airtable data from a Twilio Function. In the meantime, hit me up on Twitter or over email (tthurium [at] twilio [dot] com) if you have any questions.

 

Related Posts

Related Resources

A newspaper article

Twilio Docs

From APIs to SDKs to sample apps

API reference documentation, SDKs, helper libraries, quickstarts, and tutorials for your language and platform.
An Open book

Resource Center

The latest ebooks, industry reports, and webinars

Learn from customer engagement experts to improve your own communication.
User group reactions

Ahoy

Twilio's developer community hub

Best practices, code samples, and inspiration to build communications and digital engagement experiences.