Curated Twitter Feed

curated twitter feed using airtable and sheetdream

Having a twitter feed live on your website connects your brand to the conversation. It can show a lot of brand authority to be able to see live conversations occurring in realtime on Twitter — especially the positive conversations showing your brand in a great light.

Of course, not all that happens on Twitter is positive discourse. Oftentimes you’ll see content that is questionable, or at the very least might have something in it that you wouldn’t want to associate with your brand. Since you wouldn’t want that sort of content appearing on the front page of your website, you need a way of sifting through the duds and instead presenting the winning tweets that show your brand in the best light.

Of course, there exists software to do this already. One of which is curator.io, which offers an excellent solution for moderating the content that appears on your site. They even have a free tier, for when you’re a very small site (less than 2000 page views per month) and don’t mind waiting 24 hours for a feed refresh.

But once you start looking at the more advanced plans, you start paying a lot more.

Let’s see how you would build something like this using SheetDream and Airtable.

We’re going to take the features that we like and want from curator.io, and pull them into our app. The rest of the stuff we don’t care for we’re going to leave out.

Here’s what we’re looking for in particular:

  • Moderation ability
  • 10 minute refresh (versus the 15 minutes you’d get on the $50 plan on curator.io, or 60 minutes on the $25 plan)

And here’s what we gain by doing things this way:

  • We get to do all of our moderation in Airtable, an interface we’re already familiar with. This is ideal compared to having to learn a new interface in curator.io.
  • We have more flexibility over how our tweets are displayed
  • We get a faster refresh time for a similar price point (10 minutes vs the every 60 minutes for the $25 plan)

Let’s jump right in.

Where Do We Start?

Normally we advocate starting with the data: which would mean getting things setup in Airtable first. But in this particular scenario, we don’t exactly know what the data is going to be or what it’s going to look like yet.

SheetDream actually allows you to pull data in from APIs and create tables from them, without having setup an association with Airtable or Google Sheets just yet. It will just set an expiration date on the data, where it will be purged from the cache after 24 hours.

So you can get started from the API side, see what the data looks like, then build out things on the Airtable side from there.

So the first thing we need to do is: we need to set up a project on Twitter via their developer portal. You can head here to do so: https://developer.twitter.com/en/portal/. Twitter will expect you to give a little bit of information about the app you’re putting together (a curated twitter feed for your website).

API providers generally want to know a little bit more about what you’ll be using their API for: not only for discovery purposes (how can we make our API better for you) but also to prevent abuse.

Once your project is all set up, you should be able to retrieve your consumer key and secret. If you ever lose your consumer key and secret, you can regenerate a new key and secret here on this particular tab:

You’re going to be using this consumer key and secret in the next step.

Twitter Authentication

API authentication is probably the most confusing part of APIs.

Twitter’s authentication is actually relatively simple, mostly because the sort of access we need to their API is very simple. We don’t need access to user specific data, all we need access to is the public tweet pool.

This means that we can authenticate with Twitter using “app authentication”, also known as a “client credentials grant”. This grants us the ability to read public tweet data from the Twitter API, without having to prompt a user for access to their data.

This is greatly preferable for our application because we won’t have to do a lot of maintenance on the API request after we set it up in SheetDream.

For Twitter’s app authentication, per https://developer.twitter.com/en/docs/authentication/oauth-2-0/application-only you need to hit this endpoint:

https://api.twitter.com/oauth2/token

With an application/x-www-form-urlencoded (important!) payload of:

grant_type=client_credentials

You set your “Authorization” header value to:

Basic your_base64_encoded_consumer_key_and_secret

Whoa, back up here for a minute. That’s a lot of details all at once: what is application/x-www-form-urlencoded and why is it important for this endpoint? What is base64 encoding?

application/x-www-form-urlencoded is a way of passing data to an API. JSON is also another way of passing data to an API (with a content type of “application/json”). It just means that your data you’re passing is basically a series of parameter=value pairs separated by the & character:

parameter1=value1&parameter2=value2

For our call to the Twitter API, all we need to pass is the grant_type parameter, so that’s why we pass this:

grant_type=client_credentials

