API content series: Slack slash command app

Katherine Duh -

Stop me if this sounds familiar: you're working on a project that has you jumping back and forth between two different apps. As you switch from one to the other, you notice an email hit your inbox. Fifteen minutes of email triage later, you come to, having forgotten what you were doing in the first place entirely. The joys of the modern workplace.

At Airtable, we want to do our part to eliminate the bane of context switching. We spend the whole day in Slack over here, so we try to keep as much content and context as possible in our channels. In fact, you can send notifications from Airtable to Slack using our pre-built integration.

But that only goes in one direction (Airtable → Slack). What if you wanted to search an Airtable base from Slack, and return the relevant records back to your channel directly? Let's say, for example, that you have a CRM in Airtable, and you want to quickly pull in records to a Slack channel so you can discuss how various deals are progressing. Normally, that would require poring over our API docs (as well as Slack's). Thankfully, we've done all that hard work for you. Keep reading, and in just a few minutes you'll learn how to build a Slack slash command app (using Airtable as the CMS).

Setup

First, you will need an Airtable account and an Airtable base to search. You can use this CRM template, if you'd like. But any base that you're interested in querying will work well!

Second, we're going to be using Glitch for this app. You can remix a Node app we got started for you here. (Or, if you'd like to skip over the coding and just start tweaking, you can remix a completed version of the Node app here.)

If you're unfamiliar, take a look around the Glitch app. In addition to just cleaning up the app workspace, we did a few other things as well.

First, we added in the body-parser package. We want to format the data that's coming in from the request as json, and we use body-parser to do that.

Second, we added in the airtable package, which we'll use to fetch records from your Airtable base.

Third, we added in the request package, which we'll use to send a message back to Slack after we've fetched Airtable records.

Handling a request

Let's get started! Start by creating a new post request handler with “/slack” as the route, as follows:

app.post("/slack", function (req, res) {
    res.send('test response');
});

This enables us to run some code whenever a request is made to the base URL of your Glitch app + /slack.

Create the Slack app

Now we need to create the app in Slack. Head over to Slack, click on create a Slack app, and choose a name and workspace for your app. Select “Slash Command App” and then click to create a new command.

  • Type the command you would like to use—we'll use “/airtable.”
  • Type the request URL—this is your Glitch app's base URL + “/slack”

You can find your Glitch app's base URL by clicking the dropdown in the upper left hand corner:

Click Save.

Next, you need to install the app in your Slack workspace. Head to Settings → Basic Information → Install your app to your workspace.

In Slack, send a message using the slash command “/airtable.” If everything is hooked up correctly, you should receive a response that says “test response.”

Before moving forward, we want to add a bit of security to this set up. If a request is not actually coming from Slack, we don't want to respond to it. We need to verify that the request is coming from your Slack workspace before sending any Airtable data back.

To do so, first get the verification token for your app by going to Settings → Basic Information → App Credentials → Verification Token.

In .env on Glitch, add your Slack verification token on the following line:

SLACK_VERIFICATION_TOKEN={the verification token for your Slack app}

And in the “/slack” request handler, verify that the token Slack sends matches our token by adding the following code:

const token = req.body.token;
if (token !== process.env.SLACK_VERIFICATION_TOKEN) {
  res.status(403).send('Forbidden');
  return;
}

Once we've verified that this request is coming from Slack, we should respond immediately. Later, we'll add some code to fetch records from Airtable, but we want to respond right away to ensure that we don't run up against Slack's timeout (3 seconds). Right below the code we just added (and in place of our test response), add in:

// Pull the search query out from the request.
const searchQuery = req.body.text;

// Immediately respond to the request, telling the user that we're searching for records.
// By responding immediately, we can ensure that we won't hit Slack's 3 second timeout.
res.status(201).json({
  response_type: 'ephemeral',
  text: `Searching for records matching "${searchQuery}"`,
});

Adding Airtable to the mix

Now that our Slack app is set up, let's invite Airtable to the party!

We can get this app talking to Airtable by using the Airtable API. You can find your Airtable API key by navigating to your Airtable account page and clicking “Generate API key.”

Note: you should be careful with your API key. If anybody else will be reviewing this code, you should create a separate account, add that account as a read-only collaborator to the base and use the read-only collaborator's API key.

Back in Glitch, in .env, add AIRTABLE_API_KEY={your api key}.

We also want to make sure that our slash command app is speaking to the right base and table. You can find your base ID from the API docs for the specific base you'd like to query in Airtable. The base ID is a string that starts with 'app'. And you can find your table ID from the URL of the base you've created. The table ID is the string that starts with 'tbl'.

