How to build a Google Spreadsheet that auto-tweets your archives
Set it and forget it. That’s what we wanted from a program that would reduce our social media workload and surface the Storybench archives. Much like the “scheduling tweets” function from Twitter programs like Hootsuite, we wanted a simple-to-use script that would auto-tweet stories from our backlog.
We found a solution created by Zach Whalen, a professor of digital media at the University of Mary Washington. Whalen has a tutorial for building a Twitter bot, a program that automates functions like posting or replying on Twitter. (Some popular Twitter bots are @horse_ebooks, @museumbot, and @everyword.)
We adapted Whalen’s Twitter bot program into a lean script that auto-tweets entries from a Google Spreadsheet every six hours (or every 15 minutes, if you’d like.) Here’s how:
Copy the Auto-Tweeter spreadsheet
Make a copy of our Auto-Tweeter spreadsheet by clicking here. You should see this:
Create a Twitter app
In order for your spreadsheet to communicate with your Twitter account, you need to set up a Twitter app. Go to apps.twitter.com and sign in with the account from which you would like to auto-tweet.
Give you app a name, description and URL. Leave the Callback URL field empty for now. We’ll be returning to that later. Agree to the Developer Agreement and create the app.
Retrieve your API Key and API Secret
In order to communicate between your spreadsheet and the Twitter API, you’ll need to plug the API key and API secret (two long strings of characters and numbers) into your Auto-Tweeter Setup sheet. They can be found under the Keys and Access Tokens tab.
Plug those two keys into your spreadsheet Setup sheet. The following field asks for a Project Key. This can be found by navigating to the Tools tab in the spreadsheet and clicking Script editor…
Retrieve the Project Key in your Script editor
Once in your script editor, find Project properties under the File tab.
You’ll easily find the Project key here. Highlight and copy it.
Paste the Project key into your Setup sheet.
Insert Callback URL into Twitter app
Once the Project key is filled in, the spreadsheet auto-completes cell B37. Cell B37 is the Callback URL and you should copy and paste this into your Twitter app settings back at apps.twitter.com. Find the Callback URL field under the Settings tab and paste it in. Save by clicking Update settings.
Loading in your tweets
In your spreadsheet, navigate over to the Select Tweets sheet (which appears as a tab along the bottom). Start loading your tweets. We loaded headlines and links from the 100 stories in the Storybench archive.
Preview your tweets
Once you’ve loaded your tweets, head over to the Preview Output tab on the spreadsheet and then click Generate Preview under the Bot tab on the top menu. You’ll see a list of the randomly generated tweets pulled from the tweets you loaded in the Select Tweets sheet.
In order to generate this list, however, you’ll have to authorize the scripts in this spreadsheet. When prompted, authorize the spreadsheet.
Authenticate your app with Twitter
In the Bot menu, click Send a Test Tweet. This should result in a pop-up window asking you to navigate over to the Twitter website and authenticate the app with your Twitter account. Click the link and then authorize the app:
Send your first tweet
You have successfully authorized your Auto-Tweeter spreadsheet. Click Send a Test Tweet again and check your Twitter timeline.
Set the timing of your tweets
Find cell B54 on your Setup sheet. This will have a dropdown menu for setting the frequency of your Auto-Tweeter. It can tweet as frequently as every 15 minutes or as infrequently as every 12 hours.
Start up your Auto-Tweeter
Finally, select Start Posting Tweets from the Bot menu. Done! As long as you have your Open Graph tags (social media metadata) loaded up, your links will become expanded links on Twitter and include a headline, a teaser, and photos. Read our tutorial on adding that metadata here.
Zach Whalen built this bot and wrote a very helpful tutorial for it. We’ve changed some of the copy and a few lines of code to adapt it for our Archive Auto-Tweeter program. Please head there for technical support and troubleshooting advice.
Whalen agreed to let Storybench use his Twitter bot because he’s been encouraged by how many people have found it useful so far. This is what he told us:
“Ultimately, despite its limitations, I think my SSBot tool gets people thinking about language and Twitter in a different way, and by lowering the barrier for making a bot, it hopefully encourages more creative and critical thinking with programming.”
So what will you use this Twitter bot for? Is auto-tweeting stories from the archive helpful? What are you automating with similar technology? Please let us know!