Blog

Build a Flask API Backed by Google Sheets

Use Google Sheets as your data store in Flask - read, filter, and serve rows without a traditional database.

Jun 30, 20263 min read

#flask #python #google-sheets #api #tutorial

Flask and Google Sheets are an underrated combination. You get a familiar spreadsheet interface for managing data and a lightweight Python framework for serving it - no database migrations, no ORM, no infra to provision. It is a great fit for event registration lists, admin tools, internal dashboards, and any prototype where the data owner prefers a spreadsheet over a CMS.

SheetsAPI turns any Google Sheet into a REST endpoint, so your Flask app never touches the Sheets API directly. You just make plain HTTP requests and get paginated JSON back.

1. Set up your Sheet

Create a Google Sheet named EventRegistrations with these columns in row 1:

name	email	event	registered_at	paid

Add a few rows of sample data. Keep the header names lowercase and use underscores instead of spaces - SheetsAPI maps them directly to JSON keys.

Once the sheet is connected to your GKit account, your endpoint looks like:

https://api.gkit.io/api/spreadsheets/{userKey}/EventRegistrations

Copy your userKey and a sk_... API key from the GKit dashboard.

2. Project structure

flask-registrations/
├── app.py
├── sheets_client.py
└── .env

Install dependencies:

pip install flask requests python-dotenv flask-caching

Your .env:

GKIT_USER_KEY=your_user_key_here
GKIT_API_KEY=sk_live_...

3. SheetsAPI helper - `sheets_client.py`

Keep all HTTP logic in a dedicated module so routes stay thin:

# sheets_client.py
import os
import requests
 
BASE_URL = "https://api.gkit.io/api/spreadsheets"
 
def _headers():
    return {"Authorization": f"Bearer {os.environ['GKIT_API_KEY']}"}
 
def _user_key():
    return os.environ["GKIT_USER_KEY"]
 
def query_sheet(sheet: str, *, search=None, sort=None,
                limit=20, offset=0, fields=None):
    """Fetch rows from a sheet with optional filtering and pagination."""
    params = {"limit": limit, "offset": offset}
    if search:
        params["search"] = search
    if sort:
        params["sort"] = sort
    if fields:
        params["fields"] = fields
 
    url = f"{BASE_URL}/{_user_key()}/{sheet}"
    resp = requests.get(url, params=params, headers=_headers(), timeout=8)
    resp.raise_for_status()
    return resp.json()  # { data: [...], meta: { total, limit, offset } }
 
def find_row(sheet: str, field: str, value: str):
    """Return the first row where field == value, or None."""
    result = query_sheet(sheet, search=f"{field}:{value}", limit=1)
    rows = result.get("data", [])
    return rows[0] if rows else None

requests.get is synchronous, which is fine for Flask's default threaded mode. Add a timeout so a slow Sheets response never hangs a worker thread indefinitely.

4. Flask routes - `app.py`

# app.py
import os
from flask import Flask, jsonify, request, abort
from dotenv import load_dotenv
from sheets_client import query_sheet, find_row
 
load_dotenv()
 
app = Flask(__name__)
 
SHEET = "EventRegistrations"
 
@app.get("/registrations")
def list_registrations():
    """
    GET /registrations
      ?event=PyConUS   → search by event name
      ?paid=true       → filter to paid registrations
      ?sort=registered_at
      ?limit=50&offset=0
    """
    # Build a search string from query params
    search = None
    if request.args.get("event"):
        search = f"event:{request.args['event']}"
    elif request.args.get("paid"):
        search = f"paid:{request.args['paid']}"
 
    limit  = int(request.args.get("limit", 20))
    offset = int(request.args.get("offset", 0))
    sort   = request.args.get("sort", "registered_at")
 
    result = query_sheet(
        SHEET,
        search=search,
        sort=sort,
        limit=limit,
        offset=offset,
    )
    return jsonify(result)
 
 
@app.get("/registrations/<email>")
def get_registration(email: str):
    """
    GET /registrations/alice@example.com
    Returns the registration row for a specific email address.
    """
    row = find_row(SHEET, "email", email)
    if row is None:
        abort(404, description=f"No registration found for {email}")
    return jsonify(row)

Run it:

flask --app app run --debug

Test the list endpoint:

curl "http://localhost:5000/registrations?event=PyConUS&limit=5"

And the single-row lookup:

curl "http://localhost:5000/registrations/alice@example.com"

The response shape from SheetsAPI is consistent - data is always an array and meta carries pagination info:

{
  "data": [
    {
      "name": "Alice",
      "email": "alice@example.com",
      "event": "PyConUS",
      "registered_at": "2026-05-10",
      "paid": "true"
    }
  ],
  "meta": { "total": 1, "limit": 5, "offset": 0 }
}

5. Add a TTL cache with Flask-Caching

If your registration list is read frequently and changes infrequently, a short cache prevents hammering SheetsAPI on every request. Flask-Caching wires up in a few lines:

# app.py (additions)
from flask_caching import Cache
 
cache = Cache(config={"CACHE_TYPE": "SimpleCache", "CACHE_DEFAULT_TIMEOUT": 60})
cache.init_app(app)
 
@app.get("/registrations")
@cache.cached(timeout=60, query_string=True)  # cache per unique query string
def list_registrations():
    ...  # same body as before

query_string=True means /registrations?event=PyConUS and /registrations?event=FlaskCon are cached as separate keys. The SimpleCache backend stores results in memory - swap it for RedisCache when you scale beyond a single process.

For the single-email lookup you can cache with a per-email key:

@app.get("/registrations/<email>")
@cache.cached(timeout=120, make_cache_key=lambda email: f"reg:{email}")
def get_registration(email: str):
    ...

One minute of caching is enough to absorb a traffic spike while keeping data fresh for a typical event dashboard.

Query parameter reference

Parameter	Example	Description
`search`	`event:PyConUS`	Filter rows by `field:value`
`sort`	`registered_at` or `-registered_at`	Ascending / descending sort
`limit`	`50`	Rows per page (max 500)
`offset`	`50`	Pagination offset
`fields`	`name,email,paid`	Return only named columns

Try it with GKit

SheetsAPI is part of the GKit toolkit - one account gives you REST endpoints for any Sheet you connect. Sign up at gkit.io to get your API key and user key, connect a sheet, and have a live endpoint in under two minutes. No server required on your end for the data layer - just Flask and a few lines of requests.

All posts