Google Sheets as Database

Extension Credit

This guide uses the gsheets extension by archiewood and mharrisb1. Thanks to the DuckDB community for this excellent extension that enables seamless Google Sheets integration!

Turn Google Sheets into production-ready REST APIs. Perfect for non-technical teams, rapid prototyping, and collaborative data management - no database setup required.

Why Use Google Sheets with flAPI?

"The database your marketing team can actually use."

Google Sheets is the world's most accessible "database":

• No setup - just create a spreadsheet
• Collaborative - multiple people can edit simultaneously
• Familiar - everyone knows how to use spreadsheets
• Visual - see your data, no SQL queries needed
• Version history - built-in audit trail

Real-World Use Cases

Content Management for Marketing

Marketing teams manage content without developer intervention:

• Blog post metadata (titles, authors, tags)
• Product descriptions and pricing
• Landing page copy variants
• Campaign tracking parameters

Form & Survey Backends

Google Forms → Google Sheets → flAPI → Your App:

• Customer feedback collection
• Event registrations
• Product waitlists
• Survey responses

Rapid Prototyping

Build MVP APIs in minutes:

• No database provisioning
• No schema migrations
• Just update the spreadsheet

Team Collaboration

Non-technical stakeholders contribute directly:

• Support team manages FAQ data
• Sales team updates product specs
• HR maintains team directory

Installation

The Google Sheets extension requires authentication:

flapi.yaml

connections:
  my-sheets:
    init: |
      INSTALL gsheets FROM community;
      LOAD gsheets;
      -- Authenticate (opens browser for OAuth)
      CREATE SECRET (TYPE gsheet);

Alternative: Access Token Authentication

For production deployments, use a service account token:

flapi.yaml

connections:
  my-sheets:
    init: |
      INSTALL gsheets FROM community;
      LOAD gsheets;
      CREATE SECRET (
        TYPE gsheet,
        PROVIDER access_token,
        TOKEN '${GOOGLE_ACCESS_TOKEN}'
      );

Get your access token:

1. Create a Google Cloud project
2. Enable Google Sheets API
3. Create service account credentials
4. Download JSON key and exchange for an access token

Configuration Examples

Basic Configuration

flapi.yaml

project-name: sheets-api

connections:
  google-sheets:
    init: |
      INSTALL gsheets FROM community;
      LOAD gsheets;
      CREATE SECRET (TYPE gsheet);
    properties:
      spreadsheet_id: '1A2B3C4D5E6F7G8H9I0J'

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

The spreadsheet_id is a DuckDB gsheets extension property — see upstream docs for the full list.

Multiple Sheets

flapi.yaml

connections:
  # Product catalog
  products-sheet:
    init: |
      INSTALL gsheets FROM community;
      LOAD gsheets;
      CREATE SECRET (TYPE gsheet);
    properties:
      spreadsheet_id: '1A2B3C4D5E6F7G8H9I0J'
      sheet_name: 'Products'

  # Customer data
  customers-sheet:
    init: |
      LOAD gsheets;  # Already installed above
      CREATE SECRET (TYPE gsheet);
    properties:
      spreadsheet_id: '9J0I8H7G6F5E4D3C2B1A'
      sheet_name: 'Customers'

Example 1: Product Catalog API

Spreadsheet Structure

product_id	name	description	price	in_stock
PROD-001	Widget A	Amazing widget	29.99	TRUE
PROD-002	Gadget B	Cool gadget	49.99	FALSE
PROD-003	Doohickey C	Useful tool	19.99	TRUE

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

SQL Template

sqls/products.sql

SELECT
  product_id,
  name,
  description,
  price,
  in_stock
