Blog

Using Google Sheets as a Database with TanStack Start

Build a TanStack Start application that reads and writes Google Sheets data via the GKit SheetsAPI - with server functions, createServerFn, and TanStack Query integration.

Jul 2, 20264 min read

#tanstack #react #typescript #google-sheets #api

Why Google Sheets makes sense as a backend

Not every project needs Postgres. If your data is already in a spreadsheet - a product catalog, a CRM, a content list maintained by a non-technical team - wiring up a full database is extra work with no real payoff.

Google Sheets gives you a collaborative, always-available data store that anyone on your team can edit in a browser. The gap has always been the API layer: Google's Sheets API requires OAuth flows, service accounts, and a fair amount of boilerplate before you can read a single row.

GKit SheetsAPI closes that gap. One API key, one base URL, and your sheet data comes back as clean JSON - with filtering, sorting, and pagination built in. This post shows how to wire it into a TanStack Start application using server functions, a route loader, and TanStack Query.

What you're building

A simple TanStack Start app that:

Fetches rows from a Google Sheet on the server (no API key exposed to the browser)
Passes the data through a route loader for server-rendered HTML
Keeps the client in sync with TanStack Query for interactive filtering

Project setup

Start with a fresh TanStack Start project and install dependencies:

npx create-tsrouter-app@latest my-sheets-app --framework react --add-ons start
cd my-sheets-app
npm install @tanstack/react-query @tanstack/react-query-devtools

Add your GKit API key to .env:

# .env
GKIT_API_KEY=sk_your_key_here
GKIT_USER_KEY=your_user_key

Never commit this file. Add .env to .gitignore if it isn't already there.

TypeScript types for the GKit response

GKit SheetsAPI returns a consistent envelope regardless of which sheet you're querying. Define these types once and reuse them across your routes:

// src/lib/gkit.ts
 
export interface GKitMeta {
  total: number;
  limit: number;
  offset: number;
}
 
export interface GKitResponse<T = Record<string, unknown>> {
  data: T[];
  meta: GKitMeta;
}
 
export interface GKitQueryParams {
  limit?: number;
  offset?: number;
  search?: string;
  sort?: string;
  order?: "asc" | "desc";
  fields?: string;
}

The server function

TanStack Start's createServerFn runs exclusively on the server. It's the right place to make authenticated requests - your API key never reaches the browser.

// src/lib/gkit.ts (continued)
 
import { createServerFn } from "@tanstack/start";
 
const GKIT_BASE = "https://api.gkit.io/api/spreadsheets";
 
async function fetchSheet<T>(
  sheetName: string,
  params: GKitQueryParams = {},
): Promise<GKitResponse<T>> {
  const userKey = process.env.GKIT_USER_KEY!;
  const apiKey = process.env.GKIT_API_KEY!;
 
  const url = new URL(`${GKIT_BASE}/${userKey}/${sheetName}`);
 
  if (params.limit !== undefined) url.searchParams.set("limit", String(params.limit));
  if (params.offset !== undefined) url.searchParams.set("offset", String(params.offset));
  if (params.search) url.searchParams.set("search", params.search);
  if (params.sort) url.searchParams.set("sort", params.sort);
  if (params.order) url.searchParams.set("order", params.order);
  if (params.fields) url.searchParams.set("fields", params.fields);
 
  const res = await fetch(url.toString(), {
    headers: {
      Authorization: `Bearer ${apiKey}`,
      "Content-Type": "application/json",
    },
  });
 
  if (!res.ok) {
    throw new Error(`GKit request failed: ${res.status} ${res.statusText}`);
  }
 
  return res.json() as Promise<GKitResponse<T>>;
}
 
// The server function your routes and components will call
export const getProducts = createServerFn({ method: "GET" }).handler(
  async ({ data }: { data: GKitQueryParams }) => {
    return fetchSheet<{ name: string; price: number; sku: string }>("Products", data);
  },
);

The createServerFn wrapper handles serialization and ensures the function body is stripped from client bundles at build time. Only the RPC call stub reaches the browser.

Route with a loader

TanStack Router's loader runs on the server during SSR and on the client during navigation. Calling your server function from here gives you server-rendered data on the initial page load:

// src/routes/products.tsx
 
import { createFileRoute } from "@tanstack/react-router";
import { getProducts } from "../lib/gkit";
 
export const Route = createFileRoute("/products")({
  loader: async () => {
    return getProducts({ data: { limit: 20, sort: "name", order: "asc" } });
  },
  component: ProductsPage,
});

The loader result is available instantly in the component via Route.useLoaderData() - no loading spinner on first paint.

Component with TanStack Query

For interactive features (search, pagination) you want the client to refetch without a full navigation. Layer TanStack Query on top of the loader data using useSuspenseQuery:

// src/routes/products.tsx (continued)
 
import { useSuspenseQuery } from "@tanstack/react-query";
import { useState } from "react";
 
function ProductsPage() {
  const initialData = Route.useLoaderData();
  const [search, setSearch] = useState("");
 
  const { data } = useSuspenseQuery({
    queryKey: ["products", search],
    queryFn: () => getProducts({ data: { limit: 20, search, sort: "name", order: "asc" } }),
    initialData: search === "" ? initialData : undefined,
  });
 
  return (
    <div>
      <input
        type="text"
        placeholder="Search products..."
        value={search}
        onChange={(e) => setSearch(e.target.value)}
      />
      <p>{data.meta.total} results</p>
      <ul>
        {data.data.map((product) => (
          <li key={product.sku}>
            {product.name} - ${product.price}
          </li>
        ))}
      </ul>
    </div>
  );
}

initialData seeds the query cache with what the loader already fetched, so there's no flash of empty state. When the user types in the search box, TanStack Query fires a new call to the server function and updates the list without a page reload.

What this pattern gives you

No API key exposure. The GKIT_API_KEY environment variable only exists in the server function, which never ships to the browser.
Server-rendered first paint. The loader ensures HTML arrives with data already in it - good for SEO and perceived performance.
Interactive updates without complexity. TanStack Query handles caching, deduplication, and background refetching so you don't have to manage any of that manually.
Non-technical editing. Anyone on your team can open the Google Sheet and update rows. The next request to your app reflects those changes immediately.

Get started

You need a GKit account to get a userKey and sk_ API key. Sign up at gkit.io/signup - the free tier covers development and small production workloads. Once you have a key, connecting any Google Sheet takes under a minute.

All posts