Data Mashups with Google Spreadsheets and Yahoo Pipes

Recently I found a couple of fascinating blog posts on how to mashup data from anywhere on the web with Google spreadsheets and Yahoo pipes.  To see the exact steps on how to do this please refer to those 2 articles.  It’s explained there much better than what I can recreate, but I wanted to get into the possibilities of what you can do with these kinds of tools.

You’re probably at least somewhat familiar with Google Docs.  If not you can check out a few of my previous posts on Using Google Docs for Wedding Invitations or Using Google Docs at Work.  One of the great things about Google Docs is that it lives online, whereas an Excel document is static and saved on your local computer.  With Google Docs you can specify a specific URL to take data from, and your spreadsheet will update whenever that web page is updated.  The function you use is the “importHTML” function in Google Docs.

So let’s say you want to do some data analysis for your fantasy football league.  You want to see what players on your team are underperforming and see if there are any replacement players that you’d like to trade for.  ESPN.com has all the stats you would need available on their site.  Once you find a page that has the exact data you want you can use the “importHTML” function to throw it into a Google spreadsheet.  Now you can run any custom formulas in your spreadsheet to see how different players are performing to the standards you set.  Every week that page on ESPN.com is going to be updated with new data.  Instead of having to download that again (like you would in Excel) your data will automatically update in your spreadsheet.

Yahoo Pipes is a web application that might not be as well known.  It’s essentially a tool that allows you to take information from any web service, slice it any way you want if you have the technical know-how, and then create custom RSS or XML feeds that can be used with other services.  One of the applications that is quite useful is taking location data from a website and putting that into a map on Google maps.

So, say you have a list of cities on a concert tour, and you’d like to see these laid out visually on a map.  One option would be to use a service like BatchGeo if you have all of the address data already formatted nicely in a spreadsheet.  However, what if that data is stored on a Wikipedia page.  Here’s how you could create the map.

1. Use the “importHTML” function to pull data from that table into a Google spreadsheet.

2. Use Yahoo Pipes to pull the data from your spreadsheet into the KML format that is needed for Google Maps.  Within the Yahoo Pipes interface you can sort by date so that you can number the concert stops according to the dates they occur.

3. Export the data in the KML format and paste the new URL into Google Maps.  The tour locations will be displayed with date labels on your new map.

There are a lot of cool applications that I can see with this type functionality.

  • Create maps of sales targets for a business to give to their sales associates to use when they’re out in the field
  • Manage your fantasy team with custom metrics that you build in a spreadsheet
  • Track all your interactions on social networks and pull them into a spreadsheet to use as a CRM database so you can set reminders on when to contact someone again

I’m excited to play around with these tools and see what else is possible.

Be Sociable, Share!

One thought on “Data Mashups with Google Spreadsheets and Yahoo Pipes

  1. Tony T

    Hi – thanks for sharing!

    What happens if you want to pull data from a page that is password protected, i.e. you have to login to a site before you can access the page the table is on? Is there a way to include login info in the function?

    In Excel… you can actually use the excel browser to go to the webpage and login, then run the function and access the data. Is there something similar with google ss?

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>