To support my research in Twitter community visualisation I’ve updated my Twitter Archiving Google Spreadsheet (TAGS) [formerly called twitteralytics – I pre-emptively changed the name to play nice with Twitter ToS].
This new version has some coding improvements and new features including a dashboard summary and advanced tools for getting user profile information and friend/follower relationships for social network analysis.
You can get a copy by selecting one of the links below [make sure you are looked in to your Google account first]:
*** Twitter Archive Google Spreadsheet – TAGS v3.0 ***
[Please rate it in the Template Gallery]
[If the first link doesn't work try making a copy from the Template Gallery
or Opening this Spreadsheet and File > Make a copy]
Basic setup/use
- Open the TAGS Google Spreadsheet and copy
- On the Readme/Settings sheetenter the following settings (starting in cell B9):
- Who are you = any web address that identifies you or your event
- Search term = what you are looking for eg #cetis12
- Period = default
- No. results = 1500 (this is the maximum twitter allows but without authenticated access you might get less. See the Advanced setup for info on configuration)
- Continuous/paged = continuous
- To configure the spreadsheet to automatically update select Tools > Script Editor … and then in the Script Editor window select Triggers > Current script’s triggers… and Add a new trigger. Select to run ‘collectTweets’ as a ‘Time-driven’ choosing a time period that suits your search (For unauthenticated access I collect 1500 tweets every hour). Click ‘Save’
- The collection can manually be trigger by TAGS > Run Now! (Results appear on the ‘Archive’ Sheet).
Advanced setup/use
- Open the TAGS Google Spreadsheet and make a copy
- Register for an API key with Twitter at http://dev.twitter.com/apps/new. In the form these are the important bits:
- Application Website = anything you like
- Application Type = Browser
- Callback URL = https://spreadsheets.google.com/macros
- Default Access type = Read-only
- Once finished filling in the form and accepting Twitter’s terms and conditions you’ll see a summary page which includes a Consumer Key and Consumer Secret
- Back in the Google Spreadsheet select Twitter > API Authentication (you’ll need to select this option twice, the first time to authorise read/write access to the spreadsheet). Paste in your Consumer Key and Secret from the previous step and click ‘Save’ (if the Twitter menu is not visible click on the blue button to show it)
- From the spreadsheet select Tools > Script Editor … and then Run > authenticate and Authorize the script with Twitter using your Twitter account
- While still in the Script Editor window select Triggers > Current script’s triggers… and Add a new trigger. Select to run ‘collectTweets’ as a ‘Time-driven’ choosing a time period that suits your search (I usually collect 1500 tweets once a day, but increase to hourly during busy periods eg during a conference). Click ‘Save’
- Now close the Script Editor window. Back in the main spreadsheet on the Readme/Settings sheetenter the following settings (starting in cell B9):
- Who are you = any web address that identifies you or your event
- Search term = what you are looking for eg #cetis12
- Period = default
- No. results = 1500 (this is the maximum twitter allows)
- Continuous/paged = continuous
- Click TAGS > Run Now! to check you are collecting results into a ‘Archive’ sheet
- To allow the results to be visualised from the spreadsheet select File > Publish to the web…You can choose to Publish All sheets or just the Archive sheet. Make sure Automatically republish when changes are made is ticked and click Start publishing
Creating a public interactive visualisation of the archived conversation
- Copy the url of the published spreadsheet
- Visit http://hawksey.info/tagsexplorer and paste your spreadsheet url in the box, then click‘get sheet names’
- When it loads the sheet names leave it on the default ‘Archive’ and click ‘go’
- You now have a visualisation of your spreadsheet archive (click on nodes to delve deeper)
- 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)
Quick way to display archive community (Links) data
- Run TAGS Advanced menu options (1-3)
- Copy the url of the published spreadsheet
- Visit http://hawksey.info/edgesexplorer and paste your spreadsheet url in the box, then click‘get sheet names’
- When it loads the sheet names leave it on the default ‘Links’ and click ‘go’
Loading...
Really nice Martin – although I seem to be getting errors when I start to use advanced operators (e.g. near:”London, UK” within:1000mi) – any thoughts on why this might be?
@Tom ah the Twitter Search API reads location using a geoencode parameter rather than the search term [ref] A quick fix would be to add the geoencode parameter in the getTweets function
Cheers – will give it a try! Out of interest what does TMP stand for?
This is absolutely amazing! Thanks so much. I’m going build daily reporting and analysis based on this for some projects I’m working on. THNAKS!!!!
When I try to use the template, I get a google docs error.
Have you taken the template down?
Template should still be there – try the rate in gallery link and make a copy from there
Is there a way to extract geocoordinates with the tweets? My copy of TAGS has a geo_coordinates column that has come out largely empty.
If a tweet has Geo data it is recorded, problem is that most people don’t include location data which is why the column is mostly empty.
This was working GREAT until today whereby the test runs and will return data, but when it actually runs it returns a blank sheet. What is going on and how can I fix it?
thanks for this AMAZING template!
Ben…
Ok after reading more It seems i might be getting rate limited by twitter. So i did the API authentication and now when I run it i get an error of:
Oops
Authorisation is required to perform that action.
Ben…
PS. Authorisation should be Authorization
Hi Ben – the oops authorisation (British spelling ;) message can mean a couple of things: callback url was entered incorrectly when registering for an API on Twitter, customer secret/key have a typo, and very rarely Google doesn’t record the returned authentication token from twitter (in this scenario try running the authenticate script again, if that doesn’t work you need a fresh copy of the spreadsheet, but you can render the existing API details)
Hope that helps,
Martin
I had no idea there was a British spelling of that word. how funny.
Ok I got a new copy of the spreadsheet and followed all the instructions. I no longer get the error but I still get a blank page as the results. the test results show info, but no data is returned on the page when I run it.
any ideas? Thanks
Ben
Martin,I have a problem with advanced functions. When I run “Get Followers and Relationships” and “Replace users IDs with Names” everything seems OK except that there’s no header in Nodes spreadsheet, but there’s data. But I get blank Nodes sheet when I run “Fill Twitter user information”.
Of course I autorize script with my Consumer and Consumer secret key.Also in Dashboard there’s no Top Twitters chart.
Am I do something wrong?
Stas
There’s something wrong with copying templates in Google Spreadsheets right now. As the column headings are used to identify where data is written, as you have no headings that’s why it’s blanking the sheet. TAGSv3.1 (which should fix it) is here http://mashe.hawksey.info/2012/01/tags-r/ – should be officially releasing later today
Martin
HI Martin – I’m Using Tags 3.1. and having two issues. First, I’m trying to add a column (To_User) in the TMP and while I can add it, when I run and return results, the new column/data does not appear. Second (to try to get around that) I tried to use the advance tags tab option to “Replace User Ids with names” and I get the following error message: The coordinates or dimensions of the range are invalid. (line 527). Any ideas?
Hi Charlton – the adding column to the TMP sheet only works if you are using paged results (this is a historic feature) Most people just use continuous which writes the tweets to the Archive sheet. So if you are using continuous collections just add ‘to_user’ to the Archive sheet and it should work. The replace id error message probably appeared if step 1 didn’t finish successfully, Edges should have a pair listing of ids and Vertices should have a list of id and usernames before trying step 2.
Hope this helps (as you see from my blog i’m interested in Twitter analysis and it’s always nice to know what other people are getting up to ;)
Martin
Ok I got 3.1 and its now collecting tweets in the archive, but not when its set to paged.
Also I have the period set for yesterday, but it pulled today’s tweets. We shall see what it pulls tonight when its set to collect them.
Any more helpful advice?
Ben…
Love this spreadsheet…
Hi Ben – sounds like paged results is broken. It’s a feature from v1 that I don’t use anymore which is why it’s neglected – will have a look if I get a chance but low priority for me. Re yesterday tweets. Twitter API works off GMT so my yesterday might not be the same as your yesterday. Also can be effected by Script Editor project properties (there’s a timezone setting)
Hello-
I am having the same problem that @Ben had. I was successfully using TAGSV3.1 yesterday, now it is giving me the Oops Authorization error. I have opened a new spreadsheet, API authenticated it, and tried a new search, but still the same error. Any thoughts? Thank you!
In the Twitter Dev My application section is your api key still healthy? I’ve had a key suspended in the past
Hi Martin,
TAGS V3.1 is not working good. It is not pulling 1500 tweets.Please advise
Thanks
Sarath
Hi,
Thanks! This spreadsheet is going to be a lifesaver! It is working quite well, however, I am getting this error:
“The coordinates or dimensions of the range are invalid. (line 527)”
when I try to run the script for replaceIDsWithNames
What am I doing wrong? Also, is it as simple as adding a column for that variable under the Archive page?
Hi Martin,
I do live blogging and have been using your google docs spreadsheet TAGSV3 spreadsheet to pick up hashtagged tweets – it works very well. For the first time I’m actively using the TAGSExplorer visualiser as well to visualise the conversations that happen at an event on Wednesday.
I just wanted to thank you for sharing these tools which are really helping communicate the value of twitter to non-twitter users in the construction industry (which is my area of operation). Of course I’ve been telling loads of people about how great they are! Anything I can help you with, just give me a shout.
Su
Hi Su – many thanks for the kind words. Whilst I gave up working in the construction industry over a decade ago it still has a fond place in my heart so I’m glad you find the tool useful with what you do. Once you have the data in a spreadsheet there are many more things you can do with it. Stay tuned for updates ;)
Thanks again
Martin
Oh yes, I’m subscribed!
Very, very cool stuff. Thanks.
Martin,
I hosted a chat last night on Twitter and I was panicking because I couldn’t find a good archive tool to handle the volume of tweets I was expecting on a Mac (Archivist works only on Windows). This template has saved my life and gives so much more information than I was looking for.
Thank you so much for putting this together. It is a fantastic tool and I’m already excited about using it more often in the future.
-Brian
Hi Brian, Glad the spreadsheet was useful (and as you’ll see from my latest post I’m grateful for the traffic you sent my way ;)
Martin
Martin,
Let me start by saying this is an outstanding template to use for Twitter archiving.
I have a question that has been brought to my attention from our social media group. After we gather tweets during a Twitter chat, we use this information to disseminate to various groups, but the only problem that we are having with any Twitter tool for analytics is the timezone issue. Because alot of the chats that we do are worldwide, this is an important aspect when it comes to graphing the results to know that it is differentiating and taking the timezones into account.
Is there an easier way to do this in order to allow us to graph those results without having to go through thousands of tweets and doing this conversion by hand.
Thanks
Kevin
Hi Kevin, not entirely sure what your use case is. Do you need to change all the dates in the archive, or does it need to change based on who is looking at the spreadsheet?
Martin
When it graph’s the results, what about someone that tweeted in the chat at 10am EST and someone tweets at 7am PST. How does that graph? Does it put both of those tweets at the 10am EST or separate and then splits them up based on the time 0900 and 1000 and does not take the time zones into consideration.
Hi Kevin – results from the Twitter API are all stamped with GMT date/times which is used in the dashboard charts so in essence there is no timezone consideration
Hope that clarifies things,
Martin
Hi Martin
Thanks for the work with this – I’ve been running a sheet for a while, but the one I’ve just set up keeps giving me Apps script: Line 329 Unexpected error: Exception
I’ve been through and checked all the settings.
Thanks in advance
Might be temporary server problems with Google or Twitter. I would try a fresh copy and see if it’s still an issue (also check that if you are using Twitter API authentication that your app hasn’t been suspended)
Martin
Hi Martin-
This sheet is awesome. Is there a way to collect tweets using a combined feed instead outside of the twitter search? Ideally, I’d like to combine search feeds from yahoo pipes all in one, set a trigger for every 1 hour, and archive as many results from there as possible on a continuous basis. I mention Yahoo pipes because I’m trying to mash all the search feeds together and filter out keywords I don’t want before its outputted in the spreadsheet and archived.
I find twitter feeds from yahoo pipes very unreliable. If your search isn’t too complex you can use operators in the search term field like ‘keyword1 OR keyword2 OR keyword3 -notthisone -notthisone2′. If you still want to use Yahoo pipes not aware of anything out of the box but I’d look at getting the pipe as JSON and handling with Apps Script
Martin
Hi Martin,
Is it possible to conduct several queries with the same template, or should I use 1 query per template?
I tried one query and several minutes later another one. It seemed that the archive page does not refresh with the new results, but keeps on generating results from the previous query.
Great tool though.
your query can include OR operators (e.g. searchforA OR searchforB) which should work. Manually switching between different search terms won’t work (or you can just have multiple spreadsheets – I think I have over 50 running)
Hi Martin,
I am trying to use the TAGS advanced options and I get this error: “TypeError: Cannot call method “getRange” of undefined. (line 486)”
line 486 on the script is: var REFERER = doc.getSheetByName(“Readme/Settings”).sumSheet.getRange(B80).getValue();
Could you tell what is going wrong?
Not sure why it’s calling cell B80. Original code in the script calls cell B9 for this (the data is used to play nice with the Twitter api)
Thanks for this script!
It worked nice the first day, but now the timed execution returns an error:
“TypeError: Cannot call method “getSheetByName” of null. (línia 177)”
If I launch it by hand it works fine. At first I tought it was something related with the table name, but it isn’t. Don’t know what’s wrong.
Hi Raf – I occasionally get timeouts but usually work fine on the next trigger. I’d try removing the script trigger and then adding it back again (making sure your using collectTweets)
Thanks Martin, that did indeed fix the issue.
Impressive piece of code – looks like it will be very useful. I tried to setup the API authentication – got my key and entered it, but when I try to run I get:
Opps – should get a new ref before getting an old one (line 1012)
Also doesn’t seem any way to get back to the API authentication menu config now to remove the key
Any ideas? Running TAGS v3.1
Best wishes,
Tim
Hi Martin,
I am also having the same issue with API authentification “should get a new ref before getting an old one”.
Can you help me?
Thanks a lot for this impressive work.
Best
Sam
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