Blog

Build an Express API Backed by Google Sheets

Use Node.js and Express to proxy, transform, and expose Google Sheets data as a REST API - with caching, error handling, and TypeScript types.

Jun 25, 20264 min read

#nodejs #express #google-sheets #backend #javascript

Sometimes you need more than a raw data endpoint. You want to add your own authentication, rename fields, join in data from another source, or rate-limit certain callers. Running an Express proxy in front of SheetsAPI gives you all of that without managing a database.

Your Google Sheet stays the source of truth - non-engineers edit it in a browser - and your Express app owns every concern about how that data reaches clients: caching, transformation, auth, error formatting.

What you will build

A small Express app that:

Exposes GET /api/products backed by a "Products" Google Sheet
Caches responses in memory for 30 seconds to avoid hammering SheetsAPI
Accepts a ?category= query param and forwards it to SheetsAPI as a search filter
Requires a caller-supplied Authorization header so not just anyone can hit your proxy

Project setup

mkdir sheets-express && cd sheets-express
npm init -y
npm install express
npm install -D typescript @types/node @types/express ts-node
npx tsc --init

Create a .env file - never commit this:

GKIT_USER_KEY=YOUR_USER_KEY
GKIT_API_KEY=sk_your_key_here
PROXY_SECRET=some_secret_for_your_callers
PORT=5885

TypeScript types for SheetsAPI responses

SheetsAPI always returns the same envelope shape. Define it once and reuse it throughout your app:

// src/types.ts
 
export type SheetsMeta = {
  total: number;
  limit: number;
  offset: number;
};
 
export type SheetsResponse<T> = {
  data: T[];
  meta: SheetsMeta;
};
 
export type Product = {
  name: string;
  price: string;
  category: string;
  sku: string;
};

A reusable SheetsAPI client

Rather than writing raw fetch calls everywhere, wrap the API in a typed helper:

// src/sheetsClient.ts
 
import { SheetsResponse } from "./types";
 
const BASE_URL = "https://sheetsapi.gkit.mreshank.com/api/spreadsheets";
 
type QueryOptions = {
  search?: string;
  sort?: string;
  limit?: number;
  offset?: number;
  fields?: string[];
  searchExact?: boolean;
};
 
export async function fetchSheet<T>(
  sheetName: string,
  options: QueryOptions = {},
): Promise<SheetsResponse<T>> {
  const userKey = process.env.GKIT_USER_KEY;
  const apiKey = process.env.GKIT_API_KEY;
 
  if (!userKey) throw new Error("GKIT_USER_KEY is not set");
 
  const params = new URLSearchParams();
  if (options.search) params.set("search", options.search);
  if (options.searchExact) params.set("search_exact", "1");
  if (options.sort) params.set("sort", options.sort);
  if (options.limit != null) params.set("limit", String(options.limit));
  if (options.offset != null) params.set("offset", String(options.offset));
  if (options.fields?.length) params.set("fields", options.fields.join(","));
 
  const url = `${BASE_URL}/${userKey}/${sheetName}?${params}`;
 
  const res = await fetch(url, {
    headers: apiKey ? { Authorization: `Bearer ${apiKey}` } : {},
  });
 
  if (!res.ok) {
    throw new Error(`SheetsAPI returned ${res.status} for ${sheetName}`);
  }
 
  return res.json() as Promise<SheetsResponse<T>>;
}

GKIT_API_KEY is optional - include it if you created a private API key in the GKit dashboard. Without it, the sheet must be publicly accessible.

In-memory cache with TTL

A plain Map with timestamps is enough for a demo. For production you would swap this out for Redis or a proper cache layer, but this avoids adding any dependency:

// src/cache.ts
 
type CacheEntry<T> = {
  value: T;
  expiresAt: number;
};
 
const store = new Map<string, CacheEntry<unknown>>();
 
export function get<T>(key: string): T | null {
  const entry = store.get(key) as CacheEntry<T> | undefined;
  if (!entry) return null;
  if (Date.now() > entry.expiresAt) {
    store.delete(key);
    return null;
  }
  return entry.value;
}
 
