Automate Text Message Reminders with Airtable, Twilio Studio, and JavaScript

Lizzie Siegle - Dec 14 '21 - - Dev Community

This blog post was written for Twilio and originally published on the Twilio blog.

In my last blog post on Airtable, I showed how to send a text message in under one minute with no code using the Send SMS Airtable App--but did you know that you can also write scripts in Airtable and make automations that run in a variety of intervals? In this post you’ll learn to automate text messages from Airtable using Twilio Studio and JavaScript.
SMS image example in 15min intervals

Prerequisites

You’re going to need the Airtable API key and a base ID to access the base from your app. You will get the API key from your account page. You will get the base ID from the auto-generated API documentation when you click on the base that you want to use (Airtable x Twilio blog if you are using the copy of this project's Airtable base). You can retrieve it from either the URL or the introduction section.
get Airtable base ID

Setup your Twilio Studio Flow

In your Twilio Studio Console, make a new Studio Flow. When asked to use a template, select from scratch. I called mine Hydrate. From the widget library in the Studio Canvas, drag and drop a Send Message widget and connect it to the Rest API trigger. Click the widget and set its Config's Message Body to be

{{trigger.request.parameters.msg}}

--this will be a message that we send from Airtable.
twilio studio flow to send message
Save and publish your Airtable flow, and take note of its SID. This can be found next to each flow in your list of flows.

Setup your Airtable

Copy this ready-made Airtable base here and select the Hydr9 tab. It contains a Message column containing messages reminding you to drink water. It also includes their corresponding Sent booleans of type Checkbox. To begin, make sure all the records here do not have any checkmarks. After a message is sent, the corresponding record will contain a checkmark in the Sent column.
Airtable table with columns, messages, sent bool

Make your Airtable Automation

Airtable automations "allow you to configure custom trigger-action workflows directly within your Airtable base", letting you automate repetitive tasks. For more information on automations in Airtable, read this page. To get started, click Automations in the top right corner
Automations button in top right corner
followed by “Create a custom automation” in the Suggested automations section.
suggested automations button
Give your automation a name (something like hydration reminder). For the trigger that will initiate the automation, click At a scheduled time. This will run the automation every x-number of minutes, hours, days, weeks...it's up to you!
trigger at a scheduled time
For the purposes of this tutorial, under Interval typeMinutes and for Timing pick every 15 minutes. You could have the automation start maybe 10 minutes from the current time.
trigger timing properties

Add Actions to your Automations

After the Airtable automation is triggered, we're going to add some actions. Click Add action.
add action
First, select Find records to search for records in our Airtable base hydr9 of messages. Select the Hydr9 table, and then under Find records based on, choose Condition where Sent is unchecked.
find records where sent is unchecked
Each time the automation runs, it will search the Airtable base for records that have not been sent yet. You can test this action and see that six records were found matching that condition:
finds records
Now add another action–this time, it's Run a script.

⚠️ You need a Pro or Enterprise Airtable account to run a script in Airtable.:
Before we write some JavaScript, we need to add some Input variables on the left. Click + Add input variable.
add input vars
The input variables we're going to add:

  • Name: TWILIO_STUDIO_SID, Value: {your Twilio Studio Flow SID from when we setup your Twilio Studio Flow}
  • Name: TWILIO_API_KEY, Value: {your Twilio API SID Key generated under Prerequisites}
  • Name: TWILIO_API_SECRET, Value: {your Twilio API Secret Key generated under Prerequisites}
  • Name: TWILIO_PHONE_NUMBER, Value: {your Twilio Phone Number}
  • Name: TWILIO_ACCOUNT_SID, Value: {your Twilio Account SID}
  • Name: TWILIO_AUTH_TOKEN, Value: {your Twilio Auth Token}
  • Name: AIRTABLE_BASE_ID, Value: {your Airtable base ID from Prerequisites}
  • Name: AIRTABLE_API_KEY, Value: {your Airtable API key from Prerequisites}
  • Name: MY_PHONE_NUMBER, Value: {your personal phone number}
  • Name: numRecordsFound, Value: {click the blue plus button next to Value, click Continue next to Records (from Step 2: Find records), again click Continue next to Records, and then click Insert next to <>length under list properties.
  • Name: recordsFoundMsgList, Value: {click the blue plus button next to Value, click Continue next to Records (from Step 2: Find records), again click Continue next to Records, then click Continue next to <>Field values under Make a new list of, and finally click Insert next to <>Message.
  • Name: recordsFoundIds, Value: {click the blue plus button next to Value, click Continue next to Records (from Step 2: Find records), again click Continue next to Records, then click Insert next to Airtable Record ID under Make a new list of...} That's a lot of input variables! We’ll import them into our Airtable script so we can use them by adding onto line one: 
const {AIRTABLE_API_KEY, AIRTABLE_BASE_ID, TWILIO_STUDIO_SID, TWILIO_ACCOUNT_SID, TWILIO_AUTH_TOKEN, TWILIO_PHONE_NUMBER, MY_PHONE_NUMBER, recordsFoundMsgList, numRecordsFound, recordsFoundIds} = input.config();
Enter fullscreen mode Exit fullscreen mode

Beneath that line, let's make a function to create a Studio Execution. We also include a helper function b2a to help with encoding and decoding base64.

const studioCreateExecution = async (payload, twilioStudioSid, twilioAccountSid, twilioAuthToken) => {
    try {
        const basicAuth = b2a(`${twilioAccountSid}:${twilioAuthToken}`);
        const CREATE_EXECUTION_URL = `https://studio.twilio.com/v2/Flows/${twilioStudioSid}/Executions`;
        const result = await fetch(CREATE_EXECUTION_URL, {
            method: 'POST',
            headers: {
                'Content-Type': 'application/x-www-form-urlencoded;charset=UTF-8',
                'Authorization' : `Basic ${basicAuth}`
            },
            body: new URLSearchParams(payload)
        })
        return result.json();
    } catch(e) {
        console.error(e);
        throw e;
    }
}
//Credits: https://gist.github.com/oeon/0ada0457194ebf70ec2428900ba76255
const b2a = (a) => {
  var c, d, e, f, g, h, i, j, o, b = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=", k = 0, l = 0, m = "", n = [];
  if (!a) return a;
  do c = a.charCodeAt(k++), d = a.charCodeAt(k++), e = a.charCodeAt(k++), j = c << 16 | d << 8 | e, 
  f = 63 & j >> 18, g = 63 & j >> 12, h = 63 & j >> 6, i = 63 & j, n[l++] = b.charAt(f) + b.charAt(g) + b.charAt(h) + b.charAt(i); while (k < a.length);
  return m = n.join(""), o = a.length % 3, (o ? m.slice(0, o - 3) :m) + "===".slice(o || 3);
}
Enter fullscreen mode Exit fullscreen mode

Next, we want to get the Airtable table (Hydr9), check the Sent field to see if there are any checkmarks, and make an array called recordMsgsentIdArr full of the IDs of the records containing messages we send.

let table = base.getTable('Hydr9');
let field = table.getField("Sent");
let query = await table.selectRecordsAsync();
let recordMsgSentIdArr = [];
Enter fullscreen mode Exit fullscreen mode

Then we make the driver() method which contains the meat of our Airtable script. If no records are found where Sent is unchecked in the last step/action (meaning all the messages have been sent once), we reset the table so that all the records in Sent are unchecked.

const driver = async() => {
    console.log(`numRecordsFound ${numRecordsFound}`);
    if(numRecordsFound == 0) {
        for (let record of query.records) {
            table.updateRecordAsync(record, {
            "Sent": false,
        })    
        }
    }
Enter fullscreen mode Exit fullscreen mode

Else, we’ll want to get the first record in the array whose messages have not been sent yet (this will be the message sent) and create a payload to return. The payload contains information needed to send a SMS in Twilio: a to phone number, a from Twilio phone number, and a message.

 let msg = recordsFoundMsgList.slice(-1); //first in array
    const payload = {
        'To': MY_PHONE_NUMBER,
        'From': TWILIO_PHONE_NUMBER,
        'Parameters': JSON.stringify({
            'msg': msg
        })
    };
Enter fullscreen mode Exit fullscreen mode

Lastly, we grab the ID of the record sent and add it to the recordMsgSentIdArr array, create the Studio Execution, and return both those variables so they can be used in the next Airtable automation action with output.set. You can learn more about outputting information in Airtable automations here.

    let recordMsgSentId = recordsFoundIds.slice(-1); //first in array
    recordMsgSentIdArr.push(recordMsgSentId);

    const result = await studioCreateExecution(payload, TWILIO_STUDIO_SID, TWILIO_ACCOUNT_SID, TWILIO_AUTH_TOKEN);
    output.set('recordMsgSentId', recordMsgSentId);
    output.set('result', JSON.stringify(result));
}
await driver();
Enter fullscreen mode Exit fullscreen mode

The complete script code is below:

const {AIRTABLE_API_KEY, AIRTABLE_BASE_ID, TWILIO_STUDIO_SID, TWILIO_ACCOUNT_SID, TWILIO_AUTH_TOKEN, TWILIO_PHONE_NUMBER, MY_PHONE_NUMBER, recordsFoundMsgList, numRecordsFound, recordsFoundIds} = input.config();
const studioCreateExecution = async (payload, twilioStudioSid, twilioAccountSid, twilioAuthToken) => {
    try {
        const basicAuth = b2a(`${twilioAccountSid}:${twilioAuthToken}`);
        const CREATE_EXECUTION_URL = `https://studio.twilio.com/v2/Flows/${twilioStudioSid}/Executions`;
        const result = await fetch(CREATE_EXECUTION_URL, {
            method: 'POST',
            headers: {
                'Content-Type': 'application/x-www-form-urlencoded;charset=UTF-8',
                'Authorization' : `Basic ${basicAuth}`
            },
            body: new URLSearchParams(payload)
        })
        return result.json();
    } catch(e) {
        console.error(e);
        throw e;
    }
}
//Credits: https://gist.github.com/oeon/0ada0457194ebf70ec2428900ba76255
const b2a = (a) => {
  var c, d, e, f, g, h, i, j, o, b = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=", k = 0, l = 0, m = "", n = [];
  if (!a) return a;
  do c = a.charCodeAt(k++), d = a.charCodeAt(k++), e = a.charCodeAt(k++), j = c << 16 | d << 8 | e, 
  f = 63 & j >> 18, g = 63 & j >> 12, h = 63 & j >> 6, i = 63 & j, n[l++] = b.charAt(f) + b.charAt(g) + b.charAt(h) + b.charAt(i); while (k < a.length);
  return m = n.join(""), o = a.length % 3, (o ? m.slice(0, o - 3) :m) + "===".slice(o || 3);
}
let table = base.getTable('Hydr9');
let field = table.getField("Sent");
let query = await table.selectRecordsAsync();
let recordMsgSentIdArr = [];
const driver = async() => {
    console.log(`numRecordsFound ${numRecordsFound}`);
    if(numRecordsFound == 0) {
        for (let record of query.records) {
            table.updateRecordAsync(record, {
            "Sent": false,
        })    
        }
    }
    let msg = recordsFoundMsgList.slice(-1); //first in array
    const payload = {
        'To': MY_PHONE_NUMBER,
        'From': TWILIO_PHONE_NUMBER,
        'Parameters': JSON.stringify({
            'msg': msg
        })
    };
    let recordMsgSentId = recordsFoundIds.slice(-1); //first in array
    recordMsgSentIdArr.push(recordMsgSentId);

    const result = await studioCreateExecution(payload, TWILIO_STUDIO_SID, TWILIO_ACCOUNT_SID, TWILIO_AUTH_TOKEN);
    output.set('recordMsgSentId', recordMsgSentId);
    output.set('result', JSON.stringify(result));
}
await driver();
Enter fullscreen mode Exit fullscreen mode

If you click the blue Test button in the upper-right corner, you should see an output like this containing the numRecordsFound, recordsFoundMsgList, recordsFoundIds, Result, and recordMsgsentId and you should also get a text message containing the first Record message with no checkmark:
test output
This is all good and fun–a text message was sent! However, what happens when all the messages are sent and numRecordsFound hits 0? Add another action below Run a script of type Update record>/em>. Select the Hydr9 Table and under Record id click the blue plus button and follow the steps in the gif below to select recordMsgSentId.
get record id from Airtable in an Action
Under Fields, click + Choose field followed by Sent.
select Sent
In the box, type in true.
true typed into box
If all the messages have been sent (each record contains a check), the table will update the records so that none will contain a check and the automation can start over. Now you should get text messages every 15 minutes or until you turn off your automation! You can of course change the frequency in which the automation runs.
SMS sent at 15 mins intervals
Do you prefer learning via video more? Check out this TikTok summarizing this tutorial!

Conclusion

Airtable automations make it easy to automate actions and scripts. Instead of hitting a Twilio Studio Flow, you could write a script to hit a TwiML bin or a Twilio Function, and send bulk automated text messages to multiple phone numbers (instead of being an input variable, the phone number to text could be in the Airtable base as shown in this Twilio blog post!) Let me know online what you're building with Airtable and Twilio. Stay tuned for more Airtable content on the Twilio blog like this tutorial on building a survey app with Python or how to use Airtable as a database for your Twilio app.

Image
Cat Flap Installation - How to Fit a Cat Flap in a Door
Image
Efficient State Management in Next.js: Best Practices for Scalable Applications

webdev, react, webperf, nextjstips
Image
Efficient State Management in Next.js: Best Practices for Scalable Applications

webdev, react, webperf, nextjstips
Image
The Impact of AI on Content Moderation: Advanced Techniques for Identifying NSFW Content

ai, imageprocessing, contentmoderation, digitalsecurity
Image
Decoupling Communication with MediatR

csharp, dotnet
Image
Desacoplamento de Comunicação com MediatR

csharp, dotnet, braziliandevs
Image
Guide to Passive Income in Tech: Coding Your Way to Wealth

passiveincome, sideprojects, wealth, income
Image
Algo-Trading Journey: Building vs. Buying Bots

dlang, javascript, python, ai
Image
10 Things We Do Not Like About Leather Sofas Chesterfield
Image
Deploying a Dockerized Web App on AWS Using ECS and Fargate: A Step-by-Step Guide

docker, ecs, ecr
Image
Unexpected Business Strategies That Aided Best Folding Wheelchair To Succeed
Image
Build a Youtube Clone with Strapi and Flutter: Part 2

flutter, strapi, dart, youtube
Image
Content Performance of Mobile Apps with Sanity CMS

webdev
Image
IT data backup solutions
Image
Finding the second highest salary in Oracle SQL

interview, programming, tutorial, fullstack
Image
Pricing Smart Contracts

blockchain, cryptocurrency, hbar, hedera
Image
To Delete a record without using the DELETE | INSERT | UPDATE

sql, webdev, beginners, programming
Image
A Handy Guide to Closed Source Licenses: Protecting Your Code Like a Pro

webdev, documentation, beginners
Image
The Most Common Fold Up Travel Wheelchair Mistake Every Beginning Fold Up Travel Wheelchair User Makes
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Terabox Video Player