This blog post serves as a detailed appendix and resource hub for researchers, librarians, and developers interested in creating a no-cost, online searchable OPAC system tailored for small and medium academic libraries. The OPAC is built using Google Sheets and Google Apps Script, enabling easy deployment without paid hosting or complex infrastructure.
About the Project
Title: Developing a No-Cost, Searchable Online Public Access Catalogue (OPAC) for Small and Medium Libraries: A Design-Based Research Approach Using Google Apps Script
This project addresses the need for affordable, scalable library catalog solutions by leveraging freely available Google tools. It supports up to 9,000 book records and provides an intuitive web-based search interface.
Step-by-Step Guide to Building Your OPAC
Follow these instructions to build and deploy your own OPAC system:
Step 1: Prepare Your Library Data in Google Sheets
-
Create a Google Sheet with columns such as Title, Author, Subject, ISBN, Call Number, etc.
-
Enter your book records (recommended maximum: 9,000 rows).
Step 2: Open the Google Apps Script Editor
-
In Google Sheets, navigate to Extensions → Apps Script.
-
Rename your project (e.g., “Library OPAC”).
Step 3: Add Backend Logic (code.gs)
-
Copy and paste the backend script (provided below) into your Apps Script editor.
-
This script handles searching and caching of your library data.
Step 4: Create the Frontend User Interface (index.html)
-
In the Apps Script project, create a new file named
index.html. -
Paste the HTML and JavaScript code provided below.
-
This file creates a user-friendly search interface.
Step 5: Deploy Your Web App
-
Go to Deploy → Test deployments → New deployment.
-
Select Web app, add a description.
-
Set Execute as: Me, and Who has access: Anyone.
-
Deploy the app and copy the public URL.
Step 6: Share Your OPAC
-
Share the public URL with your library users to allow easy access to your searchable catalogue.
Code for Frontend Interface (index.html)
<!DOCTYPE html>
<html>
<head>
<title>XIME-Chennai - Library OPAC</title>
<script>
function searchLibrary() {
var query = document.getElementById("searchInput").value;
var category = document.getElementById("searchCategory").value;
google.script.run.withSuccessHandler(displayResults).searchBook(query, category);
}
function displayResults(results) {
var resultDiv = document.getElementById("results");
resultDiv.innerHTML = "";
if (results.length === 0) {
resultDiv.innerHTML = "<p>No results found.</p>";
return;
}
var table = "<table border='1'><tr><th>Title</th><th>Author</th><th>Subject</th><th>ISBN</th></tr>";
results.forEach(row => {
table += `<tr><td>${row[0]}</td><td>${row[1]}</td><td>${row[2]}</td><td>${row[3]}</td></tr>`;
});
table += "</table>";
resultDiv.innerHTML = table;
}
</script>
<style>
body {
font-family: Arial, sans-serif;
text-align: center;
}
.search-container {
margin: 20px auto;
width: 50%;
}
select, input {
padding: 10px;
font-size: 16px;
}
.search-btn {
background-color: #007bff;
color: white;
border: none;
padding: 10px 15px;
cursor: pointer;
}
</style>
</head>
<body>
<h2>XIME-Chennai - Library OPAC</h2>
<div class="search-container">
<select id="searchCategory">
<option value="title">Title</option>
<option value="author">Author</option>
<option value="subject">Subject</option>
<option value="isbn">ISBN</option>
<option value="issn">ISSN</option>
<option value="series">Series</option>
<option value="call_number">Call Number</option>
<option value="accession_no">Accession No.</option>
</select>
<input type="text" id="searchInput" placeholder="Enter search term">
<button class="search-btn" onclick="searchLibrary()">🔍</button>
</div>
<div id="results"></div>
</body>
</html>
Code for Backend Logic (code.gs)
function doGet() {
return HtmlService.createHtmlOutputFromFile("index");
}
function searchBook(query, category) {
var cache = CacheService.getScriptCache();
var cachedData = cache.get("library_data");
if (cachedData) {
var data = JSON.parse(cachedData);
} else {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
var data = sheet.getDataRange().getValues();
cache.put("library_data", JSON.stringify(data), 600); // Cache for 10 minutes
}
if (!query || !category) return [];
var columns = {
"title": 0, "author": 1, "subject": 2, "isbn": 3, "issn": 4,
"series": 5, "call_number": 6, "accession_no": 7
};
var colIndex = columns[category];
if (colIndex === undefined) return [];
query = query.toLowerCase();
// Fast search using filter
var results = data.slice(1).filter(row => row[colIndex].toString().toLowerCase().includes(query));
return results.slice(0, 50); // Limit results to 50 for performance
}
