Building a Product Catalog API with Google Sheets in 5 Minutes

Need a quick backend for your e-commerce project? In this tutorial, we’ll build a complete product catalog API using nothing but Google Sheets and SheetsJSON. By the end, you’ll have a RESTful API returning product data that you can use in any application.

What We’re Building

A product catalog API that supports:

Listing all products
Filtering by category
Sorting by price
Pagination for large catalogs
Real-time updates when you edit the spreadsheet

Total time: About 5 minutes.

Step 1: Create Your Product Spreadsheet

Open Google Sheets and create a new spreadsheet. Set up your headers in the first row:

id	sku	name	description	price	sale_price	category	brand	in_stock	image_url	created_at

Understanding the Columns

id – Unique identifier (number)
sku – Stock keeping unit (text)
name – Product name (text)
description – Product description (text)
price – Regular price (number)
sale_price – Discounted price, if any (number or empty)
category – Product category (text)
brand – Manufacturer/brand (text)
in_stock – Availability (true/false)
image_url – URL to product image (text)
created_at – When the product was added (date)

Step 2: Add Sample Data

Let’s populate the sheet with some products:

| id | sku      | name              | description                    | price  | sale_price | category    | brand     | in_stock | image_url                           | created_at |
|----|----------|-------------------|--------------------------------|--------|------------|-------------|-----------|----------|-------------------------------------|------------|
| 1  | ELEC-001 | Wireless Mouse    | Ergonomic wireless mouse       | 29.99  |            | electronics | TechCo    | true     | https://example.com/mouse.jpg       | 2024-01-15 |
| 2  | ELEC-002 | Mechanical KB     | RGB mechanical keyboard        | 149.99 | 129.99     | electronics | TechCo    | true     | https://example.com/keyboard.jpg    | 2024-01-16 |
| 3  | ELEC-003 | USB-C Hub         | 7-port USB-C hub               | 49.99  |            | electronics | ConnectPro| true     | https://example.com/hub.jpg         | 2024-01-17 |
| 4  | HOME-001 | Desk Lamp         | LED desk lamp with dimmer      | 39.99  | 34.99      | home        | BrightLife| true     | https://example.com/lamp.jpg        | 2024-01-18 |
| 5  | HOME-002 | Plant Pot         | Ceramic pot with drainage      | 24.99  |            | home        | GreenSpace| false    | https://example.com/pot.jpg         | 2024-01-19 |

Step 3: Connect to SheetsJSON

Go to SheetsJSON and create an account
Click “Connect Google Sheet”
Authorize access to your Google account
Select the product catalog spreadsheet you just created
Choose the sheet (usually “Sheet1”)

Within seconds, you’ll receive your unique API endpoint:

https://api.sheetsjson.com/api/sheets/your-account/products

Step 4: Test Your API

Let’s make some API calls. You can use curl, Postman, or any HTTP client.

Get All Products

curl "https://api.sheetsjson.com/api/sheets/your-account/products" \
  -H "Authorization: Bearer YOUR_API_KEY"

Response:

{
  "data": [
    {
      "id": 1,
      "sku": "ELEC-001",
      "name": "Wireless Mouse",
      "description": "Ergonomic wireless mouse",
      "price": 29.99,
      "sale_price": null,
      "category": "electronics",
      "brand": "TechCo",
      "in_stock": true,
      "image_url": "https://example.com/mouse.jpg",
      "created_at": "2025-06-15"
    },
    // ... more products
  ],
  "meta": {
    "total": 5,
    "page": 1,
    "per_page": 25
  }
}

Filter by Category

curl "https://api.sheetsjson.com/api/sheets/your-account/products?filter[category]=electronics" \
  -H "Authorization: Bearer YOUR_API_KEY"

Filter by Stock Status

curl "https://api.sheetsjson.com/api/sheets/your-account/products?filter[in_stock]=true" \
  -H "Authorization: Bearer YOUR_API_KEY"

Sort by Price (Low to High)

