Creating OPAC With Google Awesome Table

Hi, In this post, I'm gonna show you how to create a do-it yourself library catalog, where you can catalog your own library books and you can create a searchable online catalog for completely free. If you are working in a small library you can even use it as a Library management system(LMS) as well. 

Online Catalog

Requirements: 

1. A Google Account

2. Report generated from Koha at least with Title, Author, ISBN, Publisher, Year of Publication, Subject Heading, Home Library, Location, Call Number, Barcode

3. A website created from Google Sites

Step: I

Go to https://docs.google.com/spreadsheets create a new spreadsheet Import your local spreadsheet  from   File-->Import after importing successfully open the file from google spreadsheet and add the following Standard filters suggested by Google Awesome Table, they are 

1 STANDARD FILTERS

1.1 StringFilter

1.2 NumberRangeFilter

1.3 DateFilter

1.4 CategoryFilter

1.5 csvFilter

1.6 NoFilter

2 OPTIONS FOR FILTERS

2.1 Dependent Category Filters

2.2 Labels inside filters

Step: II

Under the title row we need to add another row and put this filters accordingly

Add filter

Here I have added an extra column for displaying cover image of books. for that just add the direct Image url ( <img src="https://pictures.abebooks.com/isbn/9788172313524-us.jpg" width="100" height="100"/> ) within that html code

Step: III

After creating sheet with appropriate filters we need to publish that sheet on the web for that click publish to web from the file menu of google sheets and make it public and click share button as well.

Step: IV

Go to Google Awesome Table  from there you will get different templates select any templats which you want. I have used a blank one here.from where you need to select a google spreadsheet and click create button. (If you are using Awesome Table as full fledged LMS  you may have to specify the rage of the sheet you want to display, Like Not showing patron details,due date, check in/checkout etc)

Step: V

Create a website from https://sites.google.com/classic  giving your library site name eg: opacgems. after clicking create button you will be prompted into google sites dashboard. from there

Click --> Edit page --> Insert --> choose Awesome table gadget from the search bar --> Click --> Select

from there you will be directed to choose your Awesome table choose that and click OK and save the task button from the top of the dashboard. that's it your searchable online spreadsheet/OPAC is ready. The entire google site can be customized, edited  by adding custom theme, text, html, color etc..

NB: Generate an order-wise report with below SQL syntax (Title, Author, ISBN, Publisher, Year of Publication, Subject Heading, Home Library, Location, Call Number, Barcode )

SELECT biblio.title, biblio.author, biblioitems.isbn, biblioitems.place, biblioitems.publishercode, biblio.copyrightdate, biblioitems.pages, ExtractValue( metadata, '//datafield[@tag="650"]/subfield[@code="a"]' ) AS keyword, items.homebranch, items.location, items.barcode
FROM items
LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)
JOIN biblio_metadata ON (biblioitems.biblionumber = biblio_metadata.biblionumber)
ORDER BY LPAD(items.barcode,40,' ') ASC

Visit our Library site with Awesome table                                                               : https://sites.google.com/site/opacgems/

Reference: https://sites.google.com/site/scriptsexamples/available-web-apps/awesome-tables/documentation/basic-configuration/available-filters
                  : https://www.youtube.com/watch?v=7EeqPTjK38s