Google Apps Script web application returning null instead of valid results

Backend
1

I am working on a web application with Google Apps Script that accesses a database. However, I am encountering an issue where the frontend receives null from the backend even though the backend is logging valid results.

When I use hardcoded values, the app functions correctly, but it fails to return search results from the actual database.

Below is the content of my index.html file:

<!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>
      google.script.run
        .withSuccessHandler((categories) => {
          const dropdown = document.getElementById('category');

          if (!categories || categories.length === 0) {
            console.error('No categories found or categories are empty');
            return;
          }

          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();

      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;
        }

        google.script.run
          .withSuccessHandler((results) => {
            const tbody = document.querySelector('#results tbody');
            tbody.innerHTML = '';

            if (!results || results.length === 0) {
              tbody.innerHTML = '<tr><td colspan="17">No results found.</td></tr>';
              return;
            }

            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>

Here is my code.gs file:

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();
  return data;
}

function searchBooks(category, searchTerm) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const categorySheet = ss.getSheetByName('Category');
  const booksSheet = ss.getSheetByName('Books');

  if (!categorySheet || !booksSheet) {
    return [];
  }

  const categories = categorySheet.getRange(2, 1, categorySheet.getLastRow() - 1, 2).getValues();
  const columnNumber = categories.find(([cat]) => cat === category)?.[1];

  if (!columnNumber) {
    return [];
  }

  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 || ''));

  return results;
}

Additionally, you can look at my Google Sheet file, which includes the data I am working with.

2

classic serialization issue. google apps script chokes when passing certain data types or empty values back to the frontend. wrap your return statement in try-catch to catch any silent errors. also check for formulas or dates in your cells - they’re notorious for breaking serialization.

3

I’ve hit this exact issue with Google Apps Script web apps - frontend gets null even though the backend runs fine. It’s usually how GAS handles async operations and data serialization that causes problems. My guess is your searchBooks function is trying to access array elements that don’t exist, or your sheet has values that can’t be serialized properly. Add some error handling to searchBooks and make sure you’re converting all cell values to strings before returning them. Also double-check that your spreadsheet actually has data in the ranges you’re expecting - empty sheets or missing rows will give you null returns even when your code looks right. Throw some console.log statements right before your return in searchBooks to see what’s actually getting processed.

4

Hmm interesting - have you checked if your columnNumber variable is resolving correctly? The find() method returns undefined when the comparison fails. Try logging the exact value of columnNumber and categories array right before the filter. Also curious about your sheet structure - any merged cells or weird formatting that might mess with the range selection?