Save your Twilio Studio Chatbot Data to Airtable using Node.js and Twilio Functions

September 29, 2021
Written by
Reviewed by
Diane Phan
Twilion

halloweetchatbot2.png

If you’ve built a chatbot or application flow with Twilio Studio, an integral part of bringing your app into production is being able to store relevant user data and access data you need to remit to your user. This means you need a database.

In this article, you’ll learn how to use Twilio Functions and Node.js to store and access data in an Airtable base. This article builds off the specific example project created in Build a Trick or Treat Chatbot with Twilio Studio and Functions, and Node.js.

To follow along step by step with this article, having built the Trick or Treat chatbot will be required, but if not, have no fear! This article contains valuable information about connecting with and using the Airtable API with Node.js for all readers.

Prerequisites

To get started with this tutorial, you’ll need the following:

Create your Airtable base

Login to your Airtable dashboard and click on the grey square with the plus button to create a new base in your workspace. Once you click on it, a small submenu will appear. Select the Start from Scratch option. When prompted, name your base Halloween.

In the top left of your new base, you’ll see a tab for the current table named Table 1. Double click the tab and change the name of the tab to locations.

screenshot showing airtable base

There are four columns, known in Airtable as fields, that have been automatically added to your table.

Screenshot showing airtable table with four fields

Delete the two rightmost fields, Attachments and Status, by clicking the small arrow next to each of their names and selecting Delete Field at the very bottom of the sub-menu.

screenshot showing field dropdown menu

Change the name of the first (left-most) field from Name to zipcode by double clicking on the field name. Change the name of the second field from Notes to address.

Your Airtable base will now look like this:

screenshot of airtable base with one table called locations and two fields, zipcode and address

Add Airtable to Twilio Functions

In order to work with Airtable in your Twilio Functions, you need to take two steps:

  • Gather your Airtable credentials and add them as environment variables in your Function Service.
  • Add the Airtable npm package as a dependency in your Function Service.

I’ll walk through each of these steps now.

Gather your Airtable credentials

Navigate to the Functions section of the Twilio Console and click on the name of the service you created when building your chatbot, likely named trick-or-treat.

On the lower half of the page, look under the Settings heading and click Environment Variables.

Screenshot showing functions service menu with settings at the bottom

This will open an editor on the right side of the screen where you can add environment variables that your functions will use. In the next step, after gathering the associated credentials, you’re going to add two environment variables here: airtable_base_id and airtable_api_key.

Get your Airtable API key

To get an API key, visit the Account section of Airtable and scroll down to the API section. Click the button that says Generate API key.

screenshot of airtable account page with generate api key button

This will create a new key that will be masked unless you click on it.

screenshot of masked api key in airtable account page

Click into the field with your masked key to toggle the visibility, and then copy the value.

Head back to the functions editor in your Twilio Functions Console and create the environment variable for the Airtable API key by typing airtable_api_key in the empty KEY field, and paste the value of the base ID into the empty VALUE field. Then click the Add button.

Screenshot showing functions environment variable editor

Get your Airtable base ID

Now you need to get your Airtable base ID. While logged into Airtable, visit the Airtable API documentation page. This will show you a list of all your Airtable workspaces and bases, with a link to see documentation specific to each of those bases.

Scroll down until you find your Halloween base and click the link.

This will open a page that resembles the following:

Screenshot showing airtable documentation with base id circled

Your base ID is found under the INTRODUCTION section of the Airtable documentation. Copy this value.

Head back to the functions editor in your Twilio Functions Console and create the environment variable for the Airtable base ID by typing airtable_base_id in the empty KEY field, and paste the value of the base ID into the empty VALUE field. Then click the Add button.

screenshot of environment variable setting

Add Airtable as a dependency

In the functions editor in your Twilio Console, click the Dependencies title under Settings to open the dependencies editor.

In the empty MODULE input type airtable and in the VERSION input to the right, type *. Then click the Add button. This will add the latest version of the airtable npm package to your project.

Screenshot showing dependencies editor in twilio functions editor

Congratulations - you’re hooked up to Airtable! Now it’s time to get coding.

Look up data from your Airtable base

Open your /find-candy function in the functions editor and take a look at the code:

