Pulling data from a published Google Spreadsheet it actually quite simple. All you need is a spreadsheet which you have “published to web”; i.e. made visible (but not editable) to the public. Backbone models have no difficulty pulling the data in, but the structure is complex, and requires a little explanation.

First, here is what the spreadsheet looks like:

Name	per square foot	height
arugula	9	9
basil	4	9
beets	9	6
carrots	16	4
collard greens	2	16
cauliflower	1	18
tomatoes	1	36
broccoli	1	24
mustard greens	9	12
peppers	1	18
chard	4	12
kale	2	12
radish	36	8
brussels sprouts	1	24
okra	1	24
eggplant	1	24
celery	4	18

I am pulling in a representational JSON feed from Google Drive. The data (trimmed to the first two rows) looks something like this:

{
	"version": "1.0",
	"encoding": "UTF-8",
	"feed": {
		"xmlns": "http://www.w3.org/2005/Atom",
		"xmlns$openSearch": "http://a9.com/-/spec/opensearchrss/1.0/",
		"xmlns$gsx": "http://schemas.google.com/spreadsheets/2006/extended",
		"id": {
			"$t": "https://spreadsheets.google.com/feeds/list/{{DOCUMENT_KEY}}/od6/public/values"
		},
		"updated": {
			"$t": "2014-01-22T19:27:16.240Z"
		},
		"category": [
			{
				"scheme": "http://schemas.google.com/spreadsheets/2006",
				"term": "http://schemas.google.com/spreadsheets/2006#list"
			}
		],
		"title": {
			"type": "text",
			"$t": "Sheet1"
		},
		"link": [
			{
				"rel": "alternate",
				"type": "text/html",
				"href": "https://spreadsheets.google.com/pub?key={{DOCUMENT_KEY}}"
			},
			{
				"rel": "http://schemas.google.com/g/2005#feed",
				"type": "application/atom+xml",
				"href": "https://spreadsheets.google.com/feeds/list/{{DOCUMENT_KEY}}/od6/public/values"
			},
			{
				"rel": "self",
				"type": "application/atom+xml",
				"href": "https://spreadsheets.google.com/feeds/list/{{DOCUMENT_KEY}}/od6/public/values?alt=json-in-script"
			}
		],
		"author": [
			{
				"name": {
					"$t": "user.name"
				},
				"email": {
					"$t": "user.name@gmail.com"
				}
			}
		],
		"openSearch$totalResults": {
			"$t": "17"
		},
		"openSearch$startIndex": {
			"$t": "1"
		},
		"entry": [
			{
				"id": {
					"$t": "https://spreadsheets.google.com/feeds/list/{{DOCUMENT_KEY}}/od6/public/values/{{CELL_KEY}}"
				},
				"updated": {
					"$t": "2014-01-22T19:27:16.240Z"
				},
				"category": [
					{
						"scheme": "http://schemas.google.com/spreadsheets/2006",
						"term": "http://schemas.google.com/spreadsheets/2006#list"
					}
				],
				"title": {
					"type": "text",
					"$t": "arugula"
				},
				"content": {
					"type": "text",
					"$t": "persquarefoot: 9, height: 9"
				},
				"link": [
					{
						"rel": "self",
						"type": "application/atom+xml",
						"href": "https://spreadsheets.google.com/feeds/list/{{DOCUMENT_KEY}}/od6/public/values/{{CELL_KEY}}"
					}
				],
				"gsx$name": {
					"$t": "arugula"
				},
				"gsx$persquarefoot": {
					"$t": "9"
				},
				"gsx$height": {
					"$t": "9"
				}
			},
			{
				"id": {
					"$t": "https://spreadsheets.google.com/feeds/list/{{DOCUMENT_KEY}}/od6/public/values/{{CELL_KEY}}"
				},
				"updated": {
					"$t": "2014-01-22T19:27:16.240Z"
				},
				"category": [
					{
						"scheme": "http://schemas.google.com/spreadsheets/2006",
						"term": "http://schemas.google.com/spreadsheets/2006#list"
					}
				],
				"title": {
					"type": "text",
					"$t": "basil"
				},
				"content": {
					"type": "text",
					"$t": "persquarefoot: 4, height: 9"
				},
				"link": [
					{
						"rel": "self",
						"type": "application/atom+xml",
						"href": "https://spreadsheets.google.com/feeds/list/{{DOCUMENT_KEY}}/od6/public/values/{{CELL_KEY}}"
					}
				],
				"gsx$name": {
					"$t": "basil"
				},
				"gsx$persquarefoot": {
					"$t": "4"
				},
				"gsx$height": {
					"$t": "9"
				}
			}
		]
	}
}

It’s a lot of information, but it is fairly simply laid out. There is a lot of meta-data and author information. For the purposes of a simple display list, the only data we need is the ‘entries’ array. This is an array of Javascript objects, each of which represents a row in the spreadsheet. This assumes that the first row in the spreadsheet contains the titles for each column; e.g. ‘name’, ‘per square foot’, etc.

Note that within each of the entries in the array are three objects which correspond to the columns in the spreadsheet: gsx$name, gsx$persquarefoot, and gsx$height. These are simply the names of the columns, stripped of spaces and converted to all lower case. Each of these objects contains a single object with the key ‘$t’, which has a value equal to the content of the corresponding cell from the spreadsheet.

If you review the code from my previous post you can see that, once the data structure is known, it is quite simple to parse the JSON for the values you need.

Note also that this entry does not address the process of modifying a Google Spreadsheet from outside of the Google Drive context; that is a significantly more complex operation.