Using Google Spreadsheet to automatically monitor Twitter event hashtags and more

I recently read Tony Hirst’s onFormSubmit – Raising Web Scale Events in Google Spreadsheets post which highlights opportunities with the new event triggers (the ability to trigger scripts on events like form submit or time-driven like every x minutes) and was blown away by the possibilities. My first thought was to re-examine my event booking spreadsheet but when I saw the twitter conversation below between Brian Kelly and Tony (psychemedia) I saw something better to do - a Google Spreadsheet which could capture and report the daily/weekly twitter activity from an event hashtag .

Brian Kelly (briankelly) @psychemedia @mweller Ive just captured stats & image of word cloud if#iwmw10 tweets to observe changes over time.
7:34 PM Jun 14th via Seesmic

Tony Hirst (psychemedia) @briankelly have you scheduled that capture using a service, or is it a manual chore?
7:43 PM Jun 14th via TweetDeck in reply to briankelly

Brian Kelly (briankelly)@psychemedia A manual capture now, week b4, during & after. Will help understand if worth development effort.
7:48 PM Jun 14th via Seesmic in reply to psychemedia

So here it is my Twitteralytics Google Spreadsheet. The video below gives a quick overview of how to set it up and what it does:

In summary the script is designed as a way to automatically pull daily or weekly results from twitter, summarise them and push an update via email. The spreadsheet also keeps a copy of the sampled tweets which could be used for further analysis down the line but I would recommend you only use this as a backup for a separate twitter archive on Twapper Keeper. There are also a number of other services which are probably better for more in-depth post analysis of event hashtag communities like Andy Powell Summarizr or Tony Hirst’s Yahoo Pipes for Personal Twitter Networks in Hashtag Communities.

While the spreadsheet was created to monitor event hashtags it could easily be modified for other applications. For example, I’m sure it could be easily modified to collect continuous student with twitter (as highlighted by AJCann). Also, it doesn’t have to be limited to Twitter results, the same framework could be used for other XML and HTML data.

How it was done

As always I like to document how it was done. The rough process of the script is:

  1. Open spreadsheet by ID (required when using event triggers)
  2. Copy the TMP sheet giving it the script execution date/time as a name
  3. getTweets from Twitter – more about this later
  4. Write results to new sheet
  5. Copy total and top tweeters to ‘Summary’ page
  6. Prepare and send email of summary results

The getTweets script I picked up from Mikael Thuneberg from, making a small modification to include date limits. The script uses built-in functions for fetching external data and parsing the results.

The TMP sheet also has some useful formula’s worth highlighting.

  • H2 - ‘=UNIQUE(E2:E)’ creates a list of the authors removing duplicate entries.
  • I2 - ‘=EXPAND(ArrayFormula(IF(ISBLANK(H2:H), “”, COUNTIF(E2:E,H2:H))))’  for every unique author in column H the formula count the number of times there name appears in column E
  • A2 - ‘=SORT(H2:I, I2:I, FALSE)’ sorts the values in column H (author names) by column I (the number of occurrences)

All of these formula automatically fill the column with results. More information on Google Spreadsheet formulas is here.

3 Responses to “Using Google Spreadsheet to automatically monitor Twitter event hashtags and more”

Leave a Reply


This blog is authored by Martin Hawksey e-Learning Advisor (Higher Education) at the JISC RSC Scotland N&E.

JISC RSC Scotland North & East logo

If you would like to subscribe to my monthly digest please enter your email address in the box below (other ways are available to subscribe from the button below):

Subscribe to MASHe to monthly email updates


The MASHezine (tabloid)

It's back! A tabloid edition of the latest posts in PDF format (complete with QR Codes). Click here to view the MASHezine

Preview powered by: Webthumb

The MASHezine (eBook)

MASHe is also available in ebook and can be downloaded in the following formats:



Opinions expressed in this blog are not necessarily those of the JISC RSC Scotland North & East.

JISC Advance Logo

JISC Advance is a new organisation that brings together the collective expertise of established JISC services:

For further information visit

Creative Commons Licence
Unless otherwise stated this work is licensed under a Creative Commons Attribution-ShareAlike 2.5 UK: Scotland License