⌙ Google , Google Apps , Google Apps Script , Google Sheets

Populating a Twitter List via Google Spreadsheet … Automatically! [Hashtag Communities]

Martin Hawksey

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.

chevron_left
chevron_right

Join the conversation

comment 18 comments

  • Phil

    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?

  • Adityo

    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?

    • Martin Hawksey

      Wonder if it’s a typo in one of your keys/tokens (maybe publish as a service key is Apps Script #clutchingatstraws)

  • Adityo

    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.

    • Martin Hawksey

      have to tried running the ‘authorize’ script before ‘AddToList’?

  • More Pivots Around Twitter Data (little-l, little-d, again;-) « OUseful.Info, the blog…

    […] First up, hashtag discovery from Twitter lists. Twitter lists can be used to collect together folk who have a particular interest, or be generated from lists of people who have used a particular hashtag (as Martin H does with his recipe for Populating a Twitter List via Google Spreadsheet … Automatically! [Hashtag Communities]). […]

  • Justin

    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

    • Martin Hawksey

      Usually its a problem with google. If you are still having problems let me know
      Martin

  • Kelli Burns

    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.

    • Martin Hawksey

      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):

      Operator Finds tweets…
      twitter search containing both “twitter” and “search”. This is the default operator.
      “happy hour” containing the exact phrase “happy hour”.
      love OR hate containing either “love” or “hate” (or both).
      beer -root containing “beer” but not “root”.
      #haiku containing the hashtag “haiku”.
      from:alexiskold sent from person “alexiskold”.
      to:techcrunch sent to person “techcrunch”.
      @mashable referencing person “mashable”.
      …
      superhero since:2010-12-27 containing “superhero” and sent since date “2010-12-27” (year-month-day).
      ftw until:2010-12-27 containing “ftw” and sent up to date “2010-12-27”.
      movie -scary 🙂 containing “movie”, but not “scary”, and with a positive attitude.
      flight 🙁 containing “flight” and with a negative attitude.
      traffic ? containing “traffic” and asking a question.
      hilarious filter:links containing “hilarious” and linking to URLs.
      news source:twitterfeed containing “news” and entered via TwitterFeed

  • Google Apps Script, Spreadsheets, Twitter and Gadgets #guug11 JISC CETIS MASHe

    […] or POST by publishing as a serviceCollect/backup tweets in a Google Spreadsheet [Twitteralytics v2]Populating a Twitter List via Google Spreadsheet … Automatically! [Hashtag Communities]The best Google Spreadsheet Event Manager (w/h Sites, Contact, Calendar integration) ever, ever, […]

Comments are closed.

css.php