Twitter Automation: Scheduled Tweets From Google Sheets
This article explains how to set up a Twitter bot in a Google Sheets spreadsheet. It provides an easy way to efficiently schedule a large number of tweets in a consistent format.
There are all kinds of ways to schedule tweets. Tweetdeck, for example, allows us to set a publishing time for a tweet (which is actually a function of Twitter Ads). However, I wanted something different. I’m starting a series of #MovieTaglines tweets on my personal account. One tweet per day, consistent format (example). I needed an easy and efficient way to manage those.
Our bot uses the Twitter API to interact with the service. That requires us to register as Twitter developers and create a Twitter app, for which we get access tokens.
The bot is basically a spreadsheet with a script. The spreadsheet hosts the data, the script contains the logic, authenticating to Twitter with the tokens mentioned above.
The spreadsheet has multiple sheets, but only one of them is important. For every tweet we want to schedule, we’re adding one row with the details: several fields which are used by the script to build the tweet’s content plus two timestamp field groups. One timestamp stores the scheduled publishing time for the tweet, the other receives the actual publishing time once the row’s data has been tweeted.
Whenever the script is run (which happens regularly through a time-driven trigger), it iterates over the rows in the sheet, looking for an entry where the scheduled time is in the past and the published time is empty. The script uses the first row that matches these criteria, tweets the row’s data, stores the publishing date, and stops.
Our bot is based on a project from Peter Berthelsen, Make Your Own Twitter Bot With Google Sheets and Google Apps Script (GAS). Peter describes in detail how to create a Twitter app. I won’t repeat that here. The process is a little cumbersome but not difficult. In short:
- Go to the developer portal
- Apply for a Twitter developer account (I selected the use case Hobbyist/Making a bot)
- Create a Twitter app
- Set your app’s permissions to read and write (default is read-only)
- Generate and copy keys and tokens. You need:
- Consumer API key
- Consumer API secret
- Access token (with read and write permissions)
- Access token secret
- Click this link to copy the sheet to your Google account.
- Navigate to Tools > Script editor.
- If you see a message asking about the Chrome V8 Apps Script runtime near the top of the window: enable V8, the project is compatible.
- In the lines 50-53 of
Tweet.gs, fill in our Twitter credentials and save the script by pressing CTRL+S.
- Go back to the sheet
SendTweetsand send a test tweet by filling out the
Tweet textfield and clicking Send tweet.
We want to script to be executed at regular intervals, e.g., every 10 minutes. To achieve that, we configure a time-driven trigger:
- In Script editor click Edit > Current project’s triggers.
- Click Add trigger and configure the new trigger as follows:
- Function to run:
- Event source:
- Type of time-based trigger:
- Minute interval:
Every 10 minutes
- Function to run:
And that’s it – you’ve completed the setup of your first Twitter bot.
In all likelihood, you are not interested in tweeting movie taglines. Luckily, modifying the bot only involves adjusting the spreadsheet and a few lines of script code. Let’s take a look at the inner workings to prepare you for that.
The spreadsheet contains three sheets:
- SendTweets: only for testing by sending a single tweet.
- MovieTaglines: the script’s data repository (aka, the content for your tweets).
- TweetLog: every successful tweet is logged here.
MovieTaglines sheet has the following columns:
- Scheduled date [UTC]: the planned date for the tweet (number format needs to be
- Scheduled time [UTC]: the planned time for the tweet (number format needs to be
- Tagline: part 1 of the tweet’s content.
- Movie name: part 2 of the tweet’s content.
- Movie year: part 3 of the tweet’s content.
- IMDb URL: part 4 of the tweet’s content.
- Scheduled for [UTC epoch]: scheduled tweet timestamp as Unix epoch (auto-calculated by a macro, do NOT modify).
- Tweeted at [UTC epoch]: actual tweet timestamp as Unix epoch (filled out when the row’s content has been tweeted, do NOT modify).
- Tweeted at [UTC time]: actual tweet timestamp in human-readable format (auto-calculated by a macro, do NOT modify; number format needs to be
The bot consists of three layers of scripts. From top to bottom:
- TweetMovieTaglines.gs: this is my code. It reads the data from the sheet and tweets by calling routines from
- Tweet.gs & Threads.gs: wrapper around Twitter lib providing logging and functions for tweet replies/threads (not used by me).
- Libraries: Twitter lib and OAuth.
To customize the bot, modify the content area in the
MovieTaglines sheet. I found it useful to split the content into multiple fields for easier handling (tagline, movie name, movie year, and URL), but for other purposes, a different number of tweet components will be more useful. In the most simple case, a single field for the tweet content might be sufficient.
Once you have configured the content area, adjust the script
- Column indexes in lines 11-19
- Hashtag in line 22
- Field value access in lines 51-55
- Tweet content format in line 84
To verify that your changed code is working, set a breakpoint by clicking a line number next to a point of interest. Then click the bug icon to manually run the function
tweetMovieTaglines. The script stops at the breakpoint; you can inspect variable values and step through the following instructions by clicking the corresponding icons (the almost-universal keyboard shortcuts F10 and F11 do not work here, unfortunately).
Changing names is easily possible, you don’t have to keep my “movie” names around.
If you rename the
MovieTaglines sheet, remember to update the name in line 26 of
The script file
TweetMovieTaglines.gs can be renamed, of course, too. If the script’s function
tweetMovieTaglines is renamed, you need to update it in the trigger, too.
To add a scheduled tweet to the list, simply add a row to the
MovieTaglines sheet. Make sure that the
Scheduled for [UTC epoch] field macro fills it with a large number (like 1597426380).
To work on content without scheduling it yet, simply leave the first two columns (date & time) empty. Once a tweet is ready to be published, fill out the date & time columns (which populates the
Scheduled for [UTC epoch] field, which in turn is used by the bot).
The bot keeps track of which spreadsheet rows have been tweeted by adding the tweet’s timestamp to the
Tweeted at [UTC epoch] field. If you clear that field, its row’s content will be processed again.
The Google Apps Script portal lists every script run on the page My executions. You should see one entry every 10 minutes. If there was nothing for the script to do, the log just contains No logs are available for this execution. If, on the other hand, the bot tried to send a tweet, you should find a detailed log along with error messages, if there were any.