I want to create a simple web app to search a database.
google app-script based webapp is not giving results.
The issue is that the frontend is receiving null from the backend, even though the backend is returning valid results (as shown in the Execution Log).
i have tried with hard coded result, that it is showing, but not result from the search.
My index.html file is as below.
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<style>
body { font-family: Arial, sans-serif; margin: 20px; }
input, select, button { margin: 5px; padding: 10px; }
table { width: 100%; border-collapse: collapse; margin-top: 20px; }
th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
th { background-color: #f2f2f2; }
</style>
</head>
<body>
<h1>Book Search</h1>
<input type="text" id="searchTerm" placeholder="Enter search term">
<select id="category">
<option value="">Select a category</option> <!-- Default option -->
</select>
<button onclick="search()">Search</button>
<table id="results">
<thead>
<tr>
<th>Timestamp</th>
<th>Date of Addition</th>
<th>Acc No</th>
<th>Book Title</th>
<th>Author</th>
<th>Book Type</th>
<th>Volume</th>
<th>Publication House</th>
<th>Publication Year</th>
<th>Pages</th>
<th>Bill No</th>
<th>Bill Date</th>
<th>Price</th>
<th>Condition</th>
<th>Subject</th>
<th>Almirah</th>
<th>Rack</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
<script>
// Load categories into dropdown
google.script.run
.withSuccessHandler((categories) => {
console.log('Categories from backend:', categories); // Log categories for debugging
const dropdown = document.getElementById('category');
if (!categories || categories.length === 0) {
console.error('No categories found or categories are empty');
return;
}
// Add categories to the dropdown
categories.forEach(([category]) => {
const option = document.createElement('option');
option.value = category;
option.text = category;
dropdown.appendChild(option);
});
})
.withFailureHandler((error) => {
console.error('Error fetching categories:', error);
alert('An error occurred while loading categories. Please check the console for details.');
})
.getCategories();
// Perform search
function search() {
const searchTerm = document.getElementById('searchTerm').value;
const category = document.getElementById('category').value;
if (!searchTerm || !category) {
alert('Please enter a search term and select a category.');
return;
}
console.log('Sending request to backend with:', { searchTerm, category }); // Log request data
google.script.run
.withSuccessHandler((results) => {
console.log('Results from backend:', results); // Log results for debugging
const tbody = document.querySelector('#results tbody');
tbody.innerHTML = ''; // Clear previous results
if (!results || results.length === 0) {
tbody.innerHTML = '<tr><td colspan="17">No results found.</td></tr>';
return;
}
// Ensure results is an array
if (Array.isArray(results)) {
results.forEach(row => {
const tr = document.createElement('tr');
row.forEach(cell => {
const td = document.createElement('td');
td.textContent = cell;
tr.appendChild(td);
});
tbody.appendChild(tr);
});
} else {
console.error('Invalid results format:', results);
tbody.innerHTML = '<tr><td colspan="17">Invalid results format.</td></tr>';
}
})
.withFailureHandler((error) => {
console.error('Error:', error);
alert('An error occurred. Please check the console for details.');
})
.searchBooks(category, searchTerm);
}
</script>
</body>
</html>
My code.gs file is as below
function doGet() {
return HtmlService.createHtmlOutputFromFile('index');
}
function getCategories() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Category');
if (!sheet) {
console.error('Category sheet not found');
return [];
}
const data = sheet.getRange(2, 1, sheet.getLastRow() - 1, 2).getValues(); // Skip header row
console.log('Categories:', data); // Log categories for debugging
return data;
}
function searchBooks(category, searchTerm) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const categorySheet = ss.getSheetByName('Category');
const booksSheet = ss.getSheetByName('Books');
if (!categorySheet || !booksSheet) {
console.error('Sheets not found');
return []; // Return an empty array if sheets are not found
}
// Get column number for the selected category
const categories = categorySheet.getRange(2, 1, categorySheet.getLastRow() - 1, 2).getValues();
const columnNumber = categories.find(([cat]) => cat === category)?.[1];
if (!columnNumber) {
console.error('Invalid category:', category);
return []; // Return an empty array if category is invalid
}
// Search the Books sheet
const booksData = booksSheet.getRange(2, 1, booksSheet.getLastRow() - 1, booksSheet.getLastColumn()).getValues();
const results = booksData
.filter(row => row[columnNumber - 1].toString().toLowerCase().includes(searchTerm.toLowerCase()))
.map(row => row.map(cell => cell || '')); // Replace null/undefined with empty strings
console.log('Search results:', results); // Log results for debugging
return results;
}
THIS IS MY GOOGLE SHEET FILE
https://docs.google.com/spreadsheets/d/1jBkZie2i3Xnt1DGYL0ChAZmSgbseq_nE6V7FzqLLJ3g/edit?usp=sharing