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 AutomateAnalytics.com, 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.

5 thoughts on “Using Google Spreadsheet to automatically monitor Twitter event hashtags and more

  1. Pingback:

  2. Pingback:

  3. Pingback:

  4. Pingback:

Comments are closed.