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...?
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=0Ak0qDiMLT3XddHlNempadUs1djd...
$(function listBooks() {
$.getJSON( "https://spreadsheets.google.com/feeds/list/0Ak0qDiMLT3XddHlNempadUs1djdk...,
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">
![http://covers.openlibrary.org/b/isbn/0151900558-S.jpg">](http://covers.openlibrary.org/b/isbn/0151900558-S.jpg">){kind=link}