Twitter: How to archive event hashtags and create an interactive visualization of the conversation

The use of Twitter to collecting tweets around an event hashtag allowing participants to share and contribute continues to grow and has even become part of mass media events, various TV shows now having and publicising their own tag. This resource is often lost in time, only tiny snippets being captured in blog posts or summaries using tools like Storify, which often loose the richness of individual conversations between participants.

It doesn’t have to be this way. Using a combination of Google Spreadsheets as a data source and a simple web interface to add interactivity it’s possible to let users explorer your entire event hashtag and replay any of conversations.


View example conversation replay

Try out a LIVE version

Update: If you are still struggling to understand the concept Radical Punch have done a overview of this tool

Here's how to archive event hashtags and create an interactive visualization of the conversation (written instructions below):


Twitter: How to archive event hashtags and create an interactive visualization of the conversation

Capturing the tweets

  1. Get and setup TAGS

Creating a public interactive visualisation of the archived tweets

  1. Copy the url of the spreadsheet you just created
  2. Visit http://hawksey.info/tagsexplorer and paste your spreadsheet url in the box, then click 'get sheet names'
  3. When it loads the sheet names leave it on the default 'Archive' and click 'go'
  4. You now have a visualisation of your spreadsheet archive (click on nodes to delve deeper)
  5. To share the visualisation at the top right-click 'link for this' which is a permanent link (as your archive grows and the spreadsheet is republished this visualisation will automatically grow)


