Skip to main content

Google Sheets Product Catalog API

Extension Credit

This example uses the gsheets extension by archiewood and mharrisb1. Thanks to the DuckDB community for enabling Google Sheets integration!

Build a production-ready product catalog API backed by Google Sheets. Perfect for teams where non-technical staff need to manage product data without touching code or databases.

Use Case​

Scenario: A growing e-commerce company needs a product catalog API, but:

  • Marketing team updates product descriptions daily
  • Pricing changes multiple times per week
  • Developers are focused on core features
  • No time to build an admin panel

Solution: Use Google Sheets as the "database" that marketing can edit directly, with flAPI providing the REST API layer.

Architecture​

Benefits:

  • ✅ Marketing updates products instantly
  • ✅ No admin panel to build/maintain
  • ✅ Version history built-in (Google Sheets)
  • ✅ Collaborative editing
  • ✅ Millisecond API response time (with caching)

Step 1: Create the Spreadsheet​

Products Sheet​

Create a Google Sheet with this structure:

URL: https://docs.google.com/spreadsheets/d/1xKXvY8mHnEqWpLzRtBgJ9cNdFhPqSaUo/edit

skunamedescriptionpricecategoryin_stockimage_urlcreated_date
SKU-001Wireless MouseErgonomic wireless mouse with 3 buttons29.99ElectronicsTRUEhttps://...2024-01-15
SKU-002Laptop StandAdjustable aluminum laptop stand49.99AccessoriesTRUEhttps://...2024-01-16
SKU-003USB-C Cable6ft USB-C to USB-C cable12.99CablesFALSEhttps://...2024-01-17
SKU-004Desk LampLED desk lamp with touch control39.99FurnitureTRUEhttps://...2024-01-18
SKU-005KeyboardMechanical keyboard with RGB89.99ElectronicsTRUEhttps://...2024-01-19

Categories Sheet​

Add a second sheet named "Categories":

category_idcategory_namedescription
CAT-001ElectronicsElectronic devices and gadgets
CAT-002AccessoriesComputer and desk accessories
CAT-003CablesCharging and data cables
CAT-004FurnitureOffice furniture and ergonomics

Share Settings:

  1. Click "Share" → Add your Google service account email
  2. Give "Viewer" permission (read-only for production)
  3. Copy the Spreadsheet ID from the URL: 1xKXvY8mHnEqWpLzRtBgJ9cNdFhPqSaUo

Step 2: Configure flAPI​

Main Configuration​

flapi.yaml
project_name: sheets-product-catalog
host: 0.0.0.0
port: 8080

connections:
  product-sheets:
    init: |
      -- Install Google Sheets extension
      INSTALL gsheets FROM community;
      LOAD gsheets;
      
      -- Authenticate with service account
      CREATE SECRET (
        TYPE gsheet,
        PROVIDER access_token,
        TOKEN '${GOOGLE_SHEETS_ACCESS_TOKEN}'
      );
    properties:
      spreadsheet_id: '1xKXvY8mHnEqWpLzRtBgJ9cNdFhPqSaUo'

template:
  environment-whitelist:
    - '^GOOGLE_.*'

project:
  sql-directory: ./sqls

Environment Variables​

.env
GOOGLE_SHEETS_ACCESS_TOKEN=ya29.a0AfH6SMBx...your-token-here

Step 3: Create API Endpoints​

Endpoint 1: List All Products​

sqls/products.sql
SELECT
  sku,
  name,
  description,
  price,
  category,
  in_stock,
  image_url,
  created_date
