Blog

Using Google Sheets as a Database with tRPC and Next.js

Create a fully type-safe data layer with tRPC that reads and writes Google Sheets via the GKit SheetsAPI - including server-side React Server Component support and React Query integration.

Jun 30, 20265 min read

#trpc #nextjs #typescript #google-sheets #react-query

Why Google Sheets as a database

For a surprising number of use cases - internal tools, lightweight admin panels, content pipelines, small SaaS features - Google Sheets is a perfectly adequate data store. It is already where non-technical stakeholders live. It handles concurrent edits. It has a familiar UI that requires zero training. The problem has never been Sheets itself; it has been the lack of a proper API contract around it.

The GKit SheetsAPI gives you that contract: a REST endpoint with filtering, pagination, sorting, and row appending - backed by a real API key, served from the edge. Wrap it in tRPC and you get end-to-end TypeScript types from the spreadsheet column name all the way to your React component props.

Project structure

This guide assumes a Next.js 14+ App Router project with tRPC wired up using @trpc/server and @trpc/react-query. If you are starting from scratch, the create-t3-app scaffold handles most of the boilerplate.

The files we will touch:

src/
  server/
    api/
      routers/
        products.ts   ← tRPC router
      root.ts         ← app router + caller factory
      trpc.ts         ← context + procedure helpers
  app/
    products/
      page.tsx        ← React Server Component
      client.tsx      ← client component with useQuery
  lib/
    sheets.ts         ← thin fetch wrapper for SheetsAPI

The SheetsAPI fetch wrapper

Isolate the HTTP layer in one file before writing any router code. This keeps procedures readable and makes it trivial to point at a different data source later.

// src/lib/sheets.ts
const BASE = "https://api.gkit.io/api/spreadsheets";
const KEY = process.env.GKIT_API_KEY!; // sk_...
 
export interface SheetsMeta {
  total: number;
  limit: number;
  offset: number;
}
 
export async function sheetsGet<T>(
  userKey: string,
  sheetName: string,
  params: Record<string, string | number> = {},
): Promise<{ data: T[]; meta: SheetsMeta }> {
  const url = new URL(`${BASE}/${userKey}/${sheetName}`);
  for (const [k, v] of Object.entries(params)) {
    url.searchParams.set(k, String(v));
  }
  const res = await fetch(url.toString(), {
    headers: { Authorization: `Bearer ${KEY}` },
    next: { revalidate: 60 }, // Next.js fetch-level cache
  });
  if (!res.ok) throw new Error(`SheetsAPI error: ${res.status}`);
  return res.json();
}
 
export async function sheetsPost<T>(userKey: string, sheetName: string, rows: T[]): Promise<void> {
  const res = await fetch(`${BASE}/${userKey}/${sheetName}`, {
    method: "POST",
    headers: {
      Authorization: `Bearer ${KEY}`,
      "Content-Type": "application/json",
    },
    body: JSON.stringify({ rows }),
  });
  if (!res.ok) throw new Error(`SheetsAPI error: ${res.status}`);
}

The next: { revalidate: 60 } option on the GET request plugs into Next.js's extended fetch cache. Repeated RSC renders within the revalidation window hit the cache instead of making a live SheetsAPI call.

The tRPC router

With the wrapper in place, the router is straightforward. Zod schemas handle both input validation from the client and output parsing of the raw rows coming back from Sheets.

// src/server/api/routers/products.ts
import { z } from "zod";
import { createTRPCRouter, publicProcedure } from "../trpc";
import { sheetsGet, sheetsPost } from "@/lib/sheets";
 
const USER_KEY = process.env.GKIT_USER_KEY!;
const SHEET = "products";
 
const ProductSchema = z.object({
  id: z.string(),
  name: z.string(),
  price: z.coerce.number(),
  inStock: z.coerce.boolean(),
});
 
export type Product = z.infer<typeof ProductSchema>;
 
const ListInputSchema = z.object({
  limit: z.number().int().min(1).max(100).default(20),
  offset: z.number().int().min(0).default(0),
  search: z.string().optional(),
  sort: z.string().optional(),
  order: z.enum(["asc", "desc"]).optional(),
});
 
const CreateInputSchema = z.object({
  name: z.string().min(1),
  price: z.number().positive(),
  inStock: z.boolean().default(true),
});
 
export const productsRouter = createTRPCRouter({
  list: publicProcedure.input(ListInputSchema).query(async ({ input }) => {
    const params: Record<string, string | number> = {
      limit: input.limit,
      offset: input.offset,
    };
    if (input.search) params.search = input.search;
    if (input.sort) params.sort = input.sort;
    if (input.order) params.order = input.order;
 
    const raw = await sheetsGet<unknown>(USER_KEY, SHEET, params);
    const data = raw.data.map((row) => ProductSchema.parse(row));
    return { data, meta: raw.meta };
  }),
 
  create: publicProcedure.input(CreateInputSchema).mutation(async ({ input }) => {
    await sheetsPost(USER_KEY, SHEET, [input]);
    return { success: true };
  }),
});

