ALA TechSource Logo
 
curve Home spacer Publications spacer Subscribe spacer Blog spacer About  
    

Using the Google Spreadsheets Data API to build a Recommended Reading List - Code Words

Submitted by Jason Clark on May 8, 2012 - 9:42am

Welcome to Code Words. A new column looking to demonstrate how simple programming tasks and emerging technology trends can be applied to libraries. My goal is to provide an introduction to the role that programming and computer tools can play in building the digital branch of the library. Topics will include everything from relational databases, to mobile services, to simple Javascript routines, and much more.

Many columns will explain a specific concept and then show how it might be applied in a live, application setting. In this first column, we will look at the idea behind a “data store” that might power an app or website. In generic terms, a data store is the place where you store the data that you will use. This “place” usually takes the form of a database, but it can be anything from a spreadsheet to a text file delimited by commas. (See http://en.wikipedia.org/wiki/Data_store.)

Our first example will show how to use a Google Docs spreadsheet as your data store. Once you have the spreadsheet and data in place, we’ll create a little HTML and add some Javascript to query the spreadsheet data and produce a recommended reading list.

Check out the demo in action here: http://goo.gl/df9Y3
Download and view the full source code here: http://goo.gl/0jbRe
Get sample spreadsheet and data here: http://goo.gl/7ZObf

Step 1: Create your data in a Google Spreadsheet

Most applications that you create will need some form of data to run the display. We are going to create a spreadsheet to hold data related to our book list. Sign into Google Docs using your Google account. Choose “Create new spreadsheet” and create six columns of common book data fields - Title, Author, Publisher, OCLC#, ISBN, Notes - and add a few empty rows for single item book data. When finished, begin adding your data in the data rows under the column fields that you just created. If you don’t want to bother with creating a new spreadsheet, here's a link to a Google Docs spreadsheet with the sample values in this example as a template - http://goo.gl/7ZObf.

Two dev notes are worth mentioning here. We are using the Open Library Book Cover API to get our cover thumbnails (http://openlibrary.org/dev/docs/api/covers). When adding your own data to a spreadsheet, it works best if you navigate to Open Library records and grab data from Open Library items that have thumbnails in their display. One other important format note: be sure to set the formatting on those columns where you are entering numbers as “plain text.” Google docs likes to strip leading zeros from number strings, which can break your ISBN and OCLC# values. (See this Google Docs thread for details - http://goo.gl/8Bkq1.)

Step 2: Make the spreadsheet available as a public feed

Once you have added all the book data, the next thing to do is make the spreadsheet available as a public feed. The script that we will create in later steps will use this public feed to bring in data to display. Go to the settings on your spreadsheet, make the document available to "all on the web," and save it. If you check the URL (address bar in the browser), you should see an alphanumeric id after the “key=” parameter in the URL. Copy that id and add it to the URL below to set your public feed.

https://spreadsheets.google.com/feeds/list/ADD-YOUR-ALPHANUMERIC-ID-HERE/od6/public/values?alt=json-in-script&callback=?

Notice that there are two extra values in the URL: "alt=json-in-script&callback=?". The "alt=" value tells Google to display the public feed as Javascript Object Notation (JSON) - a structured data format optimized for web delivery - and the "callback=" value sets up an empty value that our Javascript will use to cause the script to run. For more info on retrieving JSON feeds from Spreadsheets Data API, see https://developers.google.com/gdata/samples/spreadsheet_sample.

Step 3: Create the HTML foundation
[See the full booklist HTML in this gist - http://goo.gl/pTSwI

All buildings need a foundation, and in the case of web programs, that foundation is HTML markup. In this example, we start with the head information for the HTML file that provides a title, defines the character set for the file, and brings in the jQuery Javascript library that will create the behavior for the application.

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8" />
<title>New Books We Recommend</title>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>

Whenever possible in Code Words, we will try to use frameworks and code libraries (like jQuery). Programming tasks can be automated, and many times these frameworks and libraries are tested and vetted across multiple browsers and operating systems.

Step 4: Finishing the HTML markup & giving the script a place to display the spreadsheet data.

...
</head>
<body>
<h1>New Books We Recommend</h1>
<div id="book-list"></div>
</body>
</html>

Our script needs a home in the HTML to place the values from our spreadsheet. In the above HTML markup, we introduce <h1> and <div id="book-list"> tags for this purpose. Our display now has a title explaining what type of data is being displayed and markup that will be used to place the dynamic content from the external spreadsheet.

Step 5: Building a Javascript function to get the data from our spreadsheet and display it
[See the full booklist Javascript in this gist - http://goo.gl/hmxlh]

Once we have opened the HTML document and included the jQuery library, we can create a script that will retrieve the JSON data feed from our spreadsheet. First, we tell the script to run once the page has loaded using a built-in jQuery function - $(document).ready(function(). Next, we create a custom function, "listBooks" that will do all of the work - request the data, parse the data, and create a display for the data.

<script type="text/javascript"> 

$(document).ready(function() {
//source file is https://docs.google.com/spreadsheet/ccc?key=0Ak0qDiMLT3XddHlNempadUs1djdkQ0tFLWF6ci1rUUE
$(function listBooks() {
$.getJSON( "https://spreadsheets.google.com/feeds/list/0Ak0qDiMLT3XddHlNempadUs1djdkQ0tFLWF6ci1rUUE/od6/public/values?alt=json-in-script&callback=?",
function (data) {
$('div#book-list').append('<ul class="items"></ul>');
$.each(data.feed.entry, function(i,entry) {
var item = '<span style="display:none">' + entry.id.$t + '</span>';
item += '<img src="http://covers.openlibrary.org/b/isbn/' + entry.gsx$isbn.$t + '-S.jpg"/>';
item += '<span class="meta"><a href="http://www.worldcat.org/isbn/' + entry.gsx$isbn.$t + '">' + entry.title.$t + '</a>';
item += '<br/>Author: ' + entry.gsx$author.$t;
if (entry.gsx$notes.$t) {
item += '<br/>Description: ' + entry.gsx$notes.$t;
}
$('.items').append('<li>' + item + '</span></li>');
});
});
});
});

</script>

Yeah, so there is a much going on here... We’ll work through it from top to bottom. First, "$.getJSON" is a jQuery function telling the script to load our spreadsheet. With the spreadsheet data loaded into memory, we are now in our second phase of the script which is to parse the data that was returned. "function (data)" will play this role. We use a jQuery selector syntax to tell the script to find the <div id="book-list"> tag and place a <ul> (unordered list) inside so that our new books will display as a bulleted list. Next, we tell the script to work or "loop" through all the entries (or rows) in the spreadsheet using a the jQuery $.each syntax. We haven’t seen the JSON that our spreadsheet returns. It looks something like this:

...
"gsx$title": {
"$t": "This house of sky : landscapes of a Western mind"
},
"gsx$author": {
"$t": "Ivan Doig"
},
"gsx$publisher": {
"$t": "New York: Harcourt Brace Jovanovich, 1992."
},
"gsx$oclc": {
"$t": "25629631"
},
"gsx$isbn": {
"$t": "0151900558"
},
"gsx$notes": {
"$t": ""
}


We want the script to walk through and store all the values it retrieves. Note that we create our thumbnails by passing the ISBN from the “gsx$isbn” value to a URL for an image from Open Library - http://covers.openlibrary.org/b/isbn/' + entry.gsx$isbn.$t + '-S.jpg. With these various pieces of data available, we create a "item" variable that we can use to store the data. The script works through all pieces of the JSON and prints the HTML inside of the “var item” variable. In the end, the script generates and returns an HTML snippet for each row that looks like this:

<li>
<span style="display:none;">...</span>
<img src="http://covers.openlibrary.org/b/isbn/0151900558-S.jpg">
<span class="meta">
<a href="http://www.worldcat.org/isbn/0151900558">This house of sky : landscapes of a Western mind</a><br>Author: Ivan Doig
</span>
</li>

Each of these <li> snippets are pushed into the <div id=”book-list> on the HTML page when the browser loads the page.

Step 6: Apply the grid layout with CSS
[See the full booklist CSS in this gist - http://goo.gl/ctL9h]

<style type="text/css">
.items {display:table;list-style:none;margin:0;padding:0;border-spacing:5px;}
.items li {display:table-row;-webkit-border-radius:10px;-moz-border-radius:10px;border-radius:10px;border:1px solid #ccc;padding:5px;margin:0 0 10px 0;}
.items li img {display:table-cell;vertical-align:top;}
.items li span.meta {display:table-cell;vertical-align:top;margin:0;padding:0 0 0 5px;}
.items li {margin:0 0 5px 0;}
</style>

And finally, we create our display styles for the recommended reading list. The CSS declarations above create the grid view with a leading thumbnail. We are styling the unordered list <ul class=”items”> as a table with rows. Once you combine all the above steps, you will have the complete file. Once satisfied with the output, place it in a web accessible directory and make it live.

Final thoughts

This sample script is just one example of how you might use a Google docs spreadsheet as your data store. Things start to get interesting when you consider other options:

  • A list of subscription databases from your library
  • A rudimentary news and events ticker
  • Record common keywords from search queries on your catalog and link them in a popular/recent searches widget

The key is that you have a publicly available spreadsheet that numerous editors in your library can access and add data to. You might even create a form for your editors to simplify data entry (http://goo.gl/ddd4U). Opening your options for contributing to your data store is the first step in making this happen. (One last side note: a quick hat tip to Karen Coombs for inspiration and for pointing out to me that Google Spreadsheets had a feed API in the first place.)

I’ll be back in a few weeks to take a closer look at bookmarklets, those little bits of Javascript that add functionality to your browser. In the meantime, play around with this sample and feel free to ask questions here or on twitter @jaclark.

Posted in

Comments (3)

Hi Jason, I have been using

Hi Jason,

I have been using Google spreadsheets pretty extensively since their inception.
As a web developer I am quite interested in the possibilities that this API opens up.
Effectively this lets a Google spreadsheet become a CMS for a site.

It is perhaps not user friendly to some, but for my purposes it is ideal.
I am working on a test prep site and use this method to store banks of test questions.
This is far more agreeable than using a full fledged CMS and/or making DB calls.

In ASP.NET, you can do a "screen scrape" of any public URL to retrieve data from a Google spreadsheet.
You can get data from a spreadsheet by searching the full HTML text of the page for specific ids or patterns. But this is hackish and clumsy compared to the JSON request which uses Google's API.

I somewhat don't like that the spreadsheet must be a public URL, but in this case that isn't a huge concern.

One thing I discovered in working through your example. When referencing the columns you must use all lower case. Additionally you can't have columns that have names like "*" or "%" as these are reserved characters.

If the Google spreadsheet page was ever down that would naturally break my site, but so far as I can tell, their pages are quite reliable and that isn't too much of a concern.

Thanks for your well written guide.

Cheers,

Will Cooper

Addendum: Got the fix.

Addendum: Got the fix. Instead of "bind" use now "live":


...$('#myDiv tr').live({...

Hello Jason, thanks for this

Hello Jason,

thanks for this wonderful article. It helped me to solve another piece in my puzzle.

I adapted it and it works except one little thing, may be you have a tip for me.

In my application I build some rows and append these to a table. Works fine for some already existing rows - for the newly generated rows some events binded won't fire. Mysterious.

Here some lines of code:


$(function listActions() {
$.getJSON("https://spreadsheets.google.com/feeds/list/.../od6/public/values?alt=json-in-script&callback=?", function (data) {
var t='';
$.each(data.feed.entry, function (i, entry) {
var acts = '';
acts += t + entry.gsx$firstItem.$t + '';
...
...
acts += t + entry.gsx$lastItem.$t + '';
acts += '';
// Append each row to the already existing table with few rows
$('myTable.tableClass').append(acts);
});
});
});
...
...

// Here now the part that works fine only for the already
// existing table header and the first rows of the table - for
// the newly generated rows via JSON there is no affect.
// For example all style information (pointer...) does not exist
// when I inspect the code with Firebug:

$('#myDiv tr').bind({
mouseenter: function(){
if (!$(this).hasClass("header2")){
$(this).css({background: '#555', color: '#fff'});
$(this).css( 'cursor', 'pointer' );
}
},
mouseleave: function(){
$(this).css({background: '#fff', color: '#000'});
},
dblclick: function() {
var arr = [];
$('td', this).each(function() {
arr.push($(this).text());
})
if(confirm('Do you want to "' + arr[0] + '" MyQuestion.... ?')) {
...
...
}
}
});

Best regards
Peter