There are a lots of tools on Google spreadsheet that we could use to display live data from a source website into your own website and it keeps get updated when the source is updated.
One good example I tried is getting the 2008 Beijing Olympics Overall Medal Standing, my goal is to have a list of medal standing that is updated regularly.
The source data is from http://en.beijing2008.cn/ medal standing page
Getting the data
Open up blank/new sheet on your google docs, and on the first cell put this =ImportHtml("http://results.beijing2008.cn/WRM/ENG/INF/GL/95A/GL0000000.shtml", "table",2).
ImportHtml is a Google function that retrieves info from an HTML file. The first parameter is the URL of source data, the second parameter indicate that we are going to get a table format, and the last parameter is the index of the query format, in our case the 2nd table from the source html.
Formating the data
In some cases, the result doesn’t replicate the exact column of the source table most, especially those with colspan that could be the merged cells in spreadsheet, so we are going format it to our own liking and in my case I only want to output the list of countries with their rankings, total golds, silvers, bronze, and their overall total medals. Some regular spreadsheet functions will do the job for formatting.
Generating codes to embed
After formatting, publish the sheet by going to “Publish” tab. Check the “Automatically re-publish when changes are made” so it will always updated when the source data gets updated. Click “More publishing options” this will pop up a new window, and from there select “HTML to embed in a webpage” from the File format option, then click on Generate URL button to generate the html code we need.
The Result:
3 Trackbacks
[...] ago2 votesFuelly – A New Way to Track Your Mileage on the Go>> saved by qiuksea 38 days ago2 votesPlaying with Google Spreadsheet>> saved by lidiakitty 38 days ago6 votesTuning Spreadsheet>> saved by magicman2005 42 days ago3 [...]
[...] 2007 – Import Spreadsheet fails on top level site … First saved by VivaroLane | 9 days ago Playing with Google Spreadsheet First saved by claudioforghieri | 9 days ago EXCEL BEST PRACTICES FOR BUSINESS First saved by [...]
[...] editing in Excel. Export from the web, edit in Excel (or more likely Calc), reimport into Docs. …Playing with Google SpreadsheetThere are a lots of tools on Google spreadsheet that we could use to display live data from a source [...]