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.