Top Daily Tweets Google Apps Script Email


The idea:

I found myself compulsively checking Twitter on my phone at random times during the day. I wanted to check it less but still receive the most popular tweets from my timeline each day. I also wanted to see all tweets from specific people I follow like my family and closest friends. Knowing that I’ll see the most important tweets each day will help make sure I stay off Twitter during the day. This was also a good excuse for a project!

The finished product:

Email showing top tweets and following tweets

The tech:

I chose Google Apps Script and Google Sheets for this project because I wanted something that could run every day for free. Sheets and Apps Script can send emails to my Gmail account at certain times each day very easily.

The process:

The first step was finding how to use the Twitter API with Google Sheets. This blog post was the starting point. In this post, they used Twitter Lib for Google Apps Script, which after some more research seemed to be the best option with Google Sheets. The library allowed me to authenticate and make contact with the Twitter API. It had functions to return tweets based on a search string and post tweets. But I wanted to scan my timeline and a list with specific users. To do that, I copied the library files into my spreadsheet file and added code directly to the bottom of the library. I didn’t care about making my functions match the conventions of the library. I just needed to get my use case working.

First, on the Top Tweets tab, I call the API for my home timeline. The home timeline can pull a maximum count of 200 in one call, so I set a loopCount of 3 to pull 600 tweets in a loop. That’s plenty for me because I don’t follow a lot of people. 600 tweets easily pulls the last ~24 hours worth of tweets. I added total_count which is the sum of the tweets’ likes and retweets. hours_since_tweet shows how much time has passed since the tweet was posted. Once the 600 tweets are pulled I delete any tweets that are older than 24 hours. The remaining tweets are then sorted by the highest total_count to lowest. Finally, I delete everything after the top 10.

Google sheet for top tweets

I can control how many tweets to return and how many hours back to cut off using another sheet. Here I’ve set 10 tweets and to cut off after 26 hours.

Google sheet with number of tweets and hour settings

Once the top tweets are pulled, I pull the last 600 tweets from a list I created with the people I want to see all of their tweets. This tab does not filter by number of likes and retweets.

Google sheet for list of tweets

I was able to use the same function for both calls, and just modify the sheet name and API URL being passed in.

The data from both sheets are then combined into an email and sent on a daily timer.

Google Sheets time based trigger setup

In the email, I made the name of the person link to the specific tweet where I can easily like/retweet/comment.

Possible improvements:

In this setup, the API is called 6 times because both calls loop and do 3 calls. The Twitter API rate limits in 15 minute windows. So if you were following a lot more people and wanted to see the top tweets in the last 24 hours you could set up some delays in the code to loop through over a period of time and increase loopCount.

I also intentionally didn’t include any media (images/videos) in the email to keep things simple, but those could be added.

The code: