Skip to content
Ecce Signum

Ecce Signum

Immanentize the Empathy

  • Home
  • About Me
  • Published Works and Literary Matters
  • Laboratory

Month: April 2014

More On Using Google Spreadsheets and Backbone/Marionette

2014-04-06 John Winkelman

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.

Posted in Programming comment on More On Using Google Spreadsheets and Backbone/Marionette

Personal website of John Winkelman

Archives

Categories

Posts By Month

April 2014
S M T W T F S
 12345
6789101112
13141516171819
20212223242526
27282930  
« Mar   May »

Twitter Feed

JohnWinkelmanJohn Winkelman@JohnWinkelman·
7 Mar

New blog post. Got some books. Felt some ennui. http://eccesignum.org/2021/03/07/march-already-and-still/
#aleksandrsolzhenitsyn #apexbookcompany #andotherstoriespublishing #parisreview #amreading #russianliterature

Reply on Twitter 1368562949296709639Retweet on Twitter 1368562949296709639Like on Twitter 1368562949296709639Twitter 1368562949296709639
JohnWinkelmanJohn Winkelman@JohnWinkelman·
7 Mar

It's been a long week.

Reply on Twitter 1368431629610459140Retweet on Twitter 1368431629610459140Like on Twitter 13684316296104591403Twitter 1368431629610459140
JohnWinkelmanJohn Winkelman@JohnWinkelman·
2 Mar

I just backed Chronicles of the Immortal Swordsmen #1 on @Kickstarter https://www.kickstarter.com/projects/1617982296/chronicles-of-the-immortal-swordsmen-1?ref=android_thanks_twitter_share

Reply on Twitter 1366742012481966080Retweet on Twitter 1366742012481966080Like on Twitter 13667420124819660801Twitter 1366742012481966080
Load More...

Links of Note

Reading, Writing
Tor.com
Locus Online
The Believer
File 770
IWSG

Watching, Listening
WYCE Electric Poetry
Writing Excuses Podcast
Our Opinions Are Correct

News, Politics, Economics
Naked Capitalism
Crooked Timber

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

© 2021 Ecce Signum

Proudly powered by WordPress | Theme: x-blog by wpthemespace.com