And now onto base64 encoding: base64 encoding is a way of transforming potentially binary data that you may not be able to display on the screen (try opening up a ZIP or PDF file in a text editor to see what I mean) so that it can be sent to APIs and systems where human readable text is expected.

You don’t have to worry or know too much about how base64 encoding works, because that’s going too far into programming land. Instead, you just have to know that in order to interact with some APIs, you’ll need to pass some data in base64 format (just like some APIs you’ll interact with using x-www-form-urlencoded, or JSON).

[How do you know when to pass data using which type of encoding? That’s generally where you need to consult the API’s documentation. If you have trouble understanding API documentation for your particular use case, never hesitate to reach out to us at SheetDream!]

For Twitter, the expectation is that you take your consumer key and secret like so:

consumer_key:consumer_secret

(hint: notice the colon between the two. It’s important.)

And you run that through a base64 encoder. Here’s a lovely online base64 encoding tool:

https://www.base64decode.org/

And what the base64 encoded result looks like for the above “consumer_key:consumer_secret”:

Y29uc3VtZXJfa2V5OmNvbnN1bWVyX3NlY3JldA==

So if those values were our key / secret, here’s what we would pass to Twitter in the “Authorization” header:

Basic Y29uc3VtZXJfa2V5OmNvbnN1bWVyX3NlY3JldA==

Assuming all went according to plan, Twitter will return a very long lived access token back to you:

{
  “access_token”: “AAA...kyA”
  “token_type”: “bearer”
}

You can now use this access token to call the Twitter API. Twitter will show you in their dashboard your API usage (you’re probably limited to around 500,000 calls per month).

Now, onto calling the API itself and retrieving some Tweets.

Twitter Recent Tweet Search

Here is the documentation for the Twitter endpoint that will allow us to retrieve the latest tweet data we’re looking for:

https://developer.twitter.com/en/docs/twitter-api/tweets/search/api-reference/get-tweets-search-recent

For this particular use case, we chose to retrieve all of the tweets related to a hashtag for a popular sports team. After some experimentation in PostMan (highly recommended if you’re going to be working with APIs and calling them often. It should be a tool in any maker / no-code dev’s toolkit!) we had found the ideal settings for pulling in some tweets around this sports team and displaying said tweets nicely on our website.

We will be making a GET request to the following endpoint, and passing along an access token in the process to authorize the API call:

https://api.twitter.com/2/tweets/search/recent?query=%23seahawks&expansions=author_id&user.fields=profile_image_url&max_results=100&since_id=

You’ll notice a few extra parameters: that’s where the experimentation with PostMan came in handy: we discovered you need to add author_id to expansions to tell Twitter you want it to return public user information too, and we wanted to make sure they send us the profile_image_url field so we have something we can display for each user’s Twitter profile picture.

There is also a since_id, which tells Twitter where you left off after a previous API call, ensuring that they don’t send you back the same tweets more than once.

Calling that API with our access token returns plenty of Tweet data. Perfect. Next we are going to take what we’ve learned with both this API call and Twitter authentication, and we’re going to add this endpoint to SheetDream to get it automatically populating our table with tweet data.

Configuring External API in SheetDream

First thing’s first, we add our new API call, and we make sure it’s configured as a GET request.

[What is a GET request and why does it matter here? In the API world, the type of API call tells the server you’re talking to what you intend to do. In the case of a GET request, you’re telling the server you’re GETting data from it. There are other actions you can take like DELETE, PUT, PATCH, and POST. DELETE’s purpose is obvious: to delete a record. PUT/PATCH both let you update an existing record (PUT generally means you want to replace a record but it can be used interchangeably). POST is when you want to create a new record]

Next, we click the “$” button next to the URL so that we can create a dynamic JSONata query. What is JSONata, you ask? It’s a JSON transformation language in the vein of what XPath was for XML. It allows you to transform one JSON structure to another. In order to construct our complete URL we need to grab the last ID of the tweet currently in our Airtable. So we’ve written some JSONata to do just that. This is to ensure Twitter only sends us new data, and not repeats.

Then, we setup our OAuth. OAuth stands for Open Authorization, and it’s a standardized methodology (for the most part) that most APIs adhere to for how they authorize API users.

