Airtable <> Twilio Integration - Schedule SMS Messages

This code is an example app that connects an Airtable base to Twilio to send SMS messages. It is designed to execute once a day at 8 am PST using the Standard Library Scheduler API. It queries the linked Airtable base for messages to send on the Ecom Calendar table. It retrieves all messages scheduled to be sent on the current day and sends them to each phone number listed in the Contacts table via Twilio.

The time the code executes can be changed.

Setup

You will need:

A Twilio account
A free Airtable account

Setting up your Airtable Base

We've created a template Base you can duplicate to quickly get started with this app. Once everything is setup, you're welcome to modify your copy of the Base to better suit your needs. Please note that if you change the titles of any columns then you'll need to also change them in the code of this App.

Visit this link, and sign into Airtable if you haven't already
Then click Copy Base on the top left.

Installing this template

Select Install Free above.
Then follow the guided instructions to link to your Airtable and Twilio base by selecting Link and then Link a New Resource

How to change scheduled time

Navigate to the daily.js file under functions/events/scheduler/daily/.

Select the event trigger at the top of the code which reads once a day and configure your desired time to run this code.

Code Explanation

The code first authenticates with the Standard Library API on Autocode using a secret token stored in an environment variable.

const lib = require('lib')({token: process.env.STDLIB_SECRET_TOKEN});`

Then it gets the current date and uses it to query Airtable for messages that are scheduled to be sent today.

let date = new Date()
let today = `${date.getFullYear()}-${date.getMonth() + 1}-${date.getDate()}

let messages = await lib.airtable.query['@1.0.0'].select({
  table: 'Ecom Calendar', // required
  where: [
    {
      'Date__date_lte': today,
      'Date__date_gte': today
    }
  ],
  limit: {
    'count': 0,
    'offset': 0
  }
});

It also queries Airtable for a list of contacts with non-null phone numbers.

  baseId: `appMnUIjIJ1aR6UfD`,
  table: `Contacts`,
  where: [
    {
      'Phone__not_null': true
    }
  ],
  limit: {
    'count': 0,
    'offset': 0
  }
});

The code then loops through each message and for each message, it loops through each contact and sends the message to the contact's phone number using the Twilio API via stdlib. The phone numbers are formatted to include the +1 country code before being passed to the Twilio API.

for (let i = 0; i < messages.rows.length; i++) {
  for (let j = 0; j < phoneNumbers.length; i++) {
    await lib.twilio.messages['@1.0.0'].create({
      to: `+1${phoneNumbers[j]}`, // send the message for the current user in the loop
      body: `${messages.rows[i].fields.Message}` // send the user the message from Airtable
    });
  }  
}