exports.handler = async function(context, event, callback) { 
  
  /* This is a placeholder list of addresses submitted by users.
   * In your app, you should connect with your database and lookup all address submissions
   * with the zipcode passed to the function in event.zipcode
   */ 
  const allLocations = [
    {
      zipcode: '12345',
      address: '555 Oak Street'
    }
  ]

  const localLocations = allLocations.filter(loc => loc.zipcode == event.zipcode).join('\n');

  const msg = localLocations.length == 0 
    ? 'bOoOoOo there are no haunted locations in your area. Perhaps you should start the spOoOoky festivities.'
    : `Find your treats (or your tricks!) at these nearby haunted locations: \n${locations}`

  const twiml = new Twilio.twiml.MessagingResponse();
  twiml.message(msg);
  return callback(null, twiml);
};

The highlighted lines call out the placeholder code from part 1 of this project. Delete these lines and replace them with the code that connects to Airtable and retrieves data from your Airtable base.

After deleting the highlighted lines, paste the following code in their place:

var Airtable = require('airtable');
var base = new Airtable({apiKey: context.airtable_api_key}).base(context.airtable_base_id);

const results = await base('locations').select({
  filterByFormula: `{zipcode} = "${event.zipcode}"`
}).all();

const localLocations = results.map(record => record.fields['address']).join('\n');

This code imports the Airtable npm package and then creates a connection to the Airtable API using your two environment variables.

In Twilio Functions, you can access your environment variables on the context object that’s made available to every function.

The code then queries your Airtable base and filters the returned data with the line that says filterByFormula: {zipcode} = "${event.zipcode}". This line indicates that you only want records from the base where the record’s the zipcode field is equal to the zipcode value passed to the function.

Then, the JavaScript map()` method is used to extract only the address values from the returned records and combine them into one string, separated by a new line.

As in part 1 of this project, this string is then returned from the function and sent as an SMS to the user.

Insert data into your Airtable base

Save your /find-candy function and then open your /give-candy function.

Your function should have this code inside:

exports.handler = async function(context, event, callback) {  
  const zip = event.zipcode;
  const address = event.address;
  
  /*
   * Here, you would connect to your database and insert the user 
   * provided zip code and address, found in the two variables above
   */

  const msg = `Thanks for your contribution!`;
  const twiml = new Twilio.twiml.MessagingResponse();
  
  twiml.message(msg);
  return callback(null, twiml);
}

The highlighted comments in the code above represent the place where your Airtable insertion code will go.

Delete the comments and replace them with the following:

var Airtable = require('airtable');
var base = new Airtable({apiKey: context.airtable_api_key}).base(context.airtable_base_id);

base('locations').create([
  {
    "fields": {
      "zipcode": zip,
      "address": address
    }
  }
]);

Like in the /find-candy function, this code first imports the Airtable npm package and then connects to the Airtable API using your credentials.

Then, the code calls the Airtable SDK’s create() method, passing to it an array of JSON objects that represent the new records you’d like to add to your locations table.

Save your function, and then at the bottom of the screen click the Deploy All button to make your changes live.

Add some fake data to Airtable

Head back to Airtable and add a few fake (or real!) records to your locations table:

Screenshot showing airtable base with some fake data in it

Adding these records will make testing your chatbot more intuitive. If you jumped to this tutorial before finishing part 1, head back now to finish building your Studio flow and see how it all works together. If you have finished part 1, then go ahead and test it out!

To take your completed chatbot for a spin, send any text message to the Twilio phone number you configured to work with your Studio flow.

Screenshot showing chatbot message thread from iphone

Screenshot showing more of conversation with chatbot

When you’re testing the find candy flow, be sure to use a zip code that corresponds to one of the fake records you added in Airtable.

After testing the "give candy" flow, head back to Airtable and see your next records in your base!

If you had fun integrating Airtable with Twilio products, then check out this article teaching you how to build a messaging game with Twilio Autopilot and Airtable.

For more spooky projects, check out some of our other haunted articles, like this one on building a ghost-writing app with GPT-3, or this one on building a socially distant candy dispenser.

Happy hallowEeEeEeEeeeen!

Ashley is a JavaScript Editor for the Twilio blog. To work with her and bring your technical stories to Twilio, find her at @ahl389 on Twitter. If you can’t find her there, she’s probably on a patio somewhere having a cup of coffee (or glass of wine, depending on the time).