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
project-description: Product catalog API backed by Google Sheets

template:
  path: './sqls'
  environment-whitelist:
    - '^GOOGLE_.*'

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 '{{env.GOOGLE_SHEETS_ACCESS_TOKEN}}'
      );
    properties:
      spreadsheet_id: '1xKXvY8mHnEqWpLzRtBgJ9cNdFhPqSaUo'

Environment Variables​

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

Step 3: Create API Endpoints​

Endpoint 1: List All Products​

The API SQL reads from the cache table rather than hitting Google Sheets on every request. flAPI injects the cache catalog, schema, and table names into the template via the cache.* context, so the query always points at the materialized snapshot:

sqls/products.sql
SELECT
  sku,
  name,
  description,
  price,
  category,
  in_stock,
  image_url,
  created_date
FROM {{cache.catalog}}.{{cache.schema}}.{{cache.table}}
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}}

Upgrade the endpoint to an incremental-merge cache so flAPI only pulls rows from the sheet whose updated_at is newer than the last snapshot, and merges them on the sku primary key:

sqls/products.yaml
url-path: /products/
description: Get product catalog from Google Sheets

template-source: products.sql
connection:
  - product-sheets

cache:
  enabled: true
  table: products_cache
  schema: analytics
  schedule: 5m              # Refresh every 5 minutes
  template-file: products_cache.sql

  # Incremental-merge configuration
  primary-key: [sku]        # row identity for UPSERTs
  cursor:
    column: updated_at      # only pull rows newer than the last snapshot
    type: timestamp

  # DuckLake snapshot retention
  retention:
    keep-last-snapshots: 5
    max-snapshot-age: 14d

request:
  - field-name: category
    field-in: query
    description: Filter by category name
    required: false
    validators:
      - type: string
        max: 50

  - field-name: in_stock
    field-in: query
    description: Filter by stock status ("true" or "false")
    required: false
    validators:
      - type: enum
        allowedValues: ["true", "false"]

  - field-name: min_price
    field-in: query
    description: Minimum price filter
    required: false
    validators:
      - type: int
        min: 0

  - field-name: max_price
    field-in: query
    description: Maximum price filter
    required: false
    validators:
      - type: int
        min: 0

  - field-name: search
    field-in: query
    description: Search in name and description
    required: false
    validators:
      - type: string
        max: 100

  - field-name: sort_by
    field-in: query
    description: Sort order
    required: false
    validators:
      - type: enum
        allowedValues: [price_asc, price_desc, name, date]

  - field-name: limit
    field-in: query
    description: Maximum results
    required: false
    validators:
      - type: int
        min: 1
        max: 500

Cache Template​

The cache template runs on every scheduled tick. When primary-key and cursor are configured, flAPI exposes {{cache.previousSnapshotTimestamp}} to the template — use it to pull only the rows that changed since the last materialization:

sqls/products_cache.sql
-- Incremental merge: only pull rows updated since the last snapshot.
-- On the very first run the previous timestamp is empty, so we fall back
-- to a full sweep with a sentinel date.
SELECT
  sku,
  name,
  description,
  price,
  category,
  in_stock,
  image_url,
  created_date,
  updated_at
FROM read_gsheet('{{{conn.spreadsheet_id}}}', sheet='Products')
WHERE updated_at > COALESCE(
  TRY_CAST('{{{cache.previousSnapshotTimestamp}}}' AS TIMESTAMP),
  TIMESTAMP '1970-01-01 00:00:00'
)
Pick the right refresh mode

flAPI supports three refresh patterns: full refresh (no cursor, no primary key — rewrite the whole table each run), incremental append (cursor only — fast inserts for immutable event streams), and incremental merge (cursor + primary key — what we use here, perfect for mutable rows like a product catalog where prices and stock change in place). See Caching Strategy for the tradeoffs.

Endpoint 2: Single Product​

sqls/product_detail.sql
SELECT
  sku,
  name,
  description,
  price,
  category,
  in_stock,
  image_url,
  created_date
FROM {{cache.catalog}}.{{cache.schema}}.{{cache.table}}
WHERE sku = '{{{params.sku}}}'
LIMIT 1
sqls/product_detail.yaml
url-path: /products/:sku/
template-source: product_detail.sql
connection:
  - product-sheets

cache:
  enabled: true
  table: products_cache
  schema: analytics
  schedule: 5m
  template-file: products_cache.sql
  primary-key: [sku]
  cursor:
    column: updated_at
    type: timestamp

request:
  - field-name: sku
    field-in: path
    description: Product SKU
    required: true
    validators:
      - type: string
        regex: '^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:
  enabled: true
  table: categories_cache
  schema: analytics
  schedule: 30m              # Refresh every 30 minutes
  template-file: categories_cache.sql
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:
  enabled: true
  table: products_by_category_cache
  schema: analytics
  schedule: 10m
  template-file: products_cache.sql

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.

# Set a short schedule so the cache warms quickly after server start
cache:
  enabled: true
  table: products_cache
  schema: analytics
  schedule: 5m
  template-file: products_cache.sql

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