How to build a Google Spreadsheet that auto-tweets your archives

How to
Share on FacebookShare on Google+Tweet about this on TwitterPin on PinterestShare on LinkedInEmail this to someone

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:

copyof1stpage

 

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.

createapp

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.

keys

 

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…

script editor

 

Retrieve the Project Key in your Script editor

Once in your script editor, find Project properties under the File tab.

scrpedit2

 

You’ll easily find the Project key here. Highlight and copy it.

projectpr

By the way, this Script editor shows you the guts of this dynamic Google spreadsheet. Google gives the code a .gs extension but you can see that it is heavily based on Javascript. Scroll through it to identify how it communicates with Twitter and how it decides what spreadsheet cells to Tweet out. The full code from this Auto-Tweeter script can be found here.

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.

 

projurl

 

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.

 

loadtweets

 

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.

genprev

 

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:

auth

 

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.

 

wsj

 

Acknowledgments

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!

 

 

Storybench’s editor is Aleszu Bajak, a science journalist and former Knight Science Journalism Fellow at MIT. He is an alum of Science Friday, the founder of LatinAmericanScience.org, and is passionate about breaking down the divide between journalists, developers and designers.

6 comments

  • Hi, the bot works quite well for its intended purpose but I am trying to tweak it to tweet the archive I have in order, then wrapping around and starting from the beginning again. I’m a beginner at code and I can’t seem to figure out how to do it. Do you by any chance know how to do it?

  • Is there a way to have the tweets appear in order as opposed to the randomized one that happens when generating the previews? New to coding, so just curious as to where to look. Thank you!

Leave a Reply