Zod does double duty: it validates tRPC input before the procedure runs, and it parses raw spreadsheet rows on the way out. If a column name changes in the sheet, the ProductSchema.parse call fails loudly at the router boundary rather than silently passing malformed data into the UI.

React Server Component with `unstable_cache`

Server Components can invoke the tRPC router directly through the server-side caller - no HTTP round-trip required. Wrap the call in unstable_cache to memoize the result across renders and enable tag-based invalidation.

// src/app/products/page.tsx
import { unstable_cache } from "next/cache";
import { createCaller } from "@/server/api/root";
import { createTRPCContext } from "@/server/api/trpc";
 
const getCachedProducts = unstable_cache(
  async () => {
    const ctx = await createTRPCContext({ headers: new Headers() });
    const caller = createCaller(ctx);
    return caller.products.list({ limit: 20, offset: 0 });
  },
  ["products-list"],
  { revalidate: 60, tags: ["products"] },
);
 
export default async function ProductsPage() {
  const { data: products, meta } = await getCachedProducts();
 
  return (
    <main>
      <h1>Products ({meta.total} total)</h1>
      <ul>
        {products.map((p) => (
          <li key={p.id}>
            {p.name} - ${p.price}
          </li>
        ))}
      </ul>
    </main>
  );
}

The tags: ["products"] option means you can call revalidateTag("products") from a Server Action or route handler after a write, and Next.js will purge just the product cache on the next request - without touching other cached data.

Client component with React Query

For interactive features - search inputs, pagination controls, optimistic creates - drop to a client component and use the useQuery hook.

// src/app/products/client.tsx
"use client";
 
import { useState } from "react";
import { api } from "@/lib/trpc/react";
 
export function ProductSearch() {
  const [search, setSearch] = useState("");
 
  const { data, isLoading } = api.products.list.useQuery(
    { limit: 20, offset: 0, search: search || undefined },
    { placeholderData: (prev) => prev },
  );
 
  const utils = api.useUtils();
  const create = api.products.create.useMutation({
    onSuccess: () => {
      void utils.products.list.invalidate();
    },
  });
 
  return (
    <div>
      <input
        value={search}
        onChange={(e) => setSearch(e.target.value)}
        placeholder="Search products..."
      />
      {isLoading && <p>Loading...</p>}
      <ul>
        {data?.data.map((p) => (
          <li key={p.id}>
            {p.name} - ${p.price}
          </li>
        ))}
      </ul>
      <button
        onClick={() => create.mutate({ name: "New Product", price: 9.99, inStock: true })}
        disabled={create.isPending}
      >
        {create.isPending ? "Adding..." : "Add product"}
      </button>
    </div>
  );
}

placeholderData: (prev) => prev keeps the previous result set visible while a new search query is in-flight. On a successful mutation, utils.products.list.invalidate() triggers a refetch so the new row surfaces without a manual page reload.

Caching layers at a glance

Two caching layers are active in this setup:

Layer	Mechanism	Scope
HTTP response	`next: { revalidate: 60 }` in `sheetsGet`	All server renders, shared across requests
Caller result	`unstable_cache` with tag `"products"`	Purged on demand via `revalidateTag`
Client state	React Query in-memory cache	Per browser tab, invalidated after mutations

The HTTP and unstable_cache layers both default to a 60-second TTL, which is reasonable for data that changes infrequently. For higher-frequency writes, lower the TTL or rely entirely on tag-based invalidation from mutations.

What to build next

This pattern scales well for read-heavy internal tools. A few natural extensions:

Add a fields parameter to ListInputSchema and pass it through to sheetsGet to fetch only the columns you need - useful when a sheet has dozens of columns and your UI only cares about three.
Protect procedures behind an authedProcedure that checks a session cookie, so writes are gated on authentication.
Call revalidateTag("products") inside the create mutation's server-side path so the RSC cache updates immediately after a write rather than waiting for the TTL to expire.

For anything beyond a few thousand rows or more than a handful of writes per second, the SheetsAPI rate limits will become the constraint. That is a reasonable ceiling for most internal tools and lightweight SaaS features. When you hit it, the type-safe tRPC boundary means swapping sheetsGet for a Postgres query requires no changes to any component.

Ready to connect your own spreadsheet? Get an API key at gkit.io/signup and your first sheet is live in under two minutes.

All posts