What we think

Archives

A hack for searching for tweets by date, with help from Google Spreadsheets

Clive was the first round these parts to notice that the beloved Twitter Search (formerly known as search.twitter.com) appears to have been revamped, with certain search options apparently retired, notably the ability to specify a date range when searching. *sad face*

You can see the search functions as they once were here, and the current version here, now devoid of all date-based options.

Fortunately, all is not lost, since the search API still allows you to specify a date range via the (undocumented) since and until parameters.

However, for some unfathomable reason (unfathomable, at least, to me), some folk appear to object to having to parse Atom or JSON feeds with their own eyes, instead preferring the assistance of a helpful, friendly and intuitive user interface.

It would be fairly straightforward to knock up an API-powered search form to restore some of the now-absent search options, however being strapped for time, I prefer the quick-and-dirty hack of just using Google Spreadsheets as a stopgap solution.

So, for those wishing to search for tweets within a specific timeframe, without harming their eyes, I give you my Google Spreadsheets based hack (fanfare, please!):

https://spreadsheets.google.com/spreadsheet/ccc?key=0AgwxfJ2RdQrudDN5a1pGcjJ3RXBHZm5uNEkxcEJTZ3c&hl=en_US

You can make your own editable version of this, by just copying the spreadsheet (via the File > Make a copy… drop-down menu).

The spreadsheet uses the clever, but little-known, ImportFeed function, to import the search results into the document, and the query is driven by the cells at the top of the spreadsheet. A little sprinkling of Apps Script ensures that the parameters are safely encoded for transit across the interwebs.

So, that’s my hack! I’m not expecting it to win any prizes for visual aesthetics, but it more or less does the trick for us!

Any other clever hacks out there for achieving this? Being the geek that I am, I’d love to hear about them in the comments… :)

This post was filed under Social media, Training, Working culture and tagged , , . Join the conversation - leave a comment.

16 Comments

  1. Wow, Steve. That is so cool. From a quick observation to some clever tinkering and then a real live thing that’s useful for people. Very impressive. I owe you a pint.

    Posted 19th August 2011 at 9:17 pm | Permalink
  2. What a clever idea. I’m afraid I’ve no idea how to parse anything JSON related so I’m glad others know how to and write helpful things, so thank you :)

    I found that when changing the date it didn’t make much difference to the number of tweets returned, although I probably got into a bit of a muddle with the (to me) reversed date format, ie 21 August 2011 I think.

    How far back have you managed to get it to go? Google Realtime was able to go back to May 2010 which was a bit amazing – I think Twitter granted them special dispensation to be able to SEARCH back this far, although if you have the URL you can go back in time to any tweet (just can’t search for it).

    Thanks for this, I shall add it to my great big list of Twitter tools :-) http://brodiesnotes.blogspot.com/2011/05/list-of-tools-for-finding-or-capturing.html

    Posted 21st August 2011 at 8:43 pm | Permalink
  3. Hi Jo, you’re welcome, thanks for adding this to your list of Twitter tools :)

    This method should go back as far as Twitter’s native search function, as they both use the same underlying API, although I need to add a ‘paging’ function to get results beyond the first 100(‘paging’ added). How far back you can go seems to be constrained by volume rather than date, and the limit seems to be a mere 1,500 tweets (source: http://www.quora.com/Twitter-Search/How-far-back-in-time-does-Twitter-Search-go).

    I suspect Google was able to go back further by maintaining its own archive of tweets. Shame Google Realtime is no more :|

    Posted 22nd August 2011 at 9:23 am | Permalink
  4. Amber

    I just tried this – wow! I’m wondering if it’s possible to add a field with the # of followers each of tweet may have been seen by. I want a way to capture “reach” for some of the issues campaigns I work on.

    Also, I’m only seeing 25 tweets for the couple of searches I’ve run. Is that a limitation of this?

    Thanks!

    Posted 20th September 2011 at 9:51 pm | Permalink
  5. Hi Amber, thanks for your comment. This tool should provide the same amount of tweets as the Twitter search – perhaps you have specified too narrow a date range? Also, you need to manually navigate across pages by editing the ‘Page’ value in cell B4. That may be why you’re seeing fewer tweets than expected?

    Regarding adding number of followers each tweet has been seen by — interesting idea, although it does significantly increase the complexity of this tool. Are you aware of any 3rd party tools that provide this, or a similar, metric?

    Posted 21st September 2011 at 9:15 am | Permalink
  6. Hugh

    This is a great tool. Have been looking for a ‘tweet-extractor’ but many seem to have been bought our or blocked by Twitter. Thanks for sharing – may well be using this or similar to capture feedback/social activity around the Do Lectures.

    Posted 24th September 2011 at 10:38 am | Permalink
  7. Anne-Marie

    I love this tool. Thanks for sharing it !!
    However, I am having a problem with the pages of the worksheet. Page 2 does not seem to continue from where page 1 left off. There is a gap of missing tweets in between them.

    Example: page one shows 20 rows of my tweets dated Oct 17th (with the most current at the top ) I know I had approx 50 tweets for that date, so I would expect to see the rest of them on the next page.

    So then I change cell B4 to the number 2,to see the rest of the tweets for Oct17, but rather than showing the rest of Oct 17th’s tweets, page 2 displays tweets from Oct 16th ??

    Is there a way to modify it so I can see all tweets from Oct 17 ??

    Posted 17th October 2011 at 11:19 pm | Permalink
  8. Hi Anne-Marie, that’s weird, it should ‘just work’. Are you getting the correct results when using the native Twitter search? http://twitter.com/search

    Posted 20th October 2011 at 11:25 am | Permalink
  9. Anne-Marie

    I got it to work by editing the formula in cell A6.
    I removed the rpp=100 and it works the way I want it to.

    Posted 26th October 2011 at 2:00 am | Permalink
  10. Great, thanks for letting us know.

    Posted 26th October 2011 at 9:39 am | Permalink
  11. ‘Make a copy’ in the file menu is greyed-out for me…

    Any ideas??

    Posted 27th October 2011 at 4:54 am | Permalink
  12. Byron

    Im having a hard time figuring this out. When I change the page manually it does not work, and when I remove “rpp=100″ it still does not work.

    Can you all help! All it shows is the first page for the most recent date.

    Posted 31st October 2011 at 4:06 pm | Permalink
  13. It does not work for me. I really need to find some of my old tweets, but I don’t know how to use it. Help, anyone?

    Posted 9th April 2012 at 2:11 am | Permalink
  14. @Rodrigo: This tool uses Twitter Search, which will only go back so far, you might find Snapbird is a better tool for searching within your own timeline.

    Posted 10th April 2012 at 2:18 pm | Permalink
  15. dammika

    Would it be possible for me to get access to view the above excel sheet? I have used a similar sheet and it doesn’t show any records beyond 20 rows even though the actual search in twitter gives me more ; Is there a work around for this? Any help is great

    Posted 4th May 2012 at 8:11 am | Permalink
  16. Have you tried setting the rpp param? Will share a version of the spreadsheet with you…

    Posted 4th May 2012 at 10:38 am | Permalink

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*

Notify me of followup comments via e-mail.
You can also subscribe without commenting.