Previously in Google Apps Script: Using a Google Spreadsheet to populate a Twitter list [Hashtag Communities] I show how you can use Google Spreadsheets to populate a Twitter list. A comment on this post by Colin Gray got me thinking, instead of having to drop a list of usernames from another source, what if the spreadsheet could use the Twitter Search to pull usernames and populate a list. As I mentioned in my response to Colin I have already documented pulling Twitter searches in Using Google Spreadsheet to automatically monitor Twitter event hashtags and more. So here it is:
*** The Auto Add to Twitter List Google Spreadsheet ***
The first sheet of this Spreadsheet has setup instructions. If you get into any problems leave a comment in the box at the end of this post. One thing I’ll say is be patient some operations can take over a minute if you having lists with lots of users.
Use case
The most obvious use case is to use this spreadsheet to generate a real-time conference hashtag community list. Variations on this might be to generate a list as you speak but this might be more gimmicky than of practical use.
Advance search
It’s worth noting that you are not limited to just searching for hashtags. The search terms used in the Spreadsheet can include most of the Twitter Search Operators (‘since’ is the only one that the Spreadsheet overrides). For example your search term could be ‘to:mhawksey near:Edinburgh within:400mi’ which would collect a list of users who tweet me within 400 miles of Edinburgh.
The ‘techie bit’
So what’s new? The two main additions are utilising: Time-Driven Triggers which are part of Google Apps Script (these enable you to run Scripts on a Spreadsheet even when it is not open); and Mikael Thuneberg’s getTweets() function which is in the Script gallery.
The getTweets function uses the Apps Script UrlFetchApp.fetch to return results from the Twitter Search page.This is a pretty powerful tool for other mashineering as you can call a webpage using different methods (‘post’, ‘get’, ‘put’, ‘delete’).
The way I’ve modified Mikael’s script is to return JSON rather than XML data from Twitter (BTW Brian Kelly has an interesting post on Moves Away From XML to JSON?, my response is it is application specific, in this particular case it was easier to get JSON). I also added some code which only returned a unique array of twitter usernames from the search.
That’s about it really. If you have any questions about this script just drop them in the comments.
PS A big thank you to Lilian Soon (@xlearn) and Shri Footring (@ShriFootring) for help with testing/debugging
PPS I’m giving a face-to-face presentation as part of the JISC Winter Fayre (#jiscwf) looking some of the ways you can use Twitter to support the backend of teaching and learning on the 10th December at 12:20pm GMT. I’ll hopefully be streaming the session live via Livestream so please join me if you like.
Festive Tweets: Exploring the Utility of Twitter to Support Teaching and Learning
From passive to active communication, from one-way broadcast to two-way collaboration, there are a number of ways Twitter can be used to support teaching and learning. In this session we highlight some of Twitter’s hidden treats and explore how Twitter can be used as a free SMS broadcast system; how tweets from mobiles and other devices can be used as an alternative to electronic voting systems; how you can create and control a system to automatically tweet resources and announcements; how tweets can be used for lecture capture enhancement; and how Twitter can be used to leave timeline-based comments on videos.
Last updated by Martin Hawksey at .
Just wonder if I’m missing something. The Google Code instructions say that there should be a “twitter” menu item in the spreadsheet. I cannot see this in yours. I feel like I’m missing how to authorize the spreadsheet to my twitter account. Any suggestions?
Hi Phil, This image yfrog.com/h3u2mkj shows where the ‘Twitter Menu’should appear
Cheers,
Martin
Got a problem here, everytime I run AddToList function, it said there’s a problem with authorization. But the thing is, I’ve authorized from twitter several time using the Script window from the spreadsheet. Wonder what went wrong?
Wonder if it’s a typo in one of your keys/tokens (maybe publish as a service key is Apps Script #clutchingatstraws)
Mm, I don’t think so Martin. I’ve tried it several times, even from scratch, still can’t figure it out. Wonder if it has something to do with JSON? It seems that everytime I try AddToList, the spreadsheet ask for an authorization, and that authorization is somehow rejected. I wonder.
have to tried running the ‘authorize’ script before ‘AddToList’?
Hi Martin
When I click on the link to copy the spreadsheet I get an error message “We’re sorry, your spreadsheet cannot be copied at this time. ”
Any ideas?
Justin
Usually its a problem with google. If you are still having problems let me know
Martin
This looks great!
I’m also having the same problem as the last poster, I get the message
“We’re sorry, your spreadsheet cannot be copied at this time.” when I click on the link
*** The Auto Add to Twitter List Google Spreadsheet ***
which is a short cut to:
https://spreadsheets.google.com/ccc?key=0AqGkLMU9sHmLdDlyRld1V0JLdS0yNFR0SVZtVmJrdHc&hl=en_GB&newcopy
Much appreciated!
I think I’m getting closer to being able to use this for my research. Can you explain how the search term search works a little more. I am trying to use from:JetBlue for example. Will that pull tweets from JetBlue? I’m trying to follow your example above where you have to:mhawksey.
Hi Kelli – yes that’s how from works. Here are the search operators as described by the Twitter Search page (I’ve edited out the location operators that don’t work with the Twitter Search API):