Backbone/Marionette App Pulling Data From Google Spreadsheet

Here is an example of a simple display application pulling data from a publicly available Google spreadsheet. It parses the data into a usable form and displays it in a sortable table using a Marionette.js Composite View.

The methods used, and pitfalls inherent, in pulliing a JSON feed from a Google spreadsheet warrant their own post, which will happen in the near future. In the meantime, here is the code for this app. First the Javascript:

/* define the application */
var app = new Backbone.Marionette.Application();

/* add the main region to the application */
	appRegion: '#AppBase'

/* define the module we will be using to create this app */
app.module('Garden',function(module, App, Backbone, Marionette, $, _){
	"use strict";

	/* the URL for the JSON feed from the Google Spreadsheep */
	module.spreadsheetKey = '0AtrrbpAB-ITbdFFXVklRbHZUVW5kX2F1UWVKVVBfOVE'; // a sample spreadsheet in google docs
	module.spreadsheetURL = '{{KEY}}/od6/public/values?alt=json-in-script&callback=?';

	/* model for an individual item */
	module.GardenModel = Backbone.Model.extend({
		/* the URL from which the data is pulled */
		url: module.spreadsheetURL.split('{{KEY}}').join(module.spreadsheetKey),

		/* take the data retrieved from the URL, go through it looking for only
			the data we actually need, and pull it out and format it. This is 
			necessary because the data returned by Google Spreadsheets
			is a cumbersome mess. */
		parse: function(data) {
			var parsedArray = [];

			/* use Underscore to iterate through each item in the feed,
				and pull out the necessary fields and put them in an
				array of anonymous objects which will be returned
				as the main model for this application */
			_.each(data.feed.entry,function(oItem) {
					name: oItem['gsx$name']['$t'],
					perSquareFoot: parseInt(oItem['gsx$persquarefoot']['$t']),
					height: parseFloat(oItem['gsx$height']['$t'])
			return ({dataset:parsedArray});

	module.PlantModel = Backbone.Model.extend({
		defaults: {
			name: '',
			perSquareFoot: 1,
			height: 1

	module.PlantCollection = Backbone.Collection.extend({
		model: module.PlantModel,

		/* initial field used to sort the models when the collection is displayed */
		comparator: 'name'

	/* individual item view for each model in the collection */
	module.PlantItemView = Marionette.ItemView.extend({
		tagName: 'tr',
		template: '#row-template'

	/* Composite View for displaying tabular data. Composite Views
		allow more complex HTML to be used when rendering a template */
	module.PlantCompositeView = Marionette.CompositeView.extend({
		tagName: 'table',
		itemView: module.PlantItemView,

		/* the element in the template which will serve as the container for the ItemViews */
		itemViewContainer: 'tbody',
		template: '#table-template',

		/* add mouse events */
		events: {
			'click th' : 'onHeaderClicked'
		/* called when one of the table headers is clicked. 
			Pulls the value of the 'data-sort' attribute on the element
			and uses that as the sort argument in the collection */
		onHeaderClicked: function(event){
			this.collection.comparator = $('sort');

	/* main layout for the application */
	module.GardenLayoutView = Marionette.LayoutView.extend({
		tagName: 'div',
		id: 'AppContainer',
		template: '#layout-template',
		regions: {
			ListRegion: '#ListRegion'

		/* called when the DOM for this view is available */
		onRender: function(){

			/* create a new collection based on the model fed into this view
				when it was created */
			var plantCollection = new module.PlantCollection(this.model.get('dataset'));

			/* create the new Composite View based on the collection*/
			var plantCompositeView = new module.PlantCompositeView({collection: plantCollection});

			/* display the composite view */;

	/* add initializer, which fires when the app starts */

		/* create a new instance of GardenModel */
		var baseModel = new module.GardenModel();

		/* create a local reference to 'this', which can be used in asynchronous callbacks */
		var capturedThis = this;

		/* load the data into the model */
			.fail(function() {
			.done(function() {
				/* on successfully loading the data, create a new instance of the main layout view,
					and feed the model into it. */
				var layout = new module.GardenLayoutView({model: baseModel});

				/* show the layout in the region we created at the top of this file */;


/* when the DOM for this page is available, start the application */
$(document).ready(function() {app.start();});

And now the HTML and CSS

<!doctype html>
		<title>Backbone/Marionette using data from Google Spreadsheet rendered in a Composite View</title>
		<style type="text/css">
			body {
				background: #ffffff;
			#AppBase {
				width: 600px;
				margin: 10px auto 0 auto;
				padding: 0;
			h1 {
				font: bold 16px/24px arial, sans-serif;
				text-align: center;
			table {
				width: 600px;
				border-collapse: collapse;
			th {
				font: 14px/18px courier;
				width: 200px;
				text-align: center;
				border: 1px solid #808080;
				background: #ededed;
				cursor: pointer;
			th:hover {
				background: #cdcdcd;
				color: #990000;
			td {
				font: 14px/18px courier;
				width: 200px;
				border: 1px solid #808080;
			tfoot td {
				width: 600px;
				text-align: center;
		<!-- Base element for app -->
			Dont use the BODY element as the base because when the app renders in the BODY
			it will wipe out the template files before the views can pick them up 
		<div id="AppBase"></div>

		<!-- TEMPLATES -->
		<!-- table row template -->
		<script type="text/template" id="row-template">
			<td><%- name %></td>
			<td><%- perSquareFoot %></td>
			<td><%- height %></td>

		<!-- table body template for composite view -->
		<script type="text/template" id="table-template">
					<th data-sort="name">Name</th>
					<th data-sort="perSquareFoot">Per Square Foot</th>
					<th data-sort="height">Height</th>
				<td colspan="3">Click column headers to sort table</td>

		<!-- main layout template -->
		<script type="text/template" id="layout-template">
			<h1>Backbone/Marionette Application Which Retrieves Data From a Google Spreadsheet and Displays It In a Sortable Composite View</h1>
			<div id="ListRegion"></div>

		<!-- libraries -->
		<script type="text/javascript" src="js/jquery-1.10.2.min.js"></script>
		<script type="text/javascript" src="js/underscore.js"></script>
		<script type="text/javascript" src="js/backbone.js"></script>
		<script type="text/javascript" src="js/backbone.marionette.js"></script>

		<!-- app code -->
		<script type="text/javascript" src="js/script.js"></script>

Here are links to the code libraries used in this app:

Leave a Reply

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