Automate Text Message Reminders with Airtable, Twilio Studio, and JavaScript
Tags: airtable, javascript, sms, twilio
Published: 2021-12-14 20:01:42

---
title: Automate Text Message Reminders with Airtable, Twilio Studio, and JavaScript
published: true
description: Learn how to automate text message reminders to be sent periodically using Airtable, Twilio Studio, and JavaScript
tags: airtable, javascript, sms, twilio
cover_image: https://twilio-cms-prod.s3.amazonaws.com/images/jAsYl_p2gQzPUckA70wmXEatF7EwJWg7JyDchLpkftY0H.width-1616.png
---
This blog post was written for [Twilio](https://www.twilio.com/) and [originally published on the Twilio blog](https://www.twilio.com/blog/automate-text-reminders-airtable-studio-js).

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](https://www.twilio.com/blog/send-sms-from-airtable-in-1-minute)--but did you know that you can also [write scripts in Airtable](https://www.airtable.com/developers/scripting) and [make automations that run in a variety of intervals](https://support.airtable.com/hc/en-us/articles/360050974153-Automations-overview)? In this post you’ll learn to automate text messages from Airtable using [Twilio Studio](https://www.twilio.com/studio) and JavaScript.
![SMS image example in 15min intervals](https://twilio-cms-prod.s3.amazonaws.com/images/Screen_Shot_2021-12-13_at_1.06.40_PM.width-1600.png)
### Prerequisites
- A Twilio account - [sign up for a free one here and receive an extra $10 if you upgrade through this link](http://www.twilio.com/referral/iHsJ5D)
- Your Twilio Account SID and Auth Token: [find it in your account console here](https://www.twilio.com/console)
- API Key SID and API Key Secret: [generate them here](https://www.twilio.com/console/runtime/api-keys)
- A Twilio number with SMS capabilities (this [quickstart will help you get going](https://www.twilio.com/docs/conversations/quickstart#sign-up-for-twilio-and-provision-your-first-sms-enabled-number) )
- An [Airtable account](https://airtable.com/) with a Pro or Enterprise plan–[more on the Pro Trial here](https://support.airtable.com/hc/en-us/articles/360042806493-How-does-the-Pro-trial-work-)

You’re going to need the <strong>Airtable API key</strong> and a <strong>base ID</strong> to access the base from your app. You will get the API key from [your account page](https://airtable.com/account). You will get the base ID from the [auto-generated API documentation](https://airtable.com/api) when you click on the base that you want to use (<em>Airtable x Twilio blog</em> if you are using the [copy of this project's Airtable base](https://airtable.com/invite/l?inviteId=inv016fVue6nLssoQ&inviteToken=9ff959fd77cd32ecc337d07521bdd02264e5ffc82ca3a23a86d8227edc8fab4e&utm_source=email)). You can retrieve it from either the URL or the <em>introduction</em> section.
![get Airtable base ID](https://twilio-cms-prod.s3.amazonaws.com/images/waVOdYnKKxj4eUpUJKpL76FkOg8wmmI5ESz7Sg1pKIcJZ.width-1000.png)
### Setup your Twilio Studio Flow
In your [Twilio Studio Console](https://console.twilio.com/us1/develop/studio/flows?frameUrl=/console/studio/flows), make a new Studio Flow. When asked to use a template, select <em>from scratch</em>. I called mine <em>Hydrate</em>. From the widget library in the Studio Canvas, drag and drop a <strong>Send Message</strong> widget and connect it to the <strong>Rest API</strong> trigger. Click the widget and set its <strong>Config's Message Body</strong> to be ```{{trigger.request.parameters.msg}}```--this will be a message that we send from Airtable.
![twilio studio flow to send message](https://twilio-cms-prod.s3.amazonaws.com/images/MXrQYjpY4s61qHnyLupzXzYW3cnBqlWmmEAWOVdhPHdzK.width-1000.png)
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](https://console.twilio.com/us1/develop/studio/flows?frameUrl=/console/studio/flows).
### Setup your Airtable
Copy this [ready-made Airtable base here](https://airtable.com/invite/l?inviteId=inv016fVue6nLssoQ&inviteToken=9ff959fd77cd32ecc337d07521bdd02264e5ffc82ca3a23a86d8227edc8fab4e&utm_source=email) and select the <em>Hydr9</em> tab. It contains a <em>Message</em> column containing messages reminding you to drink water. It also includes their corresponding <em>Sent</em> booleans of [type Checkbox](https://support.airtable.com/hc/en-us/articles/360042311954-Checkbox-field). 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 <em>Sent</em> column.
![Airtable table with columns, messages, sent bool](https://twilio-cms-prod.s3.amazonaws.com/images/_5gP2NkVj9D6iRay0nNCxFTFEkaco38dUZSJSpeqUFvA0.width-1000.png)
### 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](https://support.airtable.com/hc/en-us/articles/360050974153-Automations-overview). To get started, click <em>Automations</em> in the top right corner
![Automations button in top right corner](https://twilio-cms-prod.s3.amazonaws.com/images/3zyYMZuDXcOBTFSTtT44ugmVoDSvOQGOuUlaIHB4Gfbvg2.width-500.png)
followed by “Create a custom automation” in the <em>Suggested automations</em> section.
![suggested automations button](https://twilio-cms-prod.s3.amazonaws.com/images/mYhfA1MykAJxNGgaNA4ToeqIgaEqdY-y0_RqSvXkPYD4a.width-1000.png)
Give your automation a name (something like <em>hydration reminder</em>). For the trigger that will initiate the automation, click <strong>At a scheduled time</strong>. This will run the automation every x-number of minutes, hours, days, weeks...it's up to you!
![trigger at a scheduled time](https://twilio-cms-prod.s3.amazonaws.com/images/KGc1UeH7HNuU4q1pcpInTOkYJgJvtS5B7T47xCgpJmoyl.width-1000.png)
For the purposes of this tutorial, under <em>Interval type</em< on the right-hand panel select <strong>Minutes</strong> and for <em>Timing</em> pick <strong>every 15 minutes</strong>. You could have the automation start maybe 10 minutes from the current time.
![trigger timing properties](https://twilio-cms-prod.s3.amazonaws.com/images/Wd9pGqA0T5sUU68aJ5r8DXpVM6ilX-HE4nyud1i8Ybhj3.width-1000.png)
### Add Actions to your Automations
After the Airtable automation is triggered, we're going to add some [actions](https://support.airtable.com/hc/en-us/sections/360009376134-Automation-actions). Click <em>Add action</em>.
![add action](https://twilio-cms-prod.s3.amazonaws.com/images/1F5bylqHcr0rz9KAa-cXNRsKquqEQsyMpUd2Ds-9UdFUc.width-1000.png)
First, select <em>Find records</em> to search for records in our Airtable base <em>hydr9</em> of messages. Select the <strong>Hydr9</strong> table, and then under <em>Find records based on</em>, choose <strong>Condition</strong> where <em>Sent</em> is unchecked.
![find records where sent is unchecked](https://twilio-cms-prod.s3.amazonaws.com/images/dK8vFWAa-GjiVjr2ivnqCKr5yv6KjWOs_u-8u9hzO7OOO.width-1000.png)
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](https://twilio-cms-prod.s3.amazonaws.com/images/-oEW9JZXfmNXOrOtC-8K6CtJc9m4u_G_HuaACEcKXySUE.width-1000.png)
Now add another action–this time, it's <strong>Run a script</strong>.
> :warning: **You need a Pro or Enterprise Airtable account to run a script in Airtable.**: 
Before we write some JavaScript, we need to add some <strong>Input variables</strong> on the left. Click <strong>+ Add input variable</strong>.
![add input vars](https://twilio-cms-prod.s3.amazonaws.com/images/GRFHs88H3niMihmITPGfKWIEGtu-OfkfnLQUvovGU5-Fe.width-1000.png)
The input variables we're going to add:
- Name: <strong>TWILIO_STUDIO_SID</strong>, Value: <em>{your Twilio Studio Flow SID from when we setup your Twilio Studio Flow}</em>
- Name: <strong>TWILIO_API_KEY</strong>, Value: <em>{your Twilio API SID Key generated under Prerequisites}</em>
- Name: <strong>TWILIO_API_SECRET</strong>, Value: <em>{your Twilio API Secret Key generated under Prerequisites}</em>
- Name: <strong>TWILIO_PHONE_NUMBER</strong>, Value: <em>{your Twilio Phone Number}</em>
- Name: <strong>TWILIO_ACCOUNT_SID</strong>, Value: <em>{your Twilio Account SID}</em>
- Name: <strong>TWILIO_AUTH_TOKEN</strong>, Value: <em>{your Twilio Auth Token}</em>
- Name: <strong>AIRTABLE_BASE_ID</strong>, Value: <em>{your Airtable base ID from Prerequisites}</em>
- Name: <strong>AIRTABLE_API_KEY</strong>, Value: <em>{your Airtable API key from Prerequisites}</em>
- Name: <strong>MY_PHONE_NUMBER</strong>, Value: <em>{your personal phone number}</em>
- Name: <strong>numRecordsFound</strong>, Value: <em>{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.<em>
- Name: <strong>recordsFoundMsgList</strong>, Value: <em>{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.</em>
- Name: <strong>recordsFoundIds</strong>, Value: <em>{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...}</em>
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:
```javascript
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();
```
Beneath that line, let's make a function to create a Studio Execution. We also include a helper function <em>b2a</em> to help with encoding and decoding base64.
```javascript
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);
}
```
Next, we want to get the Airtable table (<em>Hydr9</em>), check the <em>Sent</em> 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.
```javascript
let table = base.getTable('Hydr9');
let field = table.getField("Sent");
let query = await table.selectRecordsAsync();
let recordMsgSentIdArr = [];
```
Then we make the <em>driver()</em> method which contains the meat of our Airtable script. If no records are found where <em>Sent</em> 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 <em>Sent</em> are unchecked.
```javascript
const driver = async() => {
    console.log(`numRecordsFound ${numRecordsFound}`);
    if(numRecordsFound == 0) {
        for (let record of query.records) {
            table.updateRecordAsync(record, {
            "Sent": false,
        })    
        }
    }
```
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.
```javascript
 let msg = recordsFoundMsgList.slice(-1); //first in array
    const payload = {
        'To': MY_PHONE_NUMBER,
        'From': TWILIO_PHONE_NUMBER,
        'Parameters': JSON.stringify({
            'msg': msg
        })
    };
```
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](https://www.airtable.com/developers/scripting/api/output).
```javascript
    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();
```
The complete script code is below:
```javascript
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();
```
If you click the blue <em>Test</em> button in the upper-right corner, you should see an output like this containing the <em>numRecordsFound</em>, <em>recordsFoundMsgList</em>, <em>recordsFoundIds</em>, <em>Result</em>, and <em>recordMsgsentId</em> and you should also get a text message containing the first Record message with no checkmark:
![test output](https://twilio-cms-prod.s3.amazonaws.com/images/RFZTHD4MFS_mlXGe7cZJPoaC3TO0sXaHWehFK47LPPoWI.width-1000.png)
This is all good and fun–a text message was sent! However, what happens when all the messages are sent and <em>numRecordsFound</em> hits 0? Add another action below <em>Run a script</em> of type <em>Update record>/em>. Select the <em>Hydr9</em> <strong>Table</strong> and under <strong>Record</strong> id click the blue plus button and follow the steps in the gif below to select <em>recordMsgSentId</em>.
![get record id from Airtable in an Action](https://twilio-cms-prod.s3.amazonaws.com/original_images/FTYfo2vGGu6gsTU6_QgpnzBnM-IWWDSgX1OZAnpQHLTDiZNFiMLFzqvwlH-6_PDD10pXAOgy1cPbh1)
Under <em>Fields</em>, click <strong>+ Choose field</strong> followed by <strong>Sent</strong>.
![select Sent](https://twilio-cms-prod.s3.amazonaws.com/images/e-jKIMMmobwYm-_kvYSYT37q7kiqbxQ_EefpyKQXH-pai.width-1000.png)
In the box, type in <em>true</em>.
![true typed into box](https://twilio-cms-prod.s3.amazonaws.com/images/7fPmnzIxoBkA-iWeyf6GQe8mhzoMsd7zcBzIacz7aAQMa.width-1000.png)
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](https://twilio-cms-prod.s3.amazonaws.com/images/Screen_Shot_2021-12-13_at_1.06.40_PM.width-1600.png)
Do you prefer learning via video more? [Check out this TikTok summarizing this tutorial](https://www.tiktok.com/@lizziepikachu/video/7038420979147656495)!
### 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](https://console.twilio.com/us1/develop/twiml-bins/twiml-bins?) or a [Twilio Function](https://console.twilio.com/us1/develop/functions/overview), and send bulk automated [text messages](https://www.twilio.com/sms) 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](https://www.twilio.com/blog/send-sms-from-airtable-in-1-minute)!) 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](https://www.twilio.com/blog/airtable-twilio-python-survey-app) or [how to use Airtable as a database for your Twilio app](https://www.twilio.com/blog/airtable-database-read).