Click here to Skip to main content
14,355,397 members

Virtual bookshelf

Rate this:
4.64 (13 votes)
Please Sign up or sign in to vote.
4.64 (13 votes)
7 Feb 2015CPOL
Show off your book collection using a AngularJS script & Google Spreadsheet as your back-end

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.

Image 1

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.

Image 2

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. 

Image 3

After compiling the list of books, I published the Sheet to the web (File > Publish to the web...)

Image 4

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

Image 5

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

'Anil' Radhakrishna
Web Developer
India India
'Anil' Radhakrishna is a seasoned developer who enjoys working with Microsoft tools & technologies. He blogs quite regularly about his little discoveries and technical experiments on his blog called Tech Tips, Tricks & Trivia. He loves building mash-ups using public Web APIs.

Comments and Discussions

 
GeneralMy vote of 4 Pin
newton.saber7-Feb-15 6:16
membernewton.saber7-Feb-15 6:16 
GeneralRe: My vote of 4 Pin
'Anil' Radhakrishna7-Feb-15 7:08
member'Anil' Radhakrishna7-Feb-15 7:08 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Article
Posted 22 Jan 2015

Stats

17.4K views
17 bookmarked