FROM read_gsheet('{{{ conn.spreadsheet_id }}}', sheet = 'Products')
WHERE 1=1
{{#params.in_stock}}
  AND in_stock = '{{{ params.in_stock }}}'
{{/params.in_stock}}
{{#params.max_price}}
  AND price <= {{ params.max_price }}
{{/params.max_price}}
ORDER BY name

Endpoint Configuration

sqls/products.yaml

url-path: /products/
method: GET
template-source: products.sql
connection:
  - products-sheet

request:
  - field-name: in_stock
    field-in: query
    description: Filter by stock status
    required: false
    validators:
      - type: enum
        allowedValues: ['TRUE', 'FALSE']
  - field-name: max_price
    field-in: query
    description: Maximum price filter
    required: false
    validators:
      - type: int
        min: 0
        max: 1000000

API Usage

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

# Get in-stock products under $30
curl 'http://localhost:8080/products/?in_stock=TRUE&max_price=30'

Response:

{
  "data": [
    {
      "product_id": "PROD-003",
      "name": "Doohickey C",
      "description": "Useful tool",
      "price": 19.99,
      "in_stock": true
    }
  ]
}

Example 2: Blog Post Metadata

Perfect for headless CMS scenarios:

Spreadsheet

slug	title	author	published_date	tags
hello-world	Hello World	John Doe	2024-01-15	tech,intro
flapi-launch	Launching flAPI	Jane Smith	2024-02-01	product,announcement

sqls/blog_posts.sql

SELECT
  slug,
  title,
  author,
  published_date,
  tags
FROM read_gsheet('{{{ conn.spreadsheet_id }}}', sheet = 'Posts')
WHERE 1=1
{{#params.author}}
  AND author = '{{{ params.author }}}'
{{/params.author}}
{{#params.tag}}
  AND tags LIKE '%{{{ params.tag }}}%'
{{/params.tag}}
ORDER BY published_date DESC

sqls/blog_posts.yaml

url-path: /blog/posts/
method: GET
template-source: blog_posts.sql
connection:
  - my-sheets

cache:
  enabled: true
  table: blog_posts_cache
  schedule: 5m   # Refresh every 5 minutes
  template-file: blog_cache.sql

Performance Considerations

Caching is Essential

Google Sheets API has rate limits (100 requests/100 seconds). Always use caching:

sqls/products.yaml

url-path: /products/
method: GET
template-source: products.sql
connection:
  - products-sheet

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

sqls/products_cache.sql

-- Full refresh into the DuckLake cache
INSERT INTO {{cache.catalog}}.{{cache.schema}}.{{cache.table}}
SELECT * FROM read_gsheet('{{{ conn.spreadsheet_id }}}')

Performance improvement:

• Direct: ~500ms per request, rate-limited
• Cached: 1-10ms per request, no rate limits

Optimization Tips

1. Use specific sheet names instead of full URLs
2. Read only required columns (reduces bandwidth)
3. Set appropriate cache intervals (balance freshness vs load)
4. Avoid all_varchar=true (slows type inference)

-- Slow: reads entire sheet
SELECT * FROM read_gsheet('1ABC...')

-- Fast: specific sheet and columns
SELECT name, price
FROM read_gsheet('1ABC...', sheet = 'Products')

Security Best Practices

1. Use Service Accounts in Production

Never use personal OAuth tokens:

connections:
  sheets-prod:
    init: |
      INSTALL gsheets FROM community;
      LOAD gsheets;
      CREATE SECRET (
        TYPE gsheet,
        PROVIDER access_token,
        TOKEN '${GOOGLE_SERVICE_ACCOUNT_TOKEN}'
      );

2. Restrict Sheet Permissions

• Give service account read-only access when possible
• Don't make sheets publicly editable
• Use specific sheet ranges if supported

3. Validate Input

Always validate request parameters with flAPI's built-in validators:

sqls/products.yaml

request:
  - field-name: product_id
    field-in: query
    required: true
    validators:
      - type: string
        regex: '^PROD-[0-9]{3}$'

4. Sanitize Data

Triple braces escape strings for SQL safety:

WHERE product_id = '{{{ params.product_id }}}'

Troubleshooting

Authentication Failed

Error: Authentication failed

1. Delete existing secret
2. Run flAPI with browser access
3. Follow OAuth flow

Rate Limit Exceeded

Error: Quota exceeded for quota metric

Solution: Increase cache intervals:

cache:
  schedule: 30m   # Every 30 minutes instead of 5

Sheet Not Found

Error: Sheet 'Name' not found

-- Verify sheet name (case-sensitive)
FROM read_gsheet('1ABC...', sheet = 'Products')  -- correct
FROM read_gsheet('1ABC...', sheet = 'products')  -- wrong

Type Inference Issues

Some columns aren't recognized correctly:

-- Force all columns as varchar first
SELECT * FROM read_gsheet('1ABC...', all_varchar = true)

-- Then cast explicitly
SELECT
  CAST(price AS DOUBLE) AS price,
  CAST(quantity AS INTEGER) AS quantity
FROM ...

Limitations

Limitation	Impact	Workaround
100 requests/100s	Rate limiting	Use caching (essential)
5M cells max	Large datasets	Split into multiple sheets
No transactions	Race conditions	Use append-only patterns
Type inference	Inconsistent types	Use all_varchar=true + cast
OAuth complexity	Deployment	Use service account tokens

When to Use vs When to Avoid

Use Google Sheets When:

• Non-technical team needs to manage data
• Rapid prototyping or MVP
• Data volume < 10,000 rows
• Updates are infrequent (< 1/minute)
• Collaboration is key

Avoid Google Sheets When:

• High-frequency writes (> 10/second)
• Data volume > 100,000 rows
• Need ACID transactions
• Sub-50ms latency required
• Complex relational queries

Migration Path

Start with Sheets, migrate later:

# Phase 1: Prototype with Sheets
connections:
  data:
    init: |
      INSTALL gsheets FROM community;
      LOAD gsheets;
    properties:
      spreadsheet_id: '1ABC...'

# Phase 2: Migrate to PostgreSQL (same endpoints!)
connections:
  data:
    init: |
      INSTALL postgres;
      LOAD postgres;
      ATTACH 'host=postgres.example.com dbname=production user=${USER} password=${PASS}' AS data_db (TYPE postgres);
    properties:
      catalog: data_db

Your SQL templates and endpoints stay mostly the same — just point at the new attached database.

Next Steps

• Google Sheets API Example: Complete working example
• Caching Setup: Essential for production
• Authentication: Secure your APIs
• BigQuery: When you outgrow Sheets
• Vector Search: Add semantic search to Sheets data

Additional Resources

• DuckDB GSheets Extension: Official extension docs
• Google Sheets API: API documentation
• Service Accounts: Production authentication

---

Pro Tip

Start every project with Google Sheets. It's the fastest way to validate an idea. You can always migrate to a "real" database later - flAPI makes the transition seamless.