Google Sheets as a REST API in Bun

// fetch-products.ts
const USER_KEY = "YOUR_USER_KEY";
const API_KEY = process.env.SHEETS_API_KEY!;
const BASE = "https://sheetsapi.gkit.mreshank.com/api/spreadsheets";
 
const resp = await fetch(`${BASE}/${USER_KEY}/Products?limit=20&sort=name`, {
  headers: { Authorization: `Bearer ${API_KEY}` },
});
 
if (!resp.ok) throw new Error(`SheetsAPI error ${resp.status}`);
 
interface Product {
  name: string;
  price: string;
  category: string;
}
const { data, meta } = (await resp.json()) as {
  data: Product[];
  meta: { total: number; limit: number; offset: number };
};
 
console.log(`Fetched ${data.length} of ${meta.total} products`);
data.forEach((p) => console.log(`${p.name} - $${p.price}`));

SHEETS_API_KEY=sk_your_api_key_here bun run fetch-products.ts

// lib/sheets.ts
export interface SheetResponse<T> {
  data: T[];
  meta: { total: number; limit: number; offset: number };
}
 
export class SheetsApiError extends Error {
  constructor(
    public status: number,
    message: string,
  ) {
    super(message);
    this.name = "SheetsApiError";
  }
}
 
export class SheetsClient {
  constructor(
    private readonly userKey: string,
    private readonly apiKey: string,
    private readonly base = "https://sheetsapi.gkit.mreshank.com/api/spreadsheets",
  ) {}
 
  async list<T>(
    sheet: string,
    params: Record<string, string | number> = {},
  ): Promise<SheetResponse<T>> {
    const url = new URL(`${this.base}/${this.userKey}/${sheet}`);
    Object.entries(params).forEach(([k, v]) => url.searchParams.set(k, String(v)));
 
    const resp = await fetch(url, {
      headers: { Authorization: `Bearer ${this.apiKey}` },
    });
 
    if (!resp.ok) {
      throw new SheetsApiError(resp.status, await resp.text());
    }
 
    return resp.json() as Promise<SheetResponse<T>>;
  }
 
  async addRow<T extends Record<string, string>>(sheet: string, row: T): Promise<T> {
    const resp = await fetch(`${this.base}/${this.userKey}/${sheet}`, {
      method: "POST",
      headers: {
        Authorization: `Bearer ${this.apiKey}`,
        "Content-Type": "application/json",
      },
      body: JSON.stringify(row),
    });
 
    if (!resp.ok) throw new SheetsApiError(resp.status, await resp.text());
    return resp.json() as Promise<T>;
  }
 
  async fetchAll<T>(sheet: string, params: Record<string, string | number> = {}): Promise<T[]> {
    const limit = Number(params["limit"] ?? 100);
    const all: T[] = [];
    let offset = 0;
 
    while (true) {
      const { data, meta } = await this.list<T>(sheet, {
        ...params,
        limit,
        offset,
      });
      all.push(...data);
      offset += limit;
      if (all.length >= meta.total) break;
    }
 
    return all;
  }
}

import { Database } from "bun:sqlite";
import { SheetsClient } from "./lib/sheets";
 
const db = new Database("cache.sqlite");
 
db.run(`
  CREATE TABLE IF NOT EXISTS cache (
    key       TEXT PRIMARY KEY,
    value     TEXT NOT NULL,
    expires_at INTEGER NOT NULL
  )
`);
 
const getStmt = db.prepare<{ value: string }, [string, number]>(
  "SELECT value FROM cache WHERE key = ? AND expires_at > ?",
);
const setStmt = db.prepare(
  "INSERT OR REPLACE INTO cache (key, value, expires_at) VALUES (?, ?, ?)",
);
 
async function cachedFetch<T>(key: string, ttlSeconds: number, fn: () => Promise<T>): Promise<T> {
  const row = getStmt.get(key, Date.now());
  if (row) return JSON.parse(row.value) as T;
 
  const data = await fn();
  setStmt.run(key, JSON.stringify(data), Date.now() + ttlSeconds * 1000);
  return data;
}
 
const client = new SheetsClient(process.env.SHEETS_USER_KEY!, process.env.SHEETS_API_KEY!);
 
const products = await cachedFetch("products", 60, () => client.fetchAll("Products"));
 
console.log(`${products.length} products`);

// server.ts
import { SheetsClient } from "./lib/sheets";
 
const client = new SheetsClient(process.env.SHEETS_USER_KEY!, process.env.SHEETS_API_KEY!);
 
const server = Bun.serve({
  port: 5885,
 
  async fetch(req) {
    const url = new URL(req.url);
    const path = url.pathname;
 
    if (req.method === "GET" && path === "/products") {
      const search = url.searchParams.get("search") ?? undefined;
      const sort = url.searchParams.get("sort") ?? "name";
      const limit = parseInt(url.searchParams.get("limit") ?? "20");
 
      const result = await client.list("Products", {
        limit,
        sort,
        ...(search ? { search } : {}),
      });
      return Response.json(result);
    }
 
    if (req.method === "POST" && path === "/products") {
      const body = (await req.json()) as Record<string, string>;
      const added = await client.addRow("Products", body);
      return Response.json(added, { status: 201 });
    }
 
    return new Response("Not found", { status: 404 });
  },
});
 
console.log(`Listening on http://localhost:${server.port}`);

SHEETS_USER_KEY=YOUR_USER_KEY \
SHEETS_API_KEY=sk_your_api_key_here \
bun run server.ts

import { $ } from "bun";
 
const USER_KEY = "YOUR_USER_KEY";
const API_KEY = process.env.SHEETS_API_KEY!;
 
const json = await $`
  curl -s \
    -H "Authorization: Bearer ${API_KEY}" \
    "https://sheetsapi.gkit.mreshank.com/api/spreadsheets/${USER_KEY}/Products?limit=5"
`.text();
 
const { data } = JSON.parse(json);
console.log(data);

// sheets.test.ts
import { expect, test, mock } from "bun:test";
import { SheetsClient } from "./lib/sheets";
 
test("list returns data and meta", async () => {
  const mockFetch = mock(
    async () =>
      new Response(
        JSON.stringify({
          data: [{ name: "Widget" }],
          meta: { total: 1, limit: 20, offset: 0 },
        }),
      ),
  );
  globalThis.fetch = mockFetch as typeof fetch;
 
  const client = new SheetsClient("key", "sk_test");
  const result = await client.list("Products");
 
  expect(result.data).toHaveLength(1);
  expect(result.meta.total).toBe(1);
});

bun test