Introduction
Some book-lovers and bloggers like to share on their website the titles of the books they are currently reading or the ones on their bookshelf through widgets or custom built components.
Books can be a good conversation topic & a way to discover new books. Like songs, some of the favorite books I've read have been recommendations from friends. While learning AngularJS, I chanced upon a sample on the Stack Overflow forum that inspired me to list out my own books on my blog. It was around this time that I found out that data in a Google Spreadsheet can be accessed in JSON and JSONP format through a URL. Using AngularJS & the Google Spreadsheet service as a read-only database, I built a book tracker that also allows me to share my book list with friends & my blog readers. Check it out (but don't judge me entirely by my taste in books) before you decide to go through the rest of the article and possibly adapt the code for your own requirement.
Background
It came as a revelation to me that there is a way to use a Google Spreadsheet as a JSON backend. The trick is to extract the key from a Google spreadsheet's URL (the key looks something like this - 0AtMEoZDi5-pedElCS1lrVnp0Yk1vbFdPaUlOc3F3a2c) and place it in the position indicated in the below URL:
https://spreadsheets.google.com/feeds/list/KEY/od6/public/values?alt=json-in-script&callback=
This approach can be useful when a limited number of people manage a database but when it has to be publicly viewed by users of a website. You can even use Google Spreadsheet formulas (like ImportXML()) to create dynamic values for a column.
This feature can be useful in a scenario where a limited number of people manage a database but the dynamic data has to be publicly exposed to users of a website in a appealing way. The advantage for the host is that there is no expense for the back-end (as Google Spreadsheet is available for free) and no need to create a CRUD (Create, Read, Update, Delete) interface as these operations can be done easily through the sheet.
I used this nifty feature to build my virtual bookshelf
The Back-end
For my requirement, the schema is just the name of the book & the ASIN (the Amazon product code for the book). If you're an Amazon Affiliate, the ASIN can be used to display images & link to the book.
Note that ASIN can be alphanumeric and may start with a zero. In such cases, the zeros may vanish unless the format of the ASIN column is set as “Plain text” so change the format of the whole column.
After compiling the list of books, I published the Sheet to the web (File > Publish to the web...)
Of the entire JSON that can be accessed from the spreadsheet URL (which has this format - https://spreadsheets.google.com/feeds/list/KEY/od6/public/values?alt=json-in-script&callback= ), I'm interested in the values for the following fields:
- entry.gsx$bookname.$t
- entry.gsx$asin.$t
Using the code
The following AngularJS script presents the book list in a (somewhat) visually appealing manner. Evidently, there is some (!) work left to do with the CSS.
<!DOCTYPE html>
<html>
<head>
<title>My Books</title>
<script src="https://ajax.googleapis.com/ajax/libs/angularjs/1.2.16/angular.min.js"></script>
<script>
var myApp = angular.module("myApp", []);
myApp.service('myservice', ['$http', '$q', function ($http, $q) {
var entries = [];
function getData(){
var bookList = [];
var bookFeed = "https://spreadsheets.google.com/feeds/list/insert_your_spreadsheet_key_here/od6/public/values?alt=json&callback=JSON_CALLBACK";
$http.jsonp(bookFeed)
.success(function(data){
angular.forEach(data.feed.entry, function(entryX){
bookList.push(entryX);
});
angular.copy(bookList, entries);
})
.error(function (data) {
$scope.data = "Request failed";
});
}
return {
getData: getData,
entries: entries
};
}]);
myApp.controller('books', function ($scope, myservice) {
$scope.message = "";
$scope.entries = myservice.entries;
myservice.getData();
});
</script>
<style>
body {
margin: 0;
padding: 0;
text-align: center;
}
#container {
width: 99%;
margin: 0 auto;
text-align: left;
}
.cell {background-color:#f0f0f0;float: left;overflow: hidden;margin:2px;width:150px;height:250px;vertical-align:middle;}
.text { margin:3px;text-align:center; }
img { display: block; margin-left: auto; margin-right: auto; padding:3px}
</style>
</head>
<body>
<div ng-app="myApp">
<div id="container" ng-controller="books">
My Books
<div ng-repeat="entry in entries">
<div class="cell">
<img ng-src="http://images.amazon.com/images/P/{{entry.gsx$asin.$t}}.01.20MTLZZZ" />
<p class="text">{{entry.gsx$bookname.$t}}</p>
</div>
</div>
</div>
</div>
</body>
</html>
The ASIN (Amazon Standard Identification Number) value helps us provide an image for the book -
http://images.amazon.com/images/P/{{entry.gsx$asin.$t}}.01.20MTLZZZ
Amazon has a convenient file naming convention for the image thumbnails of the books (probably meant for use only by Amazon affiliates). You can control the size of the thumbnails as well other image properties as described in this (pre-2006) article.
You can re-use the script (which I adapted from this StackOverflow answer) by building your own Google Spreadsheet with your own values for the bookname & ASIN fields. After you save the sheet, note down the key for your file from the URL and replace the key within the code above. You can adapt the code to show only books you are reading or as a simple ad inventory management system with Google Spreadsheets as the back-end.
I extended the same script to track my list of aphonetic words.
Points of Interest
The above script which is less than 70 lines of un-minified readable code including CSS & JavaScript shows what can be accomplished with AngularJS (the reference to the minified AngularJS file from the Google CDN is about 38KB) & web services that expose data via JSON.
History
First published: 2015-01-23
