# Using Google Spreadsheet to automatically monitor Twitter event hashtags and more

**Update:** There is a [new version of this spreadsheet here](https://hawksey.info/blog/2011/02/twitteralyticsv2/)

I recently read Tony Hirst’s [onFormSubmit – Raising Web Scale Events in Google Spreadsheets](http://blog.ouseful.info/2010/06/09/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](https://hawksey.info/blog/2010/03/using-google-apps-script-for-a-event-booking-system/) 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 . > [@psychemedia](https://twitter.com/psychemedia) [@mweller](https://twitter.com/mweller) Ive just captured stats & image of word cloud if [\#iwmw10](https://twitter.com/hashtag/iwmw10?src=hash) tweets to observe changes over time.
> 
> — Brian Kelly (@briankelly) [June 14, 2010](https://twitter.com/briankelly/status/16167584760)

> [@briankelly](https://twitter.com/briankelly) have you scheduled that capture using a service, or is it a manual chore?
> 
> — Tony Hirst (@psychemedia) [June 14, 2010](https://twitter.com/psychemedia/status/16168122515)

> [@psychemedia](https://twitter.com/psychemedia) A manual capture now, week b4, during & after. Will help understand if worth development effort.
> 
> — Brian Kelly (@briankelly) [June 14, 2010](https://twitter.com/briankelly/status/16168406366)

So here it is my [Twitteralytics Google Spreadsheet](https://spreadsheets.google.com/ccc?key=0AqGkLMU9sHmLdHU2NWVkUFlKOG5zWWhmQ1R2MVVlX2c&newcopy). The video below gives a quick overview of how to set it up and what it does:

[Twitteralytics - Google Spreadsheet](https://vimeo.com/13180235) from [Martin Hawksey](https://vimeo.com/mhawksey) on [Vimeo](https://vimeo.com).

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](http://twapperkeeper.com). 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](http://summarizr.labs.eduserv.org.uk/?hashtag=iwmw10) or Tony Hirst’s Yahoo Pipes for [Personal Twitter Networks in Hashtag Communities](http://blog.ouseful.info/2009/09/09/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)](http://scienceoftheinvisible.blogspot.com/2010/06/continuous-student-ratings-with-twitter.html). 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 AutomateAnalytics.com](http://www.automateanalytics.com/2010/05/fetching-tweets-to-excelwordppt.html), 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](http://docs.google.com/support/bin/topic.py?topic=15164).