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.

Why Create Our Own Bot?

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.

How It Works

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.

Setup

Twitter App

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:

1. Go to the developer portal
2. Apply for a Twitter developer account (I selected the use case Hobbyist/Making a bot)
3. Create a Twitter app
4. Set your app’s permissions to read and write (default is read-only)
5. Generate and copy keys and tokens. You need:
   • Consumer API key
   • Consumer API secret
   • Access token (with read and write permissions)
   • Access token secret

Google Sheet

Basics

1. Click this link to copy the sheet to your Google account.
2. Navigate to Tools > Script editor.
3. 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.
4. In the lines 50-53 of Tweet.gs, fill in our Twitter credentials and save the script by pressing CTRL+S.
5. Go back to the sheet SendTweets and send a test tweet by filling out the Tweet text field and clicking Send tweet.

Script Trigger

We want to script to be executed at regular intervals, e.g., every 10 minutes. To achieve that, we configure a time-driven trigger:

1. In Script editor click Edit > Current project’s triggers.
2. Click Add trigger and configure the new trigger as follows:
   • Function to run: tweetMovieTaglines
   • Event source: Time-driven
   • Type of time-based trigger: Minutes timer
   • Minute interval: Every 10 minutes

And that’s it – you’ve completed the setup of your first Twitter bot.

Customization

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.

Sheets

The spreadsheet contains three sheets:

1. SendTweets: only for testing by sending a single tweet.
2. MovieTaglines: the script’s data repository (aka, the content for your tweets).
3. TweetLog: every successful tweet is logged here.

The MovieTaglines Sheet

The MovieTaglines sheet has the following columns:

1. Scheduled date [UTC]: the planned date for the tweet (number format needs to be Date).
2. Scheduled time [UTC]: the planned time for the tweet (number format needs to be Time).
3. Tagline: part 1 of the tweet’s content.
4. Movie name: part 2 of the tweet’s content.
5. Movie year: part 3 of the tweet’s content.
6. IMDb URL: part 4 of the tweet’s content.
7. Scheduled for [UTC epoch]: scheduled tweet timestamp as Unix epoch (auto-calculated by a macro, do NOT modify).
8. Tweeted at [UTC epoch]: actual tweet timestamp as Unix epoch (filled out when the row’s content has been tweeted, do NOT modify).
9. Tweeted at [UTC time]: actual tweet timestamp in human-readable format (auto-calculated by a macro, do NOT modify; number format needs to be Date time).

Scripts

The bot consists of three layers of scripts. From top to bottom:

1. TweetMovieTaglines.gs: this is my code. It reads the data from the sheet and tweets by calling routines from Tweet.gs.
2. Tweet.gs & Threads.gs: wrapper around Twitter lib providing logging and functions for tweet replies/threads (not used by me).
3. Libraries: Twitter lib and OAuth.

How to Customize the Bot

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 TweetMovieTaglines.gs accordingly:

• 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).

Renaming

Changing names is easily possible, you don’t have to keep my “movie” names around.

Renaming the Sheet

If you rename the MovieTaglines sheet, remember to update the name in line 26 of TweetMovieTaglines.gs, too.

Renaming the Script (Function)

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.

Operations

Adding a Scheduled Tweet

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).

Draft Content Without Scheduling

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).

Resend a Tweet

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.

Troubleshooting

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.