curl "https://api.sheetsjson.com/api/sheets/your-account/products?sort=price" \
  -H "Authorization: Bearer YOUR_API_KEY"

Paginate Results

curl "https://api.sheetsjson.com/api/sheets/your-account/products?limit=10&offset=0" \
  -H "Authorization: Bearer YOUR_API_KEY"

Step 5: Use in Your Application

Here’s how to consume your new API in different languages:

JavaScript (Fetch)

async function getProducts(category = null) {
  const url = new URL('https://api.sheetsjson.com/api/sheets/your-account/products');
  
  if (category) {
    url.searchParams.append('filter[category]', category);
  }
  
  const response = await fetch(url, {
    headers: {
      'Authorization': 'Bearer YOUR_API_KEY'
    }
  });
  
  const { data } = await response.json();
  return data;
}

// Usage
const electronics = await getProducts('electronics');
console.log(electronics);

Python (Requests)

import requests

def get_products(category=None):
    url = 'https://api.sheetsjson.com/api/sheets/your-account/products'
    headers = {'Authorization': 'Bearer YOUR_API_KEY'}
    params = {}
    
    if category:
        params['filter[category]'] = category
    
    response = requests.get(url, headers=headers, params=params)
    return response.json()['data']

# Usage
electronics = get_products('electronics')
print(electronics)

React Component Example

import { useState, useEffect } from 'react';

function ProductCatalog() {
  const [products, setProducts] = useState([]);
  const [loading, setLoading] = useState(true);
  
  useEffect(() => {
    fetch('https://api.sheetsjson.com/api/sheets/your-account/products', {
      headers: { 'Authorization': 'Bearer YOUR_API_KEY' }
    })
      .then(res => res.json())
      .then(({ data }) => {
        setProducts(data);
        setLoading(false);
      });
  }, []);
  
  if (loading) return <div>Loading...</div>;
  
  return (
    <div className="product-grid">
      {products.map(product => (
        <div key={product.id} className="product-card">
          <img src={product.image_url} alt={product.name} />
          <h3>{product.name}</h3>
          <p>{product.description}</p>
          <div className="price">
            {product.sale_price ? (
              <>
                <span className="original">${product.price}</span>
                <span className="sale">${product.sale_price}</span>
              </>
            ) : (
              <span>${product.price}</span>
            )}
          </div>
          {!product.in_stock && <span className="out-of-stock">Out of Stock</span>}
        </div>
      ))}
    </div>
  );
}

Pro Tips

1. Use Data Validation in Sheets

Set up dropdowns for the category column to prevent typos:

Select the category column
Data → Data validation
Criteria: List of items
Enter: electronics, home, clothing, accessories

2. Add Computed Columns

Use formulas for derived data:

// Discount percentage column
=IF(F2="", "", ROUND((E2-F2)/E2*100, 0) & "% off")

3. Set Up Conditional Formatting

Highlight out-of-stock items in red:

Select all data rows
Format → Conditional formatting
Format cells if: Custom formula =$I2=FALSE
Set red background

4. Use Multiple Sheets for Different Data

Create separate sheets for:

Products
Categories
Brands
Inventory

Each becomes its own API endpoint!

What’s Next?

Now that you have a working product API, you could:

Build a storefront with React, Vue, or Svelte
Create a mobile app with React Native or Flutter
Set up a product comparison tool
Build an inventory management dashboard
Integrate with Zapier for automated workflows

Updating Your Catalog

The best part? To update your catalog, just edit the Google Sheet. Changes appear in your API within seconds:

Add a new row for a new product
Update a price in the sheet
Mark items as out of stock by changing in_stock to false

No code changes, no deployments, no database migrations.

Conclusion

In just 5 minutes, you’ve built a fully functional product catalog API. It supports filtering, sorting, pagination, and real-time updates. This approach is perfect for:

MVPs and prototypes
Small e-commerce sites
Internal tools
Hackathon projects
Learning and experimentation

Get started with SheetsJSON and build your own API today. Have questions? Check out our documentation or reach out to our support team.

Happy building! 🚀