JustinLee.sg

Recently I had the fine opportunity to figure out how to search and compile all the tweets into a spreadsheet, be it Excel or Google Spreadsheets. So I’m going to document this down for the benefit of everyone.

There are 3 steps to doing this:

1. Getting the search data using the Twitter API
2. Using that as a data source in Excel or Google Spreadsheet
3. Populating and saving the data

1. Getting the search data using the Twitter API

For those not so computer savvy, this just means that you’re identifying where the data is for Excel or Google Spreadsheet to suck in the data from. It’s better if you use Google Chrome for this, because Internet Explorer will try to automatically download some of these files instead of displaying them.

Firstly, type the following in your browser:

http://search.twitter.com/search.rss?q=<YOUR SEARCH>

where <YOUR SEARCH> is what you want to search for (can include spaces too), without the angle brackets “<” and “>”. This should give you 15 results back. If you want more results, you can add the following in your browser:

http://search.twitter.com/search.rss?q=<YOUR SEARCH>&rpp=100

where  rpp means the number of results you want to get back. The maximum is 100 per search only. So what happens if you want more than 100? Then you do the following:

http://search.twitter.com/search.rss?q=<YOUR SEARCH>&rpp=100&page=2

where page is the page number. So page=2 will return the next 101 to 200, page=3 will return the next 201 to 300, and so on.

http://search.twitter.com/search.rss?q=justinlee&rpp=100

http://search.twitter.com/search.rss?q=justinlee&rpp=100&page=2

Save all these links, as above, somewhere once you’ve verified all the data you want to get back. We’re going to use these links so that Excel or Google Spreadsheet can suck up this data.

For more complex searches, check out Twitter Search API.

2. Using that as a data source in Excel or Google Spreadsheet

So now you’ve determined where these data are located (based on the links you’ve just saved), now we can use Excel or Google Spreadsheet to grab these data.

First, let’s take a look at using Excel 2010. Note that Excel 2011 for Mac is unable to do this, so you’ll have to fall back to Google Spreadsheet.

Go to the “Data” tab and select “From Web”. Now paste the FIRST link you’ve created just now and “Import Data”. You’ll get to choose which cell you want the data to be imported to, and done. You’ll see all your twitter data populated into your Excel sheet. Now proceed to the next step.

For Google Spreadsheet, the caveat is you can only import 20 rows at a time, so remember to set your “rpp=20″.

Select the cell you want to start populating your data in, and type the following in the cell:

=ImportFeed("")

Now proceed to the next step.

3. Populating and saving the data

Now that you’ve populated the 1st page, it’s time to put in all your data.

For BOTH Excel and Google Spreadsheet, you should copy and paste the data to another sheet before repeating step 2. After you’ve duplicated the data to another sheet, then REPEAT STEP 2 with the next link you have.

After you’re done importing all the data, you can start analyzing your Twitter data with your Excel/Spreadsheet magic. Have fun!