How to Save Tweets for any Twitter Hashtag in a Google Sheet
Wouldn’t it be nice if Twitter had an Export button? You search for a trending #hashtag, or an old tweet, or your brand name, and all the matching tweets get saved in an Excel or Google spreadsheet. And this archive would just update itself in the background as new tweets arrive.
Meet Twitter Archiver, the simplest tool for saving tweets, forever It is a Google add-on that takes 5-minutes to setup (watch video tutorial) and will easily capture all tweets that match particular search terms in a Google Spreadsheet automatically. You can use the tool to monitor tweets around any conference hashtag, learn what people are saying about your brand, track popular search terms, save tweets from any geographic location and more.
To get started, install the Twitter Archiver and it will create a new Google Spreadsheet. Go to the Add-on menu, choose Twitter Archiver and select the Authorize menu. Allow the Google Sheet to access Twitter on your behalf - the app needs this permission to only fetch tweets and will never post anything to your Twitter account.
Once your Twitter account is authorized, go to the Twitter Archiver menu again and create a new Search Rule. If you have every used the advanced search page on Twitter, this search rule screen will be very familiar. You can create rules that mention certain search terms, look for exact phrases, find tweets by #hashtags, tweets that @mention particular Twitter users and so on.
Now that you have created your Twitter search query, click “Start Tracking” button to initialize the Twitter Archiver. Internally, the sheet will connect to Twitter and pull in the historic tweets that match your search term(s). It writes these tweets in a separate sheet inside the Google Spreadsheet. After the initial set is pulled, the archiver will poll Twitter every hour and pull in the matching tweets that have been posted since the last run.
In addition to tweets, the Twitter Archiver app will also import other data including the tweet’s retweet & favorite count, the tweeter’s friend & followers count and whether they are verified or not. This data will help you filter out the spam Twitter users or easily surface tweets from the most influential users.
If you would like to stop archiving tweets for a particular search term, go to the Twitter Archiver menu, choose Saved Searches menu and you’ll see a list of your exisitng saved searches. Select the one you wish to delete from the dropdown and hit the Delete button.
Isn’t that simple? There’s no need to create any Twitter apps nor do you have to fiddle with Google Scripts.
How can I create complex search queries? The archiver supports all Twitter search operators. For instance, you can put min_retweets:5 in the Advanced Rule box to surface the best tweets for a search term.
Why does Twitter Archiver access to various services? The Archiver add-on connects to an external service (Twitter API) and saves the tweets to Google Spreadsheet. Also, should a problem arise, you can email the debug logs to the developer and hence it needs permissions to send mail.
My Twitter Archiver is not pulling all the old tweets. Why? The Twitter API does not provide all the historical tweets via the API. It will only return tweets that have been posted in the previous 5-7 days or last 1000 tweets. The archiver will however fetch future tweets for any terms that it is tracking.
Do I have to keep the sheet open to save the tweets? The archiver runs on Google servers and will therefore work even while the Google Sheet is closed. You can close the sheet, turn off your computer and the archiver will continue to work.
What is the difference between free and premium editions? The free edition of the Twitter add-on lets you track up to 2 search queries. You can track more queries in the same Google Sheet with premium. The free edition polls Twitter every hour while the premium edition pulls tweets every 15 minutes so it is more suitable for tracking terms that are generating lot of tweets (like a conference #hashtag or a live sports event).
How do I upgrade to the Premium Edition of Twitter Archiver You can use this PayPal link to upgrade to premium. It is $39.99 one-time flat fee and you can use premium for life.
Why user Twitter Archiver when IFTTT or Zapier can also save tweets? The Twitter Archiver save complete meta-data of the tweets in the spreadsheet, it fetches tweets at a much faster rate and you can visually create and manage search queries from inside a Google Sheet.
I am getting a message from Google saying “The service is using too much computer time for one day” You can only make a limited number of connections to the Twitter API from your Google Account. If you are tracking too many search terms in the Google Sheet, you might get that error. However, Google will reset the limit every 24 hours so it will resume itself the next day.
How do I save my tweets in other formats like PDF? Since the tweets are saved in a Google spreadsheet, you can easily export the search results in various formats including PDF, CSV or even publish your data set as an HTML web page (choose File -> Publish to Web inside Google Sheets). Advanced users may try this technique to create JSON or RSS Feeds from Twitter.
Google Developer Expert, Google Cloud Champion
Amit Agarwal is a Google Developer Expert in Google Workspace and Google Apps Script. He holds an engineering degree in Computer Science (I.I.T.) and is the first professional blogger in India.