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.
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.
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.
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!
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?
i’m looking at the same thing Justin, and probably for the same reason, based on the time stamps.
In the A1 cell of the “Select tweets” worsksheet, add a zero (0).
Go to tools > Script editor.
On line 197, replace:
var word = list[k][Math.floor(Math.random()*(len + 1))];
with:
var iLast = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Select Tweets”).getRange(‘a1’).getValue();
if (iLast > list.length – 1) iLast = 0;
var word = list[k][iLast];
SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Select Tweets”).getRange(‘a1’).setValue(iLast + 1);
This will work through them one by one then circle back when it gets to the end of the list.
I am looking to change the time interval of tweets posting to 2 minutes . what will be the procedure to do that ?
Just learnt python and how to web scrape, this would be a great addition to setting up my fully automatic twitter bot. Thanks!
Twitter just locked down its apps to developers requiring approval. So it’s not as easy as it used to be
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!
Did you ever find a way to do this?
Does this allow you to tweet images as well, or only text?
Is there a way to add photos to the tweets?
Is there any way to lower the frequency to something like 24 hours (instead of 12) ?
Hello!
I just started getting the following error after a few months using the service:
Error handling callback: token mismatch (line 225, file “Service”, project “OAuth1”)
Any idea of what’s happening?
This script is quite old so I wouldn’t be surprised if the credentialing/verification has changed.
Hi super amazing tutorial. Everything seemed to be working though the test tweet isn’t showing up on my timeline. Any thoughts? Before that twitter asked me to authorize the app so I think it seems to be working though not sure where the breakdown is. I think maybe with the Twitter updates? Anyway thanks again for making this
as of March 2023, this still works to tweet text. it does not post images.