This guide demonstrates how to create a free, searchable online catalog for your personal library. Smaller libraries can even adapt this method as a simple Library Management System (LMS).

Online Catalog

What You’ll Need:

1. A Google Account
2. A report from Koha (or similar system) containing at least: Title, Author, ISBN, Publisher, Year of Publication, Subject Heading, Home Library, Location, Call Number, Barcode
3. A Google Sites website

Step 1: Preparing Your Data

• Open a new spreadsheet in Google Sheets: https://docs.google.com/spreadsheets

• Import your library data from File > Import.

• Once imported, add the following standard filters recommended by Google Awesome Table:

  • StringFilter
  • NumberRangeFilter
  • DateFilter
  • CategoryFilter
  • csvFilter
  • NoFilter

• Consider these additional options for enhanced filtering:

  • Dependent Category Filters
  • Labels inside filters

Step 2: Setting Up Filters in Your Spreadsheet

• Insert a new row beneath the title row in your spreadsheet.
• Apply the filters from Step 1 to their corresponding columns.
• For book covers, create an extra column. In each cell, add the direct image URL using this HTML structure:
  ![](https://pictures.abebooks.com/isbn/9788172313524-us.jpg "Example Image") (Replace the example URL with your book cover image).

Step 3: Publishing Your Spreadsheet

• In Google Sheets, go to File > Publish to the web.
• Choose to make the sheet public and click “Share.”

Step 4: Integrating with Google Awesome Table

• Visit Google Awesome Table: https://awesome-table.com/
• Select a template (or a blank one for customization) and click “Create”.
• Choose your published Google Sheet from Step 3.
• If using this as a full LMS, define the spreadsheet’s range to control what information is displayed publicly (e.g., hiding patron data, due dates, etc.).

Step 5: Embedding on Your Google Sites Website

• Go to your Google Sites dashboard: https://sites.google.com/classic
• Navigate to Edit page > Insert > Search for “Awesome Table” gadget.
• Select the gadget, choose the Awesome Table you created in Step 4, and click “OK.”
• Save your changes. Your online catalog is now live!

Additional Notes:

• You can customize your Google Site with themes, text, HTML, colors, and more.
• To generate a report from Koha with the necessary data points (Title, Author, ISBN, etc.), use the following SQL query:

1
2
3
4
5
6

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

• Example Library Site: https://sites.google.com/site/opacgems/
• Helpful Resources:
  • https://sites.google.com/site/scriptsexamples/available-web-apps/awesome-tables/documentation/basic-configuration/available-filters
  • https://www.youtube.com/watch?v=7EeqPTjK38s