Writing to Airtable from a Twilio serverless function
Tags: twilio, airtable, serverless, node
Published: 2020-05-12 21:25:44

---
canonical_url: https://www.twilio.com/blog/writing-airtable-sms
---

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](https://www.netpromoter.com/know/) (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

- A Twilio account -- sign up for a free one [here](http://www.twilio.com/referral/vEfhDc) and receive an extra $10 when you upgrade your account
- A [free Airtable account](https://airtable.com/)
- A [Twilio phone number with SMS capabilities](https://www.twilio.com/docs/conversations/quickstart#sign-up-for-twilio-and-provision-your-first-sms-enabled-number)

### 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](https://support.airtable.com/hc/en-us/articles/360021518753), “A [base](https://support.airtable.com/hc/en-us/articles/360021518753#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:
- Choose one of [Airtable’s many official templates](https://airtable.com/templates)
- Choose a template created by the [Airtable community](https://airtable.com/universe)
- Start from scratch

I’ve created a base called “Net Promoter Score” that we’ll be working with today. You can view it [here](https://airtable.com/shrclToj4GqUHxp4l) 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.](https://dev-to-uploads.s3.amazonaws.com/i/mh5a5r9mz63a0ijg99b6.png)

### 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](https://www.twilio.com/console/functions/configure). Generate an [Airtable API key](https://support.airtable.com/hc/en-us/articles/219046777-How-do-I-get-my-API-key-) and [add it as an environment variable](https://www.twilio.com/docs/runtime/functions/invocation#environment-variables) 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](https://airtable.com/api). You’ll see links to all your bases. Click through on the link to the “Net Promoter Score” base.

![Screenshot of the Airtable API landing page. There are links to 2 bases: "Cute Animals" and "Net Promoter Score."](https://dev-to-uploads.s3.amazonaws.com/i/k35t31ojj54esaqr2xyh.png)

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.](https://dev-to-uploads.s3.amazonaws.com/i/dazm5ktaqm87xyq13cuu.png)

Go back to the Twilio functions dashboard. Add the Airtable 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.](https://dev-to-uploads.s3.amazonaws.com/i/lbx8u2k8s14ojekgntbg.png)

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:

```JavaScript
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:
```bash
curl --request POST 'https://YOUR-FUNCTION-URL.twil.io/write-nps?numericalScore=7&reason=You%20had%20impeccable%20can%C3%A1pes.' \
--header 'Content-Type: application/x-www-form-urlencoded'
Success!
```

![Screenshot of the Net Promoter Score airtable base. Now there is a new row. Number=2, Score=7, Reason="You had impeccable canápes." Calculated NPS score is 50.](https://dev-to-uploads.s3.amazonaws.com/i/d9ajyl7efbhstn26spzm.png)

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
```

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](https://github.com/denysdovhan/wtfjs) so YOLO.

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

```JavaScript
 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](https://airtable.com/api) for details.

### Adding a Twilio 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](https://www.twilio.com/docs/studio/user-guide#importing-flow-data). Before importing you’ll need to replace the example.com URL with the URL of the function you just wrote.

```json
{
  "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](https://www.twilio.com/docs/studio/user-guide#configuring-your-twilio-number-for-studio).

![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.](https://dev-to-uploads.s3.amazonaws.com/i/vp3gjkybwuxbut3be3ca.png)

### 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](https://www.twilio.com/blog/airtable-database-read). In the meantime, hit me up on [Twitter](https://twitter.com/annthurium) or over email (tthurium [at] twilio [dot] com) if you have any questions.