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

194

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)


194 Comments




  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


    1. @Matt hmm might be a temporary service disruption issue or maybe a problem with the search term.
      Martin


    2. 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!!


      1. If the event was over 7 days ago then the twitter search won’t returned anything from then (one of it’s limitations). Might that be your problem?


        1. thats it, thanks so much! I thought that may be the case, I tried another more recent search and it was lovely! thanks so much!


  4. 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


    1. 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


  5. i made a new copy of the template & i still have the same error .
    is there a way to get an old template like 2.4.2 ?


      1. i’m sorry :S , but i’m still having the same problem with the script editor .
        another problem is when i open http://hawksey.info/tagsexplorer and press ” get sheet names ” nothing open .


  6. I have No. results set to 1500, but I only get the last 50 tweets. Is it possible to get *all* tweets from my event yesterday?


    1. @jennifer hmm not sure why it would only get 50. What’s the search term you are trying?
      Martin


    1. 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


  8. 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


    1. @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


  9. I don’t have the Twitter Tool either. When I click on the Get button I get this error: Script function onOpen could not be found

    I tried to recopy to no avail.


  10. The same problem that Dan and Joakim. When I edit the script appear only this code:
    function myFunction() {

    }
    Thanks Martin


  11. 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 ;)


  13. 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


    1. @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







  19. 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


    1. 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


      1. 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)




  22. 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


  23. 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



    1. Hi Bill – looks like you need to File > Publish to the web then it should work
      Martin




  28. 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


  29. 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


    1. 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


  30. Was using Firefox on a PC. Just tried IE and had the same error.


  31. 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


    1. Hi Sam – if you download the edges sheet as a csv you should be able to open it in gephi


      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.


          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?


          3. 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.


          5. Thanks Martin – worked really well – just had a few issues with Google Docs crashing a few times, though that’s understandable.


  32. I successfully created a visualization but am having trouble seeing the whole picture. The “center” of the visualization is even off center.




  35. 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?


    1. 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


      1. 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


        1. 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.


          1. 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



  37. 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…


    1. 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



  39. You. Absolutely. Rule. Even I was able to go through these instructions. This must have taken you ages, and you are giving it away for free! Can I ask you what your motivation is?


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



  42. 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!




  45. 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.


    1. 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






  50. 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?


  51. 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






  56. 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


  57. 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.


  58. can we import the spreadsheet to nodexl.if yes, how??


  59. 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.





  63. 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


    1. Hello Martin, I have the same problem as Al here. I, too, would welcome a recommendation.


      1. 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)





  67. Hi Martin,

    Thank you for sharing this wonderful tool. And for making this guide.



  69. 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


    1. Also, is there a way to change the collecting tweets timezone GMT to different, like EST?


      1. In this version no way without going into the code -working on this for the next version


    2. On the dashboard sheet unhide columns after H and select any column with 4 thousand and from the format menu select a date format.



  72. 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








  79. 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?


  80. Cheers Martin, thanks very very much


  81. 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


    1. (you just answered this query via Twitter. Thank you!)


  82. 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


    1. Just realised I was doing wrong – script editor, not manager! Apologies! :-)



  84. Wow! I stumbled over your blog and I’m amazed! Fantastic work!

    One thing: After pasting the spreadsheet URL in http://hawksey.info/tagsexplorer I always receive this error: “Error in query: Access denied Access denied”

    What could I have done wrong?


    1. Hi CH, In the spreadsheet just make sure you have File > Published to the web and then it should work
      Thanks,
      Martin


  85. Thanks a lot! I tried to use the share button in the upper right corner, which obviousliy isn’t enough.












  96. 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,


    1. Hi Karmi – a lot of the code is already open source. If there is anything beyond this (or different license required) get in touch
      Martin


      1. 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.


        1. Hi Karmi – Sounds like a similar story to when I first created TAGSExlorer. I spent days trying to get d3js to work the way I wanted, desperately changing and testing things to get it to work. If it helps TAGSExplorer was built on the Twitter Smash example. My advice if you are still having problems is to post a question on stackoverflow
          Martin


          1. 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.




  99. 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





















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


    1. I usually just delete all the rows except the heading row in the ‘Archive’ sheet


      1. 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.


        1. Sounds like you are just deleting the row values. Highlighting the rows and then Edit > Delete rows removes the rows making it easier to navigate


          1. 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.






  124. 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


    1. 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.


  125. 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


    1. Hi Tom – the data from twitter is limited to the last 7 days. You can browser further back using a service like Topsy but there is no free data export options



  127. 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!


    1. That’s the NSA for you ;). When you open the Tools > Script editor and Run > authenticateTwitter what happens?


      1. 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!



  129. 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.






  134. 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!





Comments are closed.