We often see Tweets and screenshots of Tweets posted outside of the Twitter app. And we know a lot of people copy and paste Tweets too. Whether you’re keeping track of your latest favorite meme or taking note of interesting commentary, you may think it’s easy and common enough to simply keep a list of Tweets in a spreadsheet, like Google Sheets. But how can you make sure you selected the entire Tweet? Or what if a Tweet gets deleted? Thanks to the new Twitter API, there is a much better way to get a list of publicly available Tweets in Google Sheets: you can create two lookup formulas to retrieve Tweets and their details, including the username of the author:
Copy and paste can get messy at times, and you may end up copying unwanted formatting and other elements from the page. Instead, this Google Sheet formula gives you what you need. More importantly, though we don’t have an edit button, Tweets may change – for example, the author may delete it or choose to make it private. When that happens, your list must reflect these changes, and a formula can automatically refresh your Tweets so you always have the latest information available.
You can use the Tweet lookup endpoint with a Bearer token. Bearer token authentication is easy to implement. You will find a Bearer token in your Developer Portal, under “eys and tokens”. Take note of your Bearer token; you will need it later.
Set up your environment
Let’s start with a new spreadsheet. In case you didn’t know, there’s a shortcut to create a new Google Sheet: just navigate to https://sheets.new and you’ll have a blank slate from which to start. Next, select Script editor from the Tools menu to enter the Google Apps Script environment.
Ask for a Bearer token
It’s never a good idea to store credentials in code. You can store them separately. In Apps Script you can store data in the engine’s storage, and make it available only to your custom script. This is a good way to keep things separate, particularly if you use other add-ons. This is a two-step process. First, we display an input dialog to ask for the bearer token. And then, we store it:
The magic formula
You probably noticed the code requests some additional fields in the Tweet request. Fields allow us to use the same API request to populate information about the user through two separate formulas, TWEET_AUTHOR and BIO_OF_TWEET_AUTHOR:
Those two functions simply rely on the TWEET formula we previously created to request a Tweet, including its author’s details, where available. At this point, you should have the Tweets you need flowing into Google Sheets.
Wrapping it up
The full script contains a few nice additions, like a custom Twitter menu that allows you to change the Bearer token or to forget it completely, effectively signing you out and removing the token from the system. You can find it on Github as a Gist.
Remember, Tweets are subject to the content redistribution rules, so always be mindful of that policy. We used several tools and services beyond the Twitter API to make this tutorial, but you may have different needs and requirements and should evaluate whether those tools are right for you.