Fun with Google Spreadsheets and Fusion Tables
- Written by
- Clay Johnson
- Date
- 06/12/2009 12:48 p.m.
Fun with Google Spreadsheets and Fusion Tables
I've been having a lot of fun with Google tools today, and I wanted to share. This morning I was interested in generating this pie chart from the data off of Data.gov in my last post but needed to get all the data out of the Data.gov Data Catalog first.
Google Spreadsheets actually makes this really easy -- if you know what you're doing:
Step 1: Create a new spreadsheet, and put this little line in a cell:
=importHtml("http://www.data.gov/catalog#raw","table",2)
Step 2: There is no step 2. You're done.
Cool huh? You've now got a spreadsheet of all the data in Data.gov. But that's not what I wanted-- what I wanted was a count of each data source by agency to see who was providing the most data. The answer here was Google's new Fusion Tables. In Fusion Tables, I can then take the data, create an aggregation and provide me the counts, imported from my Google Spreadsheet.
Easy data analysis without a lick of code.
Discussion
What are Your Thoughts?
Have thoughts that might fuel this discussion further, post them below. (Markdown syntax is supported in comments.)
Many thanks for a handy way to keep tabs on Data.gov. One note and a question -- first, it appears you have to enter "3" as the index in the importHtml function in order to pull the desired data. In other words:
Also, the site is paginated the above only pulls the first 25 entries out of more than 302 current listings. I used the below URL to pull all of them:
...but clearly that will break as the listing figure climbs. Is there a simpler URL that's more appropriate for grabbing all the listings?
Otherwise -- great post!
Thanks for the useful tip! I'm also struggling to find a simpler URL to do what Serdar described above, do you know anyway of being able to pull all the listings into the spreadsheet?
That aside, it is an excellent way of keeping track of data and Fusion Tables is an excellent little tool too.
This really is a great way of keeping track of data without adding a line of code. Both tools work exceptionally well today in providing useful and meaningful data analysis. Thanks for passing them on.