Display Google Docs Spreadsheet Data on Your Website

The Google Spreadsheet API makes it pretty painless to display spreadsheet data on your own web pages using only client side technologies. You might use this, for example, to display your terrible stock investments on one of your web pages to remind you why you have to go to work everyday.

The first thing you need to do is to make a Google Doc Spreadsheet. The second thing you need to do is to "Publish" the spreadsheet. This will make your spreadsheet available for anyone to view and is required if you are going to consume this data with unauthenticated Javascript running in a web page (so you probably don't want to do this with a sheet that contains your world domination secrets). To publish your spreadsheet, click on Share -> Publish as a Web Page -> Publish Now. Depending on the data you may also wish to check "Automatically re-publish when changes are made." For my example you certainly want to do this because the spreadsheet contains stock symbol lookup functions where the numbers will change as your stock investments go lower and lower.

After you have published your spreadsheet you need to get two pieces of information about the spreadsheet - the spreadsheet identifier and the identifier of the individual worksheet within the spreadsheet that you want to snarf the data off of. While it is possible to get at this information by pulling down meta-feeds about your spreadsheet documents it is probably easier if you just navigate to your published spreadsheet and choose View -> Source from your browser. In the source look for a link tag with rel="alternate". This tag will have an href that contains both the spreadsheet and worksheet identifier. An example looks like this:

	<link rel="alternate" type="application/rss+xml"
	title="Untitled spreadsheet - Google Docs"
	href="http://spreadsheets.google.com/feeds/list/pBYwcZBFkvxZycw-gNxPCIw/od6/public/basic">

view raw gistfile1.htm hosted with ❤ by GitHub

In that href the "pBYwcZBFkvxZycw-gNxPCIw" is the spreadsheet identifier and the "od6" is the worksheet identifier. With these pieces of information you are now ready to write some code to pull the spreadsheet data back to your web page.

There are two different feeds that you can get to pull that data back to your site. They are identified as "list" and "cells" feeds. This basically breaks down to whether you get the data back as a series of rows or a series of cells. I believe in most cases you are going to want to work with a series of rows so you will use the "list" feed, however, check the API if you believe the "cells" feed may work better for you. The code below shows the feed URL being used to cause a callback to the function processStocks:

	<script>
	function processStocks(json) {
	var stocks = $("stocks");
	var bindex = 0;
	for (var i = 0, l = json.feed.entry.length; i < l; ++i) {
	var entry = json.feed.entry[i];
	var li = new Element("li");
	bindex += parseFloat(entry.gsx$price.$t);
	var text = document.createTextNode(entry.gsx$symbol.$t + " : " + entry.gsx$price.$t);
	li.appendChild(text);
	stocks.appendChild(li);
	}
	var li = new Element("li");
	var text = document.createTextNode("bindex : " + bindex);
	li.appendChild(text);
	stocks.appendChild(li);
	}
	</script>
	<script src=http://spreadsheets.google.com/feeds/list/pBYwcZBFkvxZycw-gNxPCIw/od6/public/values?alt=json-in-script&callback=processStocks""></script>

view raw gistfile1.htm hosted with ❤ by GitHub

I won't go overboard explaining this code here - I think you can figure most of it out. It merely iterates through the returned result set from the spreadsheet, adding each stock value as to an unordered list in the page. It also keeps a running count of the total in a variable called bindex - the Burger Index. The way the cell entries are referenced is a little awkward and corresponds to the column headers - as seen in my example entry.gsx$symbol.$t and entry.gsx$price.$t. Part of this awkwardness comes from the format being a translation of the alternate XML format.

Currently you can see this code in action on my github page as well as list and cells feeds examples here.