Find me on Twitter at @brelson
I live in London and work in design & technology – UX, IA, digital strategy, consulting, that sort of thing. If you know what these terms mean then you’ve probably got a good sense of what I do already. If you’ve never come across them in your life, count yourself lucky.
One thing that I do a lot of at work is to draw diagrams and, over the years, I’ve developed a tendency to start drawing diagrams whenever thinking about or explaining things. This could get awkward if I ever have to explain the birds & bees to my future offspring, although I’m sure they’ll be able to find lots of helpful diagrams on the internet if need be.
On this site I try to make diagrams that my employers, design firm Tobias & Tobias, would probably never pay me to make – diagrams that most people would never want to see. So every now and again you’ll see posts that try to explain life’s boring, quotidian mysteries in diagrammatic form. I also write about stuff to do with my work like QR codes and mobile technology and user experience design but you don’t have to read that stuff if you don’t want to.
If you’re new to this blog some posts that people have liked in the past are Want a seat on the Overground during rush hour? Then prepare for war (a Labour MP posted a link to that on Twitter which is as close to my fifteen minutes as I’m ever likely to get). There’s also Reimagining The Moral Maze as a computer game, an unsolicited pitch to Radio 4 which went nowhere, presumably because no-one would want to play as Melanie Phillips.
Then there’s a bunch of more techie stuff, including a post that explains how to use Google Spreadsheets to extract data from Twitter. Does that even make sense to you? If it doesn’t, give yourself another pat on the back – you’re better off not knowing.
Hi,
Have read your excellent article on Using Google Spreadsheets to extract Twitter data.
Question I have is where do you get the twitter categories from, eg tweet_from_user, items author etc.
I am looking to insert a location category ie where the tweet user is located.
Grateful for your reply.
Adrian
Thanks for your feedback about the article, I’m glad you found it useful. The Twitter categories come from the data returned by the Twitter API – when you request search results, Twitter gives you a bit of data about each result including the name of the author. More info about the Twitter API can be found here: http://apiwiki.twitter.com/Twitter-Search-API-Method:-search
I think it would be possible to add the user’s location, but a bit more work with Google Spreadsheets would be needed because that information doesn’t come out of the “search” API method. You’d need to run a second query for each username, which could be a bit difficult, but I will look into it!
Hi,
Love your spreadsheet to do the extract of twitter. On the last tab, the one that does the more pages, it is stopping at 100 rows as well. Do you have a suggestion how to get the full list?
Hey Garen,
Glad you find the spreadsheet useful! I’ve updated it so that on the “more pages” tab you’ll see another 20 rows appearing.
You can add more rows basically by making the spreadsheet request more pages from Twitter. If you look at the formula in, say, cell B102, you’ll see that the query string has a page number in it (“page=11”). If you copy this formula and paste it further down the spreadsheet with the page number incremented (“page=12”), you should see even more results appearing. You’ll need to do the same for the formulas in each of the columns to make sure you bring in timestamp, username and tweet text too. Let me know if it works!
brilliant stuff