Tutorial: Build a Product Catalog

In this tutorial, you’ll learn how to build a complete product catalog using SheetsJSON. By the end, you’ll have a working product listing with filtering, sorting, and the ability to add new products.

Time to complete: 20-30 minutes

What you’ll build:

A product listing page with filtering and sorting
A product detail view
An admin form to add new products (using Write API)

Prerequisites

A SheetsJSON account (sign up here)
Basic knowledge of HTML and JavaScript
A Google Sheet with product data

Step 1: Set Up Your Google Sheet

Create a new Google Sheet with the following columns:

id	name	description	price	category	image_url	in_stock
1	Wireless Headphones	Premium noise-canceling headphones	199.99	Electronics	https://example.com/headphones.jpg	true
2	Coffee Maker	12-cup programmable coffee maker	79.99	Kitchen	https://example.com/coffee.jpg	true
3	Running Shoes	Lightweight athletic running shoes	129.99	Sports	https://example.com/shoes.jpg	false

Tip: Use lowercase column headers with underscores. This makes your JSON keys cleaner and easier to work with.

Step 2: Connect Your Sheet to SheetsJSON

Go to your SheetsJSON Dashboard
Click Connect Sheet
Select your product catalog spreadsheet
Note your API endpoint URL: https://api.sheetsjson.com/api/sheets/{account}/{sheet}

Step 3: Create the Product Listing

Let’s build a simple product catalog page. Create an HTML file:

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>Product Catalog</title>
  <script src="https://cdn.tailwindcss.com"></script>
</head>
<body class="bg-gray-100 min-h-screen">
  <div class="container mx-auto px-4 py-8">
    <h1 class="text-3xl font-bold text-gray-900 mb-8">Product Catalog</h1>
    
<!-- Filters -->

    <div class="bg-white rounded-lg shadow p-4 mb-6">
      <div class="flex flex-wrap gap-4">
        <select id="category-filter" class="border rounded-lg px-4 py-2">
          <option value="">All Categories</option>
          <option value="Electronics">Electronics</option>
          <option value="Kitchen">Kitchen</option>
          <option value="Sports">Sports</option>
        </select>
        
        <select id="sort-select" class="border rounded-lg px-4 py-2">
          <option value="">Sort by</option>
          <option value="price">Price: Low to High</option>
          <option value="-price">Price: High to Low</option>
          <option value="name">Name: A-Z</option>
        </select>
        
        <label class="flex items-center gap-2">
          <input type="checkbox" id="in-stock-filter" class="rounded">
          <span>In Stock Only</span>
        </label>
      </div>
    </div>
    
<!-- Products Grid -->

    <div id="products-grid" class="grid grid-cols-1 md:grid-cols-2 lg:grid-cols-3 gap-6">
<!-- Products will be loaded here -->

    </div>
    