In .env add AIRTABLE_BASE_ID={your base id} and AIRTABLE_TABLE_ID={your table id}

Head back to server.js and add the following line above all of the request handlers:

const base = new Airtable().base(process.env.AIRTABLE_BASE_ID);

Below our initial response, add the following code. This fetches records from Airtable, and then sends a new request to Slack telling it to post a message into the Slack channel.

// Slack gives us a response URL that we can use to post back to the channel after our
// initial response, so let's pull that out.
const responseUrl = req.body.response_url;

base(process.env.AIRTABLE_TABLE_ID).select({
  // Specify the 'string' cellFormat so that all values are human-readable.
  cellFormat: 'string',
    
  // When using the 'string' cellFormat, we have to pass userLocale and timeZone
  // as well, so that dates are formatted properly.
  userLocale: 'en-US',
  timeZone: 'America/Los_Angeles',
}).all().then(records => {
  request({
    method: 'POST',
    uri: responseUrl,
    body: {
      // Make the response type "in_channel" so it is visible to everyone. If you want the response to be visible only to the
      // user who issued the command, change this to "ephemeral".
      response_type: 'in_channel',
      text: `Found ${records.length} records!"`,
    },
    json: true,
  });
}).catch(err => {
  // Received an error, so let's respond with a message telling the user that the request failed.
  request({
    method: 'POST',
    uri: responseUrl,
    body: {
      // Make the response type "ephemeral" so it is not visible to the entire channel (just to the user who issued the command).
      response_type: 'ephemeral',
      text: 'Failed to fetch records from Airtable',
    },
    json: true,
  });
});

Now, after the app responds telling the user it is searching for records, the app is counting the number of records in the specific table that you have identified. Let's test this again. Hit the slash command. It should respond first with “Searching for records matching [search query]" and then with “Found [# of records in the table] records!”

Constructing the formula

This is where the real fun begins. We need to construct an Airtable formula which we will use to filter the records in our base based on the search query.

Let's think through what we need to do. We want to create a filter that will return true if the search query is found in any field for that record. With that in mind, the formula, when written in Airtable, should look something like this:

OR(SEARCH({lowercase search query}, LOWER({field 1})) > 0, SEARCH({lowercase search query}, LOWER({field 2})) > 0, ...)

Let's break this formula down. (You can also take a look at our support article on Airtable formulas for a more in-depth description of each function.)

The SEARCH function searches for an occurrence of the search query within the lowercased (using the LOWER function) field name. If that returns a number greater than zero, we know that the search query string was found within the field name. That's simple enough, but we want to search for our search query across multiple field names. And we want to return a positive result if the search query is found in any of the field names. To do that, we use the OR function.

Now, to add this searching mechanism to the app. First, add an array called fieldNamesToSearch underneath the const base = ... code you just added, and list all the fields that you want to search.

const fieldNamesToSearch = [
  // Add the names of all the fields that you want to search.
  // Here, we're just searching the 'Name' field.
  'Name',
];

In the '/slack' handler (before the Airtable API call), convert the search query to lowercase.

const lowerCaseSearchQuery = searchQuery.toLowerCase();

Now construct the search statements:

const searchStatements = [];
for (const fieldName of fieldNamesToSearch) {
  searchStatements.push(`SEARCH('${lowerCaseSearchQuery}', LOWER({${fieldName}})) > 0`);
}

And finally, construct the formula:

const formula = `OR(${searchStatements.join(', ')})`;

Using the formula

Okay, now we want to use the filterByFormula param (from the Airtable API) to select only the records that match the filter we just constructed. The filterByFormula param evaluates your formula against each record in the table. Any record for which the formula returns true will be included in the results. Any record for which the formula returns false will not be.

We do this by updating the code we previously wrote, which selects records from the specific table. All we're doing with this new code is including the filterByFormula parameter (using the formula we just created) and updating the response text to display the original search term.