191 thoughts on “Twitter: How to archive event hashtags and create an interactive visualization of the conversation

  1. Pingback:

  2. Pingback:

  3. I was so excited to find this. I think I have it setup correctly. I was able to authenticate but when I run the script I get this error:

    Twitter said: Line 231 Unexpected error: Exception

    Reply
    1. Hi there!

      I am trying to map out our hashtag #smwedelman
      i know we have over 1400 tweets about the event but the results only pull in 105, is this to do with the date range? Or twitter being tempramental?

      really would love to get the full picture!

      thanks!!

      Reply
  4. syria

    i have problem everytime i press Tools > Script Editor i get this error
    Google error docs
    ==========
    {"message":"HTML Parsing Error: Unable to modify the parent container element before the child element is closed (KB927917)","name":null,"lineNumber":"Not available","fileName":"https://docs.google.com/macros/edit?uiv=2&tz=Europe/London&docTitle=Copy+of+TAGS+v2.4.3&csid=tyuglWnx4Jc613JkHm9noAA.13973955951514686187.3987036906824716291&mid=ACjPJvHMEUTwbGT_-t8LruR67c52O2gKQnVZfD4G300rntS76daI3jJkurjtjqxkLZRvI1GRmPNC6PqEw1D5ike9bOCogux2AEd78FjIHg&hl=en_GB&lib=MWvzTkMXz21b_YUp1rvlcO9Y2JhWsis9e","stack":"Not available","severity":"fatal"}
    ------------
    please help me

    Reply
    1. Post author

      Google Spreadsheets seem to occasionally fail to make a good copy of the template. Usually taking a new copy of the template works. If it doesn't let me know and I'll look into it further

      Reply
    1. Post author

      Appears to have data now. BTW Twitter search is limited to 1500 results you might want to switch to the 'weekly' instead of daily option and collect 1500 tweets every 3 hours
      Martin

      Reply
  5. I can't get the Twitter menu item to show, even when I use the button "If twitter menu is missing […]". All I get then i an alert with "Oops
    Script function onOpen could not be found".

    Anyone else have run into the same problem? Running Chrome and Firefox in Mac OSX - Lion

    Reply
    1. Post author

      @Joakim - in the spreadsheet check Tools > Script editor ... and make sure there is some script there (if not looks like you've got a bad copy and need to try copying the template again) If there is script there try Run > onOpen from the script editor menu

      If you are still having problems let me know (the script in the spreadsheet is reasonably stable now but Google Spreadsheets can be a bit flaky)
      Martin

      Reply
  6. Post author

    When you open Tools > Script editor ... and only see function myFunction()... this indicates that Google didn't copy the Spreadsheet template properly for you. You can:

    a) try recopying the template until it works; or
    b) copy and paste the code here into the Script editor and save it

    If you are still having problems leave a comment ;)

    Reply
  7. Sabrina

    Hi Martin, pardon if I sound like a complete newbie, well because I am, but I clicked 'use this template' and google docs said 'We're sorry, your spreadsheet cannot be copied at this time.' I'm an undergrad planning on doing my thesis on a content analysis of political tweets. I could really use your help in figuring out how to archive the hashtag tweets. Thanks

    Reply
    1. Post author

      @Sabrina - not a silly question at all but a problem with deploying templates. I've updated the instructions and provided a direct link.

      A word of note Google Spreadsheets have a limit of 400,000 cells and Twitter limits API calls to 350/hr and each call returns a maximum of 100 tweets so there are limits to how much you can get out - I get a lot of research students using this technique trying to get large volumes of data and it doesn't work. If you need large amounts of data you might want to team up with http://tweetminster.co.uk/
      Martin

      Reply
  8. Pingback:

  9. Pingback:

  10. Pingback:

  11. Pingback:

  12. Pingback:

  13. Kay

    Hi Martin,

    I have successfully begun to archive tweets per your google spreadsheet method (and also exported a bunch of twapperkeeper archives - thanks very much for your work on this!!), and wanted to use the TAGS explorer to make a visualisations of the tweets. However, when I paste in the spreadsheet key and click 'get sheet names', it looks like it's working but never comes up with any sheet names. I've tried both using the spreadsheet key and the whole URL with no success. It's probably something super simple but any advice you can provide would be appreciated!
    Thanks very much

    Reply
    1. Post author

      Hi Kay - for TAGSExplorer to work you need to publish the spreadsheet. To do this in the spreadsheet select File > Publish to the web and publish all sheets. If its still not working let me know
      Thanks
      Martin

      Reply
      1. Kay

        Thanks Martin,

        It's now working perfectly! Thanks very much for putting this together. Is there a limit on how much the tagsexplorer can process? (I have a twapperkeeper export of 15,000 tweets I'd like to use)

        Reply
  14. Pingback:

  15. Pingback:

  16. Hi Martin,

    Thanks for making this available. I've setup the spreadsheet by following your directions, but when I select "Run Now!" a new page with the archive does not open. I have tried the Test Collection option and it does find a tweet with my search term in it, so I'm not sure what is happening. You can see my version of the spreadsheet at:

    https://docs.google.com/spreadsheet/ccc?key=0AnD-DvzFX0tddEY5V09YMlV2TVl5bUQ0M2ZVd1dIU2c

    (By the way, I don't get a Twitter menu category, but one for TAGS. Not sure if this matters as the menu options seem to be the same.)

    Thanks for your help!

    Bill

    Reply
  17. Thanks, Martin! I totally didn't see the Archive sheet tab at the bottom.

    Looking forward to using this in my courses this semester. So grateful to have it now that Twapperkeeper is totally gone.

    Thanks, again.

    Bill

    Reply
  18. Pingback:

  19. Pingback:

  20. Pingback:

  21. Pete

    Hi Martin,

    really like the tools you've created here. However, i don't appear to have the Twitter menu in the tags v3.1 sheet, so i'm unable to authenticate with Twitter... can you advise.

    Cheers

    Reply
  22. DudeTV

    Hi Martin,

    I'm getting this error when I try to authenticate:

    "Unexpected error: (line 1085)"

    Here's line 1080 to 1090 which is the end:

    var requestData = {
    "method": "GET",
    "oAuthServiceName": "twitter",
    "oAuthUseToken": "always"
    };
    var result = UrlFetchApp.fetch(
    "http://api.twitter.com/1/account/verify_credentials.json",
    requestData);
    var o = Utilities.jsonParse(result.getContentText());
    ScriptProperties.setProperty("STORED_SCREEN_NAME", o.screen_name);
    }

    Suggestions?

    Best

    Reply
    1. Post author

      something wrong with twitter customer key/secret. I noticed the other day that copy and pasting the customer secret with Firefox on the Mac added a tab to the beginning of the field

      Reply
  23. Hi Martin,
    This is a great script, and I got it to work well in your TagExplorer. However, I would also like to export the sheet so I can use it in Gephi. Can you tell me where I can do this within the GoogleDocs template? Instead of a 'Nodes' tab, as shown in your advanced video, my TAGS document seems to have created two extra tabs at the bottom, which aren't populating 'TMP' and 'TMP_NODES'.

    Any idea what I'm doing wrong, and how to export the data so I can use it in Gephi?

    Here is my current data: https://docs.google.com/spreadsheet/pub?hl=en_GB&hl=en_GB&key=0AvoF_1KZEYbddDVsVEU0aW51SUxHY1RwMk90Q0h4TGc&output=html

    Reply
      1. Hi Martin

        Thanks - the only problem with my edges sheet, is that it doesn't contain the information I would need to get my gephi visualisation looking like your TAGExplorer - it just seems ot have some Twitter ID names, rather than a connected network. Is there a way I can get the info from the Archives sheet to import correctly as a csv in Gephi so that it looks like your TAGExplorer network? I have tried importing the archives as a nodes table, but there are no connections between the nodes in the overview.

        Reply
          1. Thanks for this Martin. I get so far with your post, but then realise I can't use NodeXL, as I have a Mac - I guess there's no alternative? Also, which method do you advise for opening a CSV in Gephi - File>Open or Import as CSV?

          2. Yes, I got the CSV bit - I am just getting Java.Lang.RuntimeException error once I try to load the CSV into Gephi. No worries, I'll figure things out eventually.

          3. Thanks Martin - worked really well - just had a few issues with Google Docs crashing a few times, though that's understandable.

  24. Pingback:

  25. Pingback:

  26. BillMew

    I keep getting "Oops" and "Authorization is required to perform that action" whenever I try test collection or run now. I have been through every step of the Twitter API Authentication. Is it browser dependent? or is there something else I have missed?

    Reply
    1. Post author

      Hi Bill - the spreadsheet should work on all browsers. Sounds like the authenticate script didn't complete. When you ran it did it open a popup to twitter for you to log in and approve access?
      Martin

      Reply
      1. BillMew

        I figured it out. I have more than one Twitter ID and my gmail email ID is used to login to the lesser used the Twitter ID rather than the one that I normally use. You may want to add a note in your instructions that you need to create the Twitter App using the Twitter ID that corresponds to the gmail account that you are using for the Google docs - it would save dummies like me going wrong.

        At the same time do you have a link with instructions on how to manipulate the visualization. I cannot find any button to allow me to replay tweets, zoom in or zoom out or even scroll up down or side to side when some of the nodes are off screen.

        Thanks for you help as ever. Great App by the way

        Reply
        1. BillMew

          It would really help if you can provide a link with instructions on how to manipulate the visualization. I cannot find any button to allow me to replay tweets, and when viewing I'd like to be able to zoom in or zoom out or even scroll up down or side to side when some of the nodes are off screen.

          Reply
          1. Post author

            Thanks for the suggestion. Not a huge amount of control to write about (mousescroll wheel can move nodes in and out, you can click and drag individual nodes around) Need to experiment with new tools to give the user more control
            Martin

  27. Pingback:

  28. Is it possible to change the time zone? We're in New Zealand (I'm archiving conference tweets), but all the times are US times :(

    Also, I don't appear to be able to TAGSEplorer working. Having put in the URL and pressed 'get names', it just gives me the 'i'm thinking' wheel of doom. Been doing so for a coupla hours now, despite refreshes, reducing to hourly the frequency with which tweets were collected, etc...

    Reply
    1. Post author

      Hi Re timezone the raw data from Twitter comes back in GMT, currently there is no feature to modify the raw data.
      Re TAGSExplorer make sure that you have File > Published to the web the spreadsheet first

      Martin

      Reply
  29. Pingback:

  30. Jeremy D.

    This is an amazing tool - I cannot wait to share this with all of my analytic/techie friends & colleagues! Thanks & fine work Martin.

    Reply
  31. Pingback:

  32. Matteo

    Martin, it seems that I have a problem with the function authenticate. It gives me an unexpected error at line 1085. Is it a common issue?

    Thanks!

    Reply
  33. Pingback:

  34. Pingback:

  35. OH thank you so much for this! I have one goof error right now, but I'm sure it's me. [Should get a new ref before getting an old one (line 1012) and one for line 105 another time]

    I have my archive and that is so what I needed. Thanks a million.

    Reply
    1. Post author

      For those getting 'get a new ref before getting an old one' errors Google changed the way the code is handled. I'm updated the spreadsheet to fix this. Old copies should continue working if already authenticated. For new archives you should take a fresh copy of the spreadsheet from the link in the most.

      Thanks for bringing this to my attention - sorry for the delay in fixing
      Martin

      Reply
  36. Pingback:

  37. Pingback:

  38. Pingback:

  39. Pingback:

  40. Has someone tried to use google fusion tables to have a map in real time? I imported the archive into fusion tables, set the field of geo location and it worked, but it is a copy and not a feed.
    Any suggestion?

    Reply
  41. Martin,
    Thanks so much - this is the best tweet-archiving tool I have managed to find! You've saved my research project! Will be recommending this one for sure!

    Regards,
    Jenni

    Reply
  42. Pingback:

  43. Pingback:

  44. Pingback:

  45. Pingback:

  46. pooja

    sir, if I have to specify multiple tags do I need to obtain multiple keys?
    secondly is there a way this tool can help me to obtain only retweets/ replies to a a particular tweet containing hashtag

    Reply
  47. pooja

    sir,
    the tool is perhaps the first integrated one. i have one doubt whether we can specify multiple tags, secondly can we have multiple sheets archived corresponding to multiple tags data. if yes where can we see them.

    Reply
  48. Hi Martin, this is fantastic and I've used it a bunch of times for events. Thanks very much for sharing a brilliant tool.

    I'm struggling to make complex search terms work. Could you possibly share some examples?

    In point 3 of the instructions you say "You can control the type of data returned by changing the column heading" - sorry for me being slow, but I can't see where to change the column heading. Can you advise please? Thanks very much for your help.

    Reply
  49. Pingback:

  50. Pingback:

  51. Pingback:

  52. Al

    Hi,

    Thanks for making this guide!

    I started making a collection but it seems that the script soon fills the spreadsheet, exceeding maximum cell count. At the same time, data in 'Archive' seems to get lost (or at least FX is not displaying anything in the window).

    What would you recommend in terms of long-term archiving, should I just start creating manually copies of the original file for real archives and manually clear out stuff from the 'Archive' sheet?

    Al

    Reply
      1. Post author

        Make a copy of your original or download archive and then clear all of the archive sheet except the header and 1st row after that. Then just keep on archiving. (I find it best to remerge the archives in Excel)

        Reply
  53. Pingback:

  54. Pingback:

  55. Pingback:

  56. Pingback:

  57. This is an awesome free (!) tool! Thanx a lot for sharing and your efforts.
    If I may ask one question, it would be the following: Is it possible to change the metrics of the horizontal axe in the "Tweet volume over time" grafic? It displays 41154, 41158,... and so forth. Would be nice if here could be a time scale?

    Thanks again!
    Cheers,
    Sebastian

    Reply
  58. Pingback:

  59. MC Lingold

    Hi Martin! Thanks for this amazing resource! I'm having a bit of trouble in the final stage. When I select Run Now! I get an error message reading: "Apps Script Line 352 unexpected Error: unknown" Any ideas? I'm using Firefox in Mac Lion. Thank you again. Best, MCL

    Reply
  60. Pingback:

  61. Pingback:

  62. Pingback:

  63. Pingback:

  64. Pingback:

  65. Pingback:

  66. Al

    Thanks Martin for the solution on how to work with the data.

    However, I'm having some new issues with the script (TAGS v3.1). The main issue is that once it get's too crowded the whole document becomes very unresponsive.

    This get's worse because I've lost the TAGS menu and the control over the script - but it still keeps on running! I would like to stop it, but I haven't figure out how to do that because I cant' access the script editor ATM (while the script is running?), all related items in the Tools menu are grayed out.

    Any suggestions, just kill the spreadsheet?

    Reply
  67. Martin,

    A million thanks to you for this tool. As a newbie to coding, I love how simple you've made this - my students and I are rather in awe of you. We have a question, though, if you have time to address it: We'd like to archive the tweets from our class sessions going back to September. Is such a thing possible? The collection of tweets seems to stop at just a few days old. We'd be grateful if you could point us to a potential work-around to grab more, older tweets.

    Best,
    Amanda
    Duke University

    Reply
  68. Rob Alexander

    Hi Martin,

    Just trying to get to grips with the script for a project that I'm working on and can't seem to get it to authenticate when I do the Script Manager > run 'authenticate' step - it just seems to get stuck saying 'running script...', just wondering if you could think of any ideas as to why/any solutions?

    Thanks for sharing your work - looks like this is going to be a really useful tool!

    Cheers,
    Rob

    Reply
  69. Pingback:

  70. Pingback:

  71. Pingback:

  72. Pingback:

  73. Pingback:

  74. Pingback:

  75. Pingback:

  76. Pingback:

  77. Pingback:

  78. Pingback:

  79. Pingback:

  80. Karmi Nguyen

    Hi Martin,

    I want to reuse your code in my project. Would you permit that? But I know you are free not to. Please let me know how much I can reuse your code.

    Regards,

    Reply
      1. Karmi Nguyen

        Dear Martin,

        I'm a loyal reader of your blog, and thank you for contributing your knowledge in such an amazing way (at least for me, who really admire your works and your skills). Thanks to you, TAGS and TAGSExplorer become part of my work, and following your Scoop on Google Scripts help me to discover interesting ideas.

        Well, today I'm writing to ask for your help. I want to build a similar tool like TAGSExplorer for my social monitoring application. I know it's a bit sensitive, and if you accept or not, I can understand and still be your loyal follower.
        FYI, at this time, I have built a web app that uses social APIs to fetch social data (FB, TW, G+, Utube, News sites) about a specific keyword into local files. Then, I use GCharts to do some basic reports. I have successfully integrated some of your code in TAGSExplorer to visualize the Tweet Archive. But the problem is, when I change data archive without reloading the page (pass json data into saveAndDraw() in tags.js), the old nodes won't change. I have tried to figure it out myself for 3 days but still no luck.
        ~
        Karmi Nguyen.

        P/s: Sorry for my English, I'm a Vietnamese.

        Reply
          1. Karmi Nguyen

            Hi Martin,

            Thank you for your guide, I have found some sources helped me tame it the way I wanted :) It's really amazing, and I think that you would be a great teacher. You really inspire and provide me with food for thought.

            Again, thank you very much!
            ~
            Karmi.

  81. Pingback:

  82. Pingback:

  83. Sanjay Mahtani

    Hi Martin,

    I'm creating a visualisation to capture the conversations around a Q&A session tomorrow. Is there a way of collecting tweets during a specific time period, ie. starting 12:45 BST and ending 14:00 BST?

    I selected the collectTweets trigger, making it time-driven at a specific date and time (2013-04-18 12:45), but the script starts collecting tweets immediately.

    In addition, is there a way to end the collection at a certain time?

    Thanks in advance,
    Sanjay

    Reply
  84. Pingback:

  85. Pingback:

  86. Pingback:

  87. Pingback:

  88. Pingback:

  89. Pingback:

  90. Pingback:

  91. Pingback:

  92. Pingback:

  93. Pingback:

  94. Pingback:

  95. Pingback:

  96. Pingback:

  97. Pingback:

  98. Pingback:

  99. Pingback:

  100. Pingback:

  101. Pingback:

  102. Pingback:

  103. Gebaly

    how can we reset the data in a sheet? by reset i mean: return to the original state without resetting the connection.

    Reply
      1. Gebaly

        that is what i did, but i found that it collected empty row after that.
        i had a sheet collecting a string. it reached the maximum cells per sheet as google spreadsheets defined it. so i downloaded a copy to my computer, then emptied the sheet by deleting all rows except the first one. what happened is that the system kept adding rows to the sheet, but they were empty. so i thought that there is a better way to do this.

        Reply
          1. Gebaly

            anyway, i would like to thank you for this great script. one of the best that i have found and used. i would also like to recommend that you move the script to the new Google spreadsheets. this will delay all the of the cell and sheets limitations. thank you again. wonderful work.

  104. Pingback:

  105. Pingback:

  106. Pingback:

  107. Pingback:

  108. Hello Martin - I will 'out' myself right away as a non-techie, but I'm looking for a tool that will allow me to archive tweet associated with a hashtag that is no longer in use. Most of the tweets would be from a couple years ago. Is that possible with your tool?

    Much thanks,
    Cecile Farnum

    Reply
    1. Post author

      Unfortunately as far as I'm aware this data is currently only available through Twitter official data resellers like Datasift and Topsy. The cost per tweet is low but I've done no work with their systems so don't know how easy the data is to get at.

      Reply
  109. Tom

    Hello Martin, I would like to collect the tweets connected to the hashtag #freethearctic30 which was created in september 2013. I need the tweets between september 2013 and december 2013. I'm doing this as a research for my final thesis, I'm an Italian student. I want to see the online activity around this hashtag. Do you think it will work using this script? I tried to modify the trigger, in order to collect the data since last semptember, but it doesn't work. Can you help me?
    thanks
    tom

    Reply
  110. Pingback:

  111. Luis

    Hi Martin. I tried collecting tweets for the #stoptheNSA hashtag using TAGS but it doesn't seem to work. It collects no tweets when I use either the run now command or the hourly collection command. In the dashboard, on the first right-hand box, I see an error in red that says "cannot read property 'title' of undefined." Is there something I'm missing in the setup, execution, etc.? Thanks!

    Reply
      1. Luis

        Actually I figured out what I was doing wrong after all...I think I just hadn't put the Google Spreadsheets URL as the callback link when I created my Twitter app. Thanks!

        Reply
  112. Pingback:

  113. wonderful script, works great except that, after publishing, I'm not able to grab the permanent link to the visualisation, when i click "get link" the visualisation tool keeps reloading data.

    Reply
  114. Pingback:

  115. Pingback:

  116. Pingback:

  117. Pingback:

  118. I am trying to capture all hashtag mentions for a major event we had. I have done everything, I just can't get it authenticate? I have an email that says it is not authenticating with Twitter. How do I verify that my credentials are correct and the connection is successfully being made before I try and verify this script? Thank you!

    Reply
  119. Pingback:

Leave a Reply