<!-- Loading State -->

    <div id="loading" class="text-center py-12">
      <div class="animate-spin rounded-full h-12 w-12 border-b-2 border-blue-600 mx-auto"></div>
      <p class="mt-4 text-gray-600">Loading products...</p>
    </div>
  </div>

  <script>
    // Configuration
    const API_BASE = 'https://api.sheetsjson.com/api/sheets';
    const ACCOUNT_SLUG = 'your-account';  // Replace with your account slug
    const SHEET_SLUG = 'products';        // Replace with your sheet slug
    const API_KEY = 'your-api-key';       // Replace with your API key (if required)
    
    // State
    let allProducts = [];
    
    // Fetch products from SheetsJSON
    async function fetchProducts(params = {}) {
      const url = new URL(`${API_BASE}/${ACCOUNT_SLUG}/${SHEET_SLUG}`);
      
      // Add query parameters
      Object.entries(params).forEach(([key, value]) => {
        if (value) url.searchParams.set(key, value);
      });
      
      try {
        const response = await fetch(url, {
          headers: {
            'Authorization': `Bearer ${API_KEY}`
          }
        });
        
        if (!response.ok) {
          throw new Error(`HTTP error! status: ${response.status}`);
        }
        
        const data = await response.json();
        return data.data || [];
      } catch (error) {
        console.error('Failed to fetch products:', error);
        return [];
      }
    }
    
    // Render a single product card
    function renderProductCard(product) {
      const inStock = product.in_stock === 'true' || product.in_stock === true;
      
      return `
        <div class="bg-white rounded-lg shadow overflow-hidden hover:shadow-lg transition-shadow">
          <img 
            src="${product.image_url || 'https://via.placeholder.com/400x300'}" 
            alt="${product.name}"
            class="w-full h-48 object-cover"
            onerror="this.src='https://via.placeholder.com/400x300?text=No+Image'"
          >
          <div class="p-4">
            <span class="text-sm text-blue-600 font-medium">${product.category}</span>
            <h3 class="text-lg font-semibold text-gray-900 mt-1">${product.name}</h3>
            <p class="text-gray-600 text-sm mt-2 line-clamp-2">${product.description}</p>
            <div class="flex items-center justify-between mt-4">
              <span class="text-2xl font-bold text-gray-900">$${parseFloat(product.price).toFixed(2)}</span>
              <span class="${inStock ? 'text-green-600' : 'text-red-600'} text-sm font-medium">
                ${inStock ? '✓ In Stock' : '✗ Out of Stock'}
              </span>
            </div>
            <button 
              class="w-full mt-4 px-4 py-2 rounded-lg font-medium transition-colors
                ${inStock 
                  ? 'bg-blue-600 text-white hover:bg-blue-700' 
                  : 'bg-gray-200 text-gray-500 cursor-not-allowed'}"
              ${!inStock ? 'disabled' : ''}
            >
              ${inStock ? 'Add to Cart' : 'Notify Me'}
            </button>
          </div>
        </div>
      `;
    }
    
    // Render all products
    function renderProducts(products) {
      const grid = document.getElementById('products-grid');
      const loading = document.getElementById('loading');
      
      loading.classList.add('hidden');
      
      if (products.length === 0) {
        grid.innerHTML = `
          <div class="col-span-full text-center py-12">
            <p class="text-gray-600">No products found matching your criteria.</p>
          </div>
        `;
        return;
      }
      
      grid.innerHTML = products.map(renderProductCard).join('');
    }
    
    // Apply filters and sorting
    async function applyFilters() {
      const category = document.getElementById('category-filter').value;
      const sort = document.getElementById('sort-select').value;
      const inStockOnly = document.getElementById('in-stock-filter').checked;
      
      const params = {};
      
      if (category) {
        params['filter[category]'] = category;
      }
      
      if (inStockOnly) {
        params['filter[in_stock]'] = 'true';
      }
      
      if (sort) {
        params['sort'] = sort;
      }
      
      const products = await fetchProducts(params);
      renderProducts(products);
    }
    
    // Initialize
    async function init() {
      // Set up event listeners
      document.getElementById('category-filter').addEventListener('change', applyFilters);
      document.getElementById('sort-select').addEventListener('change', applyFilters);
      document.getElementById('in-stock-filter').addEventListener('change', applyFilters);
      
      // Initial load
      await applyFilters();
    }
    
    // Start the app
    init();
  </script>
</body>
</html>

Step 4: Add Search Functionality

Let’s enhance our catalog with a search feature. Add this to the filters section:

<input 
  type="text" 
  id="search-input" 
  placeholder="Search products..."
  class="border rounded-lg px-4 py-2 flex-grow"
>

And update the JavaScript to include search:

// Add debounce function for search
function debounce(func, wait) {
  let timeout;
  return function executedFunction(...args) {
    const later = () => {
      clearTimeout(timeout);
      func(...args);
    };
    clearTimeout(timeout);
    timeout = setTimeout(later, wait);
  };
}

// Update applyFilters to include search
async function applyFilters() {
  const category = document.getElementById('category-filter').value;
  const sort = document.getElementById('sort-select').value;
  const inStockOnly = document.getElementById('in-stock-filter').checked;
  const searchTerm = document.getElementById('search-input').value;
  
  const params = {};
  
  if (category) {
    params['filter[category]'] = category;
  }
  
  if (inStockOnly) {
    params['filter[in_stock]'] = 'true';
  }
  
  if (sort) {
    params['sort'] = sort;
  }
  
  // Search by name (contains)
  if (searchTerm) {
    params['filter[name][contains]'] = searchTerm;
  }
  
  const products = await fetchProducts(params);
  renderProducts(products);
}

// In init(), add search listener with debounce
document.getElementById('search-input').addEventListener(
  'input', 
  debounce(applyFilters, 300)
);

Step 5: Add New Products (Write API)

If you have a Pro plan, you can add new products directly from your application. Create an admin form:

<!-- Add Product Form (Admin) -->

<div id="admin-panel" class="bg-white rounded-lg shadow p-6 mb-6">
  <h2 class="text-xl font-semibold mb-4">Add New Product</h2>
  <form id="add-product-form" class="grid grid-cols-1 md:grid-cols-2 gap-4">
    <input type="text" name="name" placeholder="Product Name" required
           class="border rounded-lg px-4 py-2">
    <input type="text" name="category" placeholder="Category" required
           class="border rounded-lg px-4 py-2">
    <input type="number" name="price" placeholder="Price" step="0.01" required
           class="border rounded-lg px-4 py-2">
    <input type="url" name="image_url" placeholder="Image URL"
           class="border rounded-lg px-4 py-2">
    <textarea name="description" placeholder="Description" required
              class="border rounded-lg px-4 py-2 md:col-span-2"></textarea>
    <label class="flex items-center gap-2">
      <input type="checkbox" name="in_stock" checked class="rounded">
      <span>In Stock</span>
    </label>
    <button type="submit" 
            class="bg-green-600 text-white px-6 py-2 rounded-lg hover:bg-green-700 transition-colors">
      Add Product
    </button>
  </form>
  <div id="form-message" class="mt-4 hidden"></div>