base(process.env.AIRTABLE_TABLE_ID).select({
  filterByFormula: formula,
  
  // Specify the 'string' cellFormat so that all values are human-readable.
  cellFormat: 'string',
    
  // When using the 'string' cellFormat, we have to pass userLocale and timeZone
  // as well, so that dates are formatted properly.
  userLocale: 'en-US',
  timeZone: 'America/Los_Angeles',
}).all().then(records => {
  request({
    method: 'POST',
    uri: responseUrl,
    body: {
      // Make the response type "in_channel" so it is visible to everyone. If you want the response to be visible only to the
      // user who issued the command, change this to "ephemeral".
      response_type: 'in_channel',
      text: `Found ${records.length} records matching "${searchQuery}"`,
      attachments: attachments,
    },
    json: true,
  });
}).catch(err => {
  // Received an error, so let's respond with a message telling the user that the request failed.
  request({
    method: 'POST',
    uri: responseUrl,
    body: {
      // Make the response type "ephemeral" so it is not visible to the entire channel (just to the user who issued the command).
      response_type: 'ephemeral',
      text: 'Failed to fetch records from Airtable',
    },
    json: true,
  });

Now when you hit the slash command with a search term, you should see “Found [# of records] records matching [search query]” in response, depending on how many records matched your search term.

Finishing it up

Phew! That was the hard stuff. Let's finish this up. Rather than return the number of records, we want to return the actual contents of the records which match our search query.

To do that, we need to format the Airtable records as Slack attachments, so they can be viewed easily in the channel.

Let's first add some helper functions. Add the following code below const fieldNamesToSearch = ...:

// Replace this with whatever your primary field is named
const primaryFieldName = 'Name';

// Helper function to escape some special characters for Slack.
const slackEscape = function(str) {
  return str.replace('&', '&').replace('<', '<').replace('>', '>');
};

// Takes an Airtable record and formats it as a Slack attachment.
const formatAirtableRecordAsSlackAttachment = function(record) {
  // Get the primary field name, which we'll use as the title for the Slack attachment.
  // We'll just use 'Untitled record' if the primary field is empty.
  const primaryFieldValue = record.get(primaryFieldName) || 'Untitled record';
  const title = slackEscape(primaryFieldValue);

  // Now, let's start constructing our Slack attachment.
  const attachment = {
    title,
    fallback: title,
    title_link: `https://airtable.com/${process.env.AIRTABLE_TABLE_ID}/${record.getId()}`,
  };
  return attachment;
};

We want to return the Airtable records as Slack attachments (in addition to the number of records that were found). To do this, format the Airtable records as Slack attachments, and include the Slack attachments in the response json. Slack limits the number of attachments that you are allowed to post in a single message, so we'll only display the first 10 records that match the search query.

// Successfully retrieved records, so let's format them for Slack and return.
const maxNumRecordsToReturn = 10;
const attachments = records.map(record => formatAirtableRecordAsSlackAttachment(record)).slice(0, maxNumRecordsToReturn);
let text = `Found ${records.length} records matching "${searchQuery}"`;
if (records.length > maxNumRecordsToReturn) {
  text += ` (showing first ${maxNumRecordsToReturn})`;
}
request({
  method: 'POST',
  uri: responseUrl,
  body: {
    // Make the response type "in_channel" so it is visible to everyone. If you want the response to be visible only to the
    // user who issued the command, change this to "ephemeral".
    response_type: 'in_channel',
    text: text,
    attachments: attachments,
  },
  json: true,
});

Try hitting the slash command now. You should see the primary field values of all matching records.

All right. This is the final step. We don't just want to return the primary field. We want to be able to quickly pull in other fields from the record too.

To do that, let's update the formatAirtableRecordAsSlackAttachment function to also include some additional fields from the record. Before returning the Slack attachment from that function, insert the following code:

// So that our Slack attachments don't get too long, we're capping the number of included fields at 6.
// This limit is totally arbitrary though, so update it as you see fit :)
const maxFieldsToShow = 6;

// Let's go through this record's fields and format them for Slack.
const fieldNames = Object.keys(record.fields);
const slackAttachmentFields = [];
for (const fieldName of fieldNames) {
  if (slackAttachmentFields.length >= maxFieldsToShow) {
    // Reached the # of fields limit, so just break out of the loop.
    break;
  }

  if (fieldName === primaryFieldName) {
    // Skip the primary field, since we already used it as the attachment title.
    continue;
  }

  const fieldValue = record.get(fieldName);
  if (!fieldValue) {
    // Skip empty values.
    continue;
  }

  // Slack's short flag dictates how much horizontal space this attachment field should use.
  const short = fieldValue.length < 25;

  // Push this onto our array so that it's included in the Slack attachment.
  slackAttachmentFields.push({
    title: fieldName,
    value: slackEscape(fieldValue),
    short: short,
  });
}

// Set the fields on the attachment so that it uses the fields we just accumulated.
attachment.fields = slackAttachmentFields;
return attachment;

You did it! Try hitting the slash command one last time. You should see up to 10 records matching your search and some additional fields per record.

This is just the beginning of what you can do with the Airtable API and Slack. Here are some ideas to explore if you want to extend your app's functionality even further:

  • Try searching within a view (rather than the whole table) by passing through a view parameter.
  • Instead of just returning the first 10 results, what about setting up pagination, so a user can click a link to see more results?
  • Rather than returning a link to an image within an attachment field, try returning the actual image.