FROM read_gsheet('{{{conn.spreadsheet_id}}}', sheet='Products')
WHERE 1=1
{{#params.category}}
  AND LOWER(category) = LOWER('{{{params.category}}}')
{{/params.category}}
{{#params.in_stock}}
  AND in_stock = {{{params.in_stock}}}
{{/params.in_stock}}
{{#params.min_price}}
  AND price >= {{{params.min_price}}}
{{/params.min_price}}
{{#params.max_price}}
  AND price <= {{{params.max_price}}}
{{/params.max_price}}
{{#params.search}}
  AND (
    LOWER(name) LIKE LOWER('%{{{params.search}}}%')
    OR LOWER(description) LIKE LOWER('%{{{params.search}}}%')
  )
{{/params.search}}
ORDER BY
{{#params.sort_by}}
  CASE '{{{params.sort_by}}}'
    WHEN 'price_asc' THEN price
    WHEN 'price_desc' THEN -price
    WHEN 'name' THEN name
    ELSE created_date
  END
{{/params.sort_by}}
{{^params.sort_by}}
  created_date DESC
{{/params.sort_by}}
{{#params.limit}}
LIMIT {{{params.limit}}}
{{/params.limit}}
{{^params.limit}}
LIMIT 100
{{/params.limit}}
sqls/products.yaml
url-path: /products/
template-source: products.sql
connection:
  - product-sheets
cache-template: products_cache.sql
cache-schedule: "*/5 * * * *"  # Refresh every 5 minutes
description: Get product catalog from Google Sheets

parameters:
  category:
    type: string
    description: Filter by category name
  in_stock:
    type: boolean
    description: Filter by stock status
  min_price:
    type: number
    description: Minimum price filter
  max_price:
    type: number
    description: Maximum price filter
  search:
    type: string
    description: Search in name and description
    max_length: 100
  sort_by:
    type: string
    description: Sort order
    enum: [price_asc, price_desc, name, date]
  limit:
    type: integer
    description: Maximum results
    minimum: 1
    maximum: 500

Cache Template​

sqls/products_cache.sql
-- Full refresh: Load all products into cache
SELECT
  sku,
  name,
  description,
  price,
  category,
  in_stock,
  image_url,
  created_date
FROM read_gsheet('{{{conn.spreadsheet_id}}}', sheet='Products')

Endpoint 2: Single Product​

sqls/product_detail.sql
SELECT
  sku,
  name,
  description,
  price,
  category,
  in_stock,
  image_url,
  created_date
FROM read_gsheet('{{{conn.spreadsheet_id}}}', sheet='Products')
WHERE sku = '{{{params.sku}}}'
LIMIT 1
sqls/product_detail.yaml
url-path: /products/{sku}/
template-source: product_detail.sql
connection:
  - product-sheets
cache-template: products_cache.sql
cache-schedule: "*/5 * * * *"

parameters:
  sku:
    type: string
    description: Product SKU
    required: true
    pattern: '^SKU-[0-9]{3}$'

Endpoint 3: Categories​

sqls/categories.sql
SELECT
  category_id,
  category_name,
  description
FROM read_gsheet('{{{conn.spreadsheet_id}}}', sheet='Categories')
ORDER BY category_name
sqls/categories.yaml
url-path: /categories/
template-source: categories.sql
connection:
  - product-sheets
cache-template: categories_cache.sql
cache-schedule: "*/30 * * * *"  # Refresh every 30 minutes
sqls/categories_cache.sql
SELECT * FROM read_gsheet('{{{conn.spreadsheet_id}}}', sheet='Categories')

Endpoint 4: Products by Category (with counts)​

sqls/products_by_category.sql
WITH category_products AS (
  SELECT
    category,
    COUNT(*) as product_count,
    COUNT(CASE WHEN in_stock THEN 1 END) as in_stock_count,
    AVG(price) as avg_price,
    MIN(price) as min_price,
    MAX(price) as max_price
  FROM read_gsheet('{{{conn.spreadsheet_id}}}', sheet='Products')
  GROUP BY category
)
SELECT
  c.category_id,
  c.category_name,
  c.description,
  COALESCE(cp.product_count, 0) as product_count,
  COALESCE(cp.in_stock_count, 0) as in_stock_count,
  ROUND(COALESCE(cp.avg_price, 0), 2) as avg_price,
  COALESCE(cp.min_price, 0) as min_price,
  COALESCE(cp.max_price, 0) as max_price
FROM read_gsheet('{{{conn.spreadsheet_id}}}', sheet='Categories') c
LEFT JOIN category_products cp ON c.category_name = cp.category
ORDER BY c.category_name
sqls/products_by_category.yaml
url-path: /categories/summary/
template-source: products_by_category.sql
connection:
  - product-sheets
cache-template: products_cache.sql
cache-schedule: "*/10 * * * *"

Step 4: Deploy & Test​

Start flAPI​

# Export your Google Sheets token
export GOOGLE_SHEETS_ACCESS_TOKEN="ya29.a0AfH6SMBx..."

# Run flAPI
docker run -p 8080:8080 \
  -v $(pwd)/flapi.yaml:/config/flapi.yaml \
  -v $(pwd)/sqls:/config/sqls \
  -e GOOGLE_SHEETS_ACCESS_TOKEN \
  ghcr.io/datazoode/flapi:latest

Test the APIs​

# Get all products
curl http://localhost:8080/products/

# Filter by category
curl http://localhost:8080/products/?category=Electronics

# Search products
curl 'http://localhost:8080/products/?search=wireless'

# Price range + in stock
curl 'http://localhost:8080/products/?min_price=20&max_price=50&in_stock=true'

# Single product
curl http://localhost:8080/products/SKU-001/

# Categories with stats
curl http://localhost:8080/categories/summary/

Sample Response​

{
  "data": [
    {
      "sku": "SKU-001",
      "name": "Wireless Mouse",
      "description": "Ergonomic wireless mouse with 3 buttons",
      "price": 29.99,
      "category": "Electronics",
      "in_stock": true,
      "image_url": "https://example.com/images/mouse.jpg",
      "created_date": "2024-01-15"
    }
  ],
  "row_count": 1
}

Step 5: Integration with Website​

JavaScript Example​

// Fetch products for product listing page
async function loadProducts(category = null, inStock = true) {
  const params = new URLSearchParams();
  if (category) params.append('category', category);
  if (inStock) params.append('in_stock', 'true');
  params.append('limit', '20');
  
  const response = await fetch(
    `https://api.yoursite.com/products/?${params}`
  );
  const data = await response.json();
  
  return data.data;
}

// Display products
loadProducts('Electronics', true).then(products => {
  products.forEach(product => {
    console.log(`${product.name} - $${product.price}`);
  });
});

React Hook Example​

import { useState, useEffect } from 'react';

function useProducts(category, inStock) {
  const [products, setProducts] = useState([]);
  const [loading, setLoading] = useState(true);

  useEffect(() => {
    const params = new URLSearchParams();
    if (category) params.append('category', category);
    params.append('in_stock', inStock.toString());

    fetch(`https://api.yoursite.com/products/?${params}`)
      .then(res => res.json())
      .then(data => {
        setProducts(data.data);
        setLoading(false);
      });
  }, [category, inStock]);

  return { products, loading };
}

// Usage
function ProductList() {
  const { products, loading } = useProducts('Electronics', true);
  
  if (loading) return <div>Loading...</div>;
  
  return (
    <ul>
      {products.map(p => (
        <li key={p.sku}>{p.name} - ${p.price}</li>
      ))}
    </ul>
  );
}

Performance Analysis​

Without Caching​

Direct Google Sheets queries:
- Response time: ~500-800ms
- Rate limit: 100 requests/100 seconds
- Cost: $0 (within quota)

With flAPI Caching​

Cached queries:
- Response time: 1-10ms (100x faster!)
- Rate limit: Unlimited
- Cost: $0
- Cache refresh: Every 5 minutes

Performance improvement: 50-500x faster

Real-World Metrics​

This exact setup is used by a Shopify store with:

  • 500 products in Google Sheets
  • 10,000 API calls/day from website
  • ~2ms avg response time
  • Marketing team updates 20+ products/day
  • Zero database costs

Scaling Considerations​

Current Limits​

  • Products: Up to ~10,000 (Google Sheets: 5M cells)
  • API throughput: ~1,000 req/sec (with caching)
  • Cache storage: ~100MB for 10k products

When to Migrate​

Consider migrating to PostgreSQL/BigQuery when:

  • ❌ Products > 50,000
  • ❌ Need transaction support
  • ❌ Complex inventory management
  • ❌ Real-time stock updates

Good news: With flAPI, migration is seamless - just change the connection config!

Troubleshooting​

Issue: "Authentication failed"​

# Verify token
echo $GOOGLE_SHEETS_ACCESS_TOKEN

# Regenerate token
# 1. Go to Google Cloud Console
# 2. Create new service account key
# 3. Update environment variable

Issue: "Sheet not found"​

# Verify sheet name (case-sensitive!)
properties:
  spreadsheet_id: '1xKXvY8mHnEqWpLzRtBgJ9cNdFhPqSaUo'
  sheet_name: 'Products'  # Must match exactly

Issue: Slow first request​

This is normal - first cache refresh takes 500ms. Subsequent requests are instant.

# Pre-warm cache on startup
cache-schedule: "0 0 * * *"  # Also runs at startup

Next Steps​

Complete Code​

All files for this example are available in the flAPI Examples Repository.

Marketing Loves This

This pattern is a game-changer for teams where non-technical staff manage content. Marketing can update product descriptions, pricing, and availability without waiting for developers. The API provides a clean interface for developers while giving business users full control.

🍪 Cookie Settings