Airtable: Notifications on Field Change

This project acts a simple template to allow you to create notifications whenever a field changes in Airtable. For example, if you're using Airtable to keep track of client projects and you'd like to notify them when their project status changes. In this project, alteration of the Status field will trigger both an SMS message via the utils/sms API and an e-mail to be sent via the gmail/messages API.

Note that all Phone Numbers require a country code (1 for US and Canada).

To get started, you'll first need click here to copy the Project Status SMS Example Base, available here https://airtable.com/shrIceuFa6rErnJEC.

When modifying the Status field...

Airtable Example

An e-mail will be sent via Gmail...

E-mail Received

And an SMS notification will be triggered as well...

SMS Received

How It Works

Please Note: By default, this is configured to send to test e-mails and phone numbers. Before you put it live, please change the IS_TEST variable to false.

Every hour, Autocode will check Airtable to see if there are field updates. It does this via the scheduler.hourly event, configured in stdlib.json and applied to the functions/events/scheduler/hourly.js file. The Autocode editor will automatically configure the event for you when creating new files. After sending SMS / E-mails, Airtable will set the Last Notified Status field so duplicate e-mails don't get sent.

The code to retrieve your Airtable records looks like this, using the airtable/query API:

console.log(`Running [Airtable → Find Records by Formula]...`);
result.airtable.records = await lib.airtable.query['@0.5.3'].records.find.formula({
  table: `Projects`,
  formula: `{Status} != {Last Notified Status}`
});

Next, in order to parallelize our message send we invoke what's called Promise.all, which means everything inside will be executed simultaneously. We iterate over each record object that's found.

await Promise.all(
  result.airtable.records.map(record => {
    return (async () => {
      // Parallel implementation here
    })();
  })
);

Finally — the guts. Here we use three APIs;

utils/sms to send SMS messages
gmail/messages to send E-mails
airtable/query to update records by ID and set the Last Notified Status field

Please Note: By default, there's an IS_TEST variable that's set to true in the top of the file. This will make sure you don't send E-mails out accidentally. When you're ready to deploy, please change this to false.

let name = record.fields['Name'];

let emails = IS_TEST
  ? [TEST_EMAIL] // If it's a test, use your test email
  : record.fields['Client E-mails'];
  
let phoneNumbers = IS_TEST
  ? [TEST_PHONE_NUMBER] // If it's a test, use your test number
  : record.fields['Client Phone Numbers'];
  
let fromStatus = record.fields['Last Notified Status'] || '(None)';
let toStatus = record.fields['Status'] || '(None)';

// Send all SMS messages in parallel
await Promise.all(
  phoneNumbers.map(phoneNumber => {
    return lib.utils.sms['@2.0.2']({
      to: phoneNumber,
      body: `✨ ${name} updated!\n\nStatus Changed\n${fromStatus} ➡️ ${toStatus}`
    })
  })
);

// Send an E-mail to all clients on the thread
await lib.gmail.messages['@0.2.0'].create({
  to: emails.join(', '),
  subject: `Project \"${name}\" Status Updated!`,
  text: `✨ ${name} updated!\n\nStatus Changed\n${fromStatus} ➡️ ${toStatus}`
});

// Update the record in the Base
await lib.airtable.query['@0.5.3'].records.update({
  table: `Projects`, // required
  id: `${record.id}`,
  fields: {
    'Last Notified Status': `${toStatus}`
  }
});

Linking Airtable, Gmail Accounts

Linking accounts is straightforward, Autocode will do most of the handholding for you. After you've forked this source by clicking Fork (at the top of the screen), you'll be brought to the Autocode editor. At the bottom of your screen you'll see a big red button that says 2 Accounts Required.

2 Accounts Required

Click the button to see a prompt with your required 3rd party accounts, in this case Airtable and Gmail.

Link Account Modal

Beside each of the two rows, there's a Link button. Click it to open a new modal...

Link New Resource

Click the Link New Resource button. You'll be walked through how to link each account.

Deploying Your Webhook / Script

To deploy this project to a live web environment, click the Deploy button in the bottom left of the Autocode editor.

Once clicked, you'll see a deploy prompt. When finished, your project is live.

Deployment in Progress

You can ship to production any time by going to autocode.com/manage and using the Ship Release button. Note: that you must have a live development API to see this option.

Ship Release

Acknowledgements

Special thanks to Howie Liu and the Airtable team for being so supportive! You can follow Team Autocode on Twitter @AutocodeHQ — please reach out at any time!

You should also follow @Airtable for platform updates.