Per our earlier discovery with Twitter authentication when we were tinkering with PostMan, we have input the main stuff in the Refresh Headers, Refresh Payload and Refresh Token URL.

We’ve also input an access token received from doing the tinkering in PostMan, but technically that isn’t required. All you need in this section for Twitter are the above three mentioned fields.

[What is Refresh Token and why isn’t it filled out? The refresh token field is specific to APIs where you may need to go through the classic user-focused authentication where a user approves an app to access their data. For example, if you setup a Google Sheet in SheetDream there’s a Connect To Google button you have to click. When you click that, it redirects to Google and asks if you want to allow access. After you allow it, behind the scenes they send us an access token and refresh token. The access token is shortlived, but the refresh token is much longer lived. The idea is that we don’t have to keep asking for access everyday. Refresh tokens can be used to grab new access tokens automatically. We’ll have a use case / example where we go over a user authentication flow in the future. Sit tight!]

Now here comes a tricky bit.

Twitter doesn’t just send you back all the tweets at once. Depending on how many tweets your search query will return, they may break the results up into pages, requiring several API calls. For very active hashtags you could be looking at a lot of pages.

SheetDream has native support for many different paging models used by APIs. These models tell SheetDream how it should make subsequent API calls to retrieve the rest of the pages. You can also tell SheetDream the max amount of pages you want to process to ensure you don’t pull too much data. In our case above, we tell it that we’ll only allow it to go 5 pages deep.

Twitter happens to use the “token” paging model, where they will send you back a next_token field when you call the recent tweets endpoint. More information on that here:

https://developer.twitter.com/en/docs/twitter-api/tweets/search/integrate/paginate

We select Paging Type “Parsed From Response”, which indicates that we’re going to be grabbing some data (a token) from the initial recent tweet response we receive, and use that to grab some more pages.

$.meta[$exists(next_token)].{ “__url”: “...&next_token=” & meta.next_token }

This basically says: if meta.next_token exists, we use that to construct the next URL SheetDream should query out to.

[You might be wondering: how on earth do you make sure your JSONata syntax is correct? If you click the $ button next to a query, it pops up a JSONata designer window that will guide you on the way as you write it. It will tell you of any errors in your script and give you instant feedback as you write.]

We click “Test & Load Data”, which will actually make the call out to Twitter to grab the most recent tweets. It won’t store that data into Airtable yet, but it will store it into internal memory so that we can complete the next step of configuring the Airtable storage.

If the API call succeeds, you will see a green message underneath the button saying “Test successful!”

If it fails, it will be a red message and will contain whatever error message was returned from the API. You’ll also have the option to take a look at the logs for a more in-depth look to see what had happened.

In our case, the test actually failed. And it’s because of the since_id, because we don’t have any data yet. This upset Twitter greatly, so we had to make an adjustment to our initial URL JSONata to exclude that parameter. We’ll add it back in after we successfully pull some tweet data into Airtable. And we’ll figure out if there’s a way where we don’t have to pre-seed the data before running this API call.

Storing Into Airtable

Here’s what the next section looks like:

We select the table we’d like to store our tweets into (calling it “tweets”), and we also give a name for our key field.

A key field is a unique identifier for a record within a table. Telling SheetDream which field should be considered the unique identifier for a record helps it make intelligent decisions with how it decides to store this data into Airtable.

If you take a look at the “Parse API Result” field, the JSONata again looks slightly confusing. Let’s take a look at what the return result was from the Twitter API so you can see what’s happening.

What the heck is that? All those symbols. My brain hurts.

This is actually one of those situations where you can feel free to contact us at SheetDream and we’ll help write your JSONata query, as well as explain what it does for you.

What we have above is a bit of a complex data structure twitter returns back to you.

Instead of something simple like:

{
  “text”: “....”,
  “author_id”: “...”,
  “profile_image_url”: “...”,
}, …

We get this:

{
  “data”: [{
    “author_id”: “...”,
    “text”: “...”
  },...]
  “includes”: [{
    “users”: [{
      “id”: “...”,
      “profile_image_url”: “...”
    },...]
  }]
}

Why does Twitter do this? Presumably to save space in the JSON response, in the event a single user has tweeted out a lot. Luckily, this is where JSONata shines and can transform the data for us.

What we need to do is to match up the author_id field to the id field in includes.users. We do this in order to get the profile image URL associated with each tweet. Here’s what the JSONata looks like (don’t worry we’ll break it down).

$.includes@$i.data@$d.{ "ID": $d.id, "Tweet": $d.text, "Profile URL": $i.users[id=$d.author_id].profile_image_url }

The first part of this query, $.includes@$i.data@$d, makes use of a special feature of JSONata that allows you to assign variables you can use later on in the query. It can be read as: $i = the includes section, and $d = the data section. We navigate to the includes section and @ it into $i, then we navigate into the data section and @ it into $d.

The next part of the query is the meat of it. We grab the id field from the data section (denoted by $d), and the same thing for the tweet’s text.

Then for the Profile URL, we do a little magic. We wander into the includes section with:

$i.users[id=$d.author_id].profile_image_url

This says: search for a user in includes.users where the id field matches the author_id in our tweet. Then we grab the profile image URL.

Looking at the bottom right of the JSONata designer window, we can see that we’re successfully pulling profile image URLs with this query so we’re happy with it. We’re good to go!

We save the API call to SheetDream and activate it.

After SheetDream successfully makes a call (wait a couple of minutes!) you’ll see that data starts to fill out in the Tables & Activity tab.

After hitting refresh data a few times we see the new data populate, success!

Now we need to go back into the external API configuration and re-add in the since_id logic to the initial JSONata query. Remember, it didn’t work before because we didn’t have any data populated.

This time around though we’re going to actually add some JSONata logic to check if any records are available, and if not, it won’t pass the since_id. This should appease the Twitter API gods.

Here’s what the resulting query looks like:

$.{ "__url": "https://api.twitter.com/2/tweets/search/recent?query=%23seahawks&expansions=author_id&user.fields=profile_image_url&max_results=100" & ($boolean($max($.tweets.$number(ID))) ? "&since_id=" & $max($.tweets.$number(ID)) : "") }

This makes use of the (condition ? if_true_do_this : if_false_do_this) syntax. It’s not the prettiest thing to look at, but it works.

Actually Storing Into Airtable

OK, now that our API call is working and is populating SheetDream with data, we need to setup Airtable.

We head into Airtable and create a “tweets” table (must be the exact same name as we chose in our External API configuration!). Add the following fields: ID, Tweet, and Profile URL.

We also added an extra field, “Expiration Status” which we will use internally on AirTable to expire old tweets (outside the scope of this article!).

We head back to SheetDream and configure our Airtable:

After clicking Save, we start to see the data piling in. Very neat:

To moderate the tweets, we can outright delete a tweet altogether if it doesn’t bring any value to the table. All from Airtable!

OK, so far we’ve done a lot. We’ve gotten the Twitter API call configured nicely, and Airtable is getting populated. Next, it’s time to actually display this tweet data to the users on our website.

The Twitter Widget For The Website

If we head over to the Embedding & Code tab of SheetDream, we can generate some customized HTML code for displaying your tweets on our site.

We choose a Layout of type Collage, which will give us a nice Pinterest style layout (you know the one I’m talking about).

We set the Limit to 100 here, because we don’t want to be displaying any more than 100 tweets at a time. Realistically, you would probably want to show far less than that, like 30 or so max. We’re going with 100 because this is just a proof of concept.

Next, we copy and paste this code into a JSFiddle, and start fiddling around with the HTML/CSS. After a short period of time, we wind up getting something really pretty!

This use case showcased some of the advanced API capabilities SheetDream possesses. Particularly with navigating difficult paging. This is something that even Zapier and Integromat can struggle with, and unless the app you’re using has logic built into it to handle paging you would need to write custom JavaScript/Python code for.

This use case also shows how quickly you can get something looking nice by using SheetDream’s HTML generation facility in the Embedding & Code tab, as well as knowing a little bit of CSS.

Has this use case sparked some creativity inside you? Have an idea you want to try out and want to know if it’s possible? Reach out to us! We’d love to hear about what you plan to do, and to see if SheetDream will work for your particular use case.