</div>

Add the JavaScript to handle form submission:

// Add new product
async function addProduct(productData) {
  const url = `${API_BASE}/${ACCOUNT_SLUG}/${SHEET_SLUG}`;
  
  try {
    const response = await fetch(url, {
      method: 'POST',
      headers: {
        'Authorization': `Bearer ${API_KEY}`,
        'Content-Type': 'application/json'
      },
      body: JSON.stringify(productData)
    });
    
    if (!response.ok) {
      const error = await response.json();
      throw new Error(error.error || 'Failed to add product');
    }
    
    return await response.json();
  } catch (error) {
    console.error('Failed to add product:', error);
    throw error;
  }
}

// Handle form submission
document.getElementById('add-product-form').addEventListener('submit', async (e) => {
  e.preventDefault();
  
  const form = e.target;
  const formMessage = document.getElementById('form-message');
  const submitButton = form.querySelector('button[type="submit"]');
  
  // Gather form data
  const formData = new FormData(form);
  const productData = {
    id: Date.now().toString(), // Generate a simple ID
    name: formData.get('name'),
    description: formData.get('description'),
    price: formData.get('price'),
    category: formData.get('category'),
    image_url: formData.get('image_url') || '',
    in_stock: formData.get('in_stock') ? 'true' : 'false'
  };
  
  // Disable button and show loading
  submitButton.disabled = true;
  submitButton.textContent = 'Adding...';
  
  try {
    await addProduct(productData);
    
    // Show success message
    formMessage.className = 'mt-4 p-4 bg-green-100 text-green-800 rounded-lg';
    formMessage.textContent = 'Product added successfully!';
    formMessage.classList.remove('hidden');
    
    // Reset form
    form.reset();
    
    // Refresh product list
    await applyFilters();
    
    // Hide message after 3 seconds
    setTimeout(() => {
      formMessage.classList.add('hidden');
    }, 3000);
    
  } catch (error) {
    // Show error message
    formMessage.className = 'mt-4 p-4 bg-red-100 text-red-800 rounded-lg';
    formMessage.textContent = `Error: ${error.message}`;
    formMessage.classList.remove('hidden');
  } finally {
    submitButton.disabled = false;
    submitButton.textContent = 'Add Product';
  }
});

Step 6: Add Loading States and Error Handling

Improve user experience with better loading and error states:

// Enhanced fetch with error handling
async function fetchProducts(params = {}) {
  const grid = document.getElementById('products-grid');
  const loading = document.getElementById('loading');
  
  // Show loading state
  loading.classList.remove('hidden');
  grid.innerHTML = '';
  
  const url = new URL(`${API_BASE}/${ACCOUNT_SLUG}/${SHEET_SLUG}`);
  
  Object.entries(params).forEach(([key, value]) => {
    if (value) url.searchParams.set(key, value);
  });
  
  try {
    const response = await fetch(url, {
      headers: {
        'Authorization': `Bearer ${API_KEY}`
      }
    });
    
    if (response.status === 429) {
      const retryAfter = response.headers.get('Retry-After') || 60;
      throw new Error(`Rate limited. Please try again in ${retryAfter} seconds.`);
    }
    
    if (!response.ok) {
      const error = await response.json();
      throw new Error(error.error || `HTTP error: ${response.status}`);
    }
    
    const data = await response.json();
    return data.data || [];
    
  } catch (error) {
    console.error('Failed to fetch products:', error);
    
    // Show error in grid
    grid.innerHTML = `
      <div class="col-span-full text-center py-12">
        <div class="text-red-600 mb-2">⚠️ Error loading products</div>
        <p class="text-gray-600">${error.message}</p>
        <button onclick="applyFilters()" class="mt-4 px-4 py-2 bg-blue-600 text-white rounded-lg">
          Try Again
        </button>
      </div>
    `;
    
    return [];
  } finally {
    loading.classList.add('hidden');
  }
}

Complete Example

Here’s a link to the complete working example:

View on CodePen (coming soon)
Download source code (coming soon)

Next Steps

Now that you have a working product catalog, you can:

Add pagination — Use limit and offset parameters for large catalogs
Implement a cart — Store selections in localStorage and sync with a “cart” sheet
Add product detail pages — Create modal or separate pages for full product info
Set up webhooks — Get notified when products are added or updated

Troubleshooting

Products not loading?

Check browser console for errors
Verify your API endpoint URL is correct
Ensure your API key is valid (if authentication is enabled)
Check that your Google Sheet is connected in the dashboard

Write API not working?

Confirm you have a Pro plan or higher
Check that your API key has write permissions
Verify the JSON structure matches your sheet columns

Rate limit errors?

Implement caching and use the Retry-After header. See Rate Limits for details.

Tutorial: Product Catalog