export function set<T>(key: string, value: T, ttlMs: number): void {
  store.set(key, { value, expiresAt: Date.now() + ttlMs });
}

Auth middleware

A simple middleware that checks for a Bearer token matching your PROXY_SECRET:

// src/middleware/auth.ts
 
import { Request, Response, NextFunction } from "express";
 
export function requireAuth(req: Request, res: Response, next: NextFunction) {
  const header = req.headers.authorization ?? "";
  const token = header.startsWith("Bearer ") ? header.slice(7) : "";
 
  if (!token || token !== process.env.PROXY_SECRET) {
    res.status(401).json({ error: "Unauthorized" });
    return;
  }
 
  next();
}

This keeps your proxy private even if the underlying SheetsAPI sheet is public. Callers send Authorization: Bearer YOUR_PROXY_SECRET and your Express app validates it before touching SheetsAPI at all.

The products route

// src/routes/products.ts
 
import { Router } from "express";
import { fetchSheet } from "../sheetsClient";
import { get as cacheGet, set as cacheSet } from "../cache";
import { Product, SheetsResponse } from "../types";
 
const router = Router();
const CACHE_TTL = 30_000; // 30 seconds
 
router.get("/", async (req, res) => {
  const category = typeof req.query.category === "string" ? req.query.category : undefined;
  const sort = typeof req.query.sort === "string" ? req.query.sort : "-name";
  const limit = Number(req.query.limit ?? 50);
  const offset = Number(req.query.offset ?? 0);
 
  const cacheKey = `products:${category ?? ""}:${sort}:${limit}:${offset}`;
  const cached = cacheGet<SheetsResponse<Product>>(cacheKey);
 
  if (cached) {
    res.json({ ...cached, cached: true });
    return;
  }
 
  try {
    const result = await fetchSheet<Product>("Products", {
      search: category ? `category:${category}` : undefined,
      sort,
      limit,
      offset,
    });
 
    cacheSet(cacheKey, result, CACHE_TTL);
    res.json({ ...result, cached: false });
  } catch (err) {
    const message = err instanceof Error ? err.message : "Unknown error";
    res.status(502).json({ error: "Failed to fetch from SheetsAPI", detail: message });
  }
});
 
export default router;

The cache key encodes every parameter that affects the response, so different filter combinations get their own cached entry.

Wiring it together

// src/index.ts
 
import express from "express";
import { requireAuth } from "./middleware/auth";
import productsRouter from "./routes/products";
 
const app = express();
app.use(express.json());
 
app.use("/api/products", requireAuth, productsRouter);
 
app.get("/health", (_req, res) => res.json({ ok: true }));
 
const port = Number(process.env.PORT ?? 5885);
app.listen(port, () => {
  console.log(`Listening on http://localhost:${port}`);
});

Start it with:

npx ts-node src/index.ts

Test with curl:

curl -H "Authorization: Bearer some_secret_for_your_callers" \
  "http://localhost:5885/api/products?category=Audio&sort=-price"

Response shape:

{
  "data": [
    {
      "name": "Studio Monitors",
      "price": "299",
      "category": "Audio",
      "sku": "AUD-004"
    },
    {
      "name": "Wireless Headphones",
      "price": "79",
      "category": "Audio",
      "sku": "AUD-001"
    }
  ],
  "meta": { "total": 2, "limit": 50, "offset": 0 },
  "cached": false
}

Extending from here

This proxy pattern composes cleanly:

Transform fields - rename sku to id, parse price as a number, add computed fields before sending the response.
Rate limiting - add express-rate-limit before requireAuth to cap calls per IP.
Multiple sheets - add a sheetsRouter factory that accepts a sheet name and wraps the same fetch + cache logic, then mount sheetsRouter("Products") at /api/products and sheetsRouter("Team") at /api/team.
Webhooks - accept a POST /api/cache/clear endpoint protected by a separate admin secret so your sheet editor can bust the cache immediately after saving changes.

The key insight is that SheetsAPI handles the hard part - authenticating with Google, parsing the sheet, exposing a clean REST interface - and your Express layer handles the hard part on your side: auth, caching, and shaping data for your specific clients.

Get started with SheetsAPI → or jump straight to the quickstart guide.

All posts