Blog

Using Google Sheets as a Database in Qwik City

Build a resumable Qwik City application that reads Google Sheets data via the GKit SheetsAPI - with server loaders, actions, and optimistic UI updates.

Jul 1, 20264 min read

#qwik #qwikcity #typescript #google-sheets #api

Why Google Sheets works as a lightweight backend

For internal tools, small SaaS features, and prototypes, a relational database is often overkill. Your team already lives in Google Sheets. The data is there, it is editable without a database client, and non-engineers can update it without filing a ticket.

The missing piece is a clean HTTP interface. The native Google Sheets API requires OAuth tokens, service account credentials, and a fair amount of boilerplate just to read a range. GKit SheetsAPI wraps all of that: you get a REST endpoint that accepts a bearer token, returns typed JSON, and handles pagination - no OAuth flow in your app.

This post walks through a Qwik City route that reads product inventory from a spreadsheet and lets users submit new entries, all through GKit SheetsAPI.

Environment variables

Create a .env.local file at your project root:

GKIT_API_KEY=sk_...
GKIT_USER_KEY=your_user_key
GKIT_SHEET_NAME=inventory

GKIT_USER_KEY identifies which spreadsheet to target. You find it in the GKit dashboard after connecting a sheet. GKIT_SHEET_NAME is the tab name within that spreadsheet.

Qwik City exposes .env.local variables to server code automatically - they are never bundled into the client.

TypeScript types

Define the shape of a row before writing any fetch logic. This keeps the rest of the code honest.

// src/types/inventory.ts
 
export interface InventoryItem {
  id: string;
  name: string;
  quantity: number;
  unit: string;
  last_updated: string;
}
 
export interface SheetsResponse<T> {
  data: T[];
  meta: {
    total: number;
    limit: number;
    offset: number;
  };
}

The column names in InventoryItem should match the header row of your spreadsheet exactly. GKit SheetsAPI maps headers to object keys automatically.

Fetching data with routeLoader$

routeLoader$ runs on the server before the component renders. It has access to environment variables and can make authenticated requests without exposing the API key to the client.

// src/routes/inventory/index.tsx
 
import { component$ } from "@builder.io/qwik";
import { routeLoader$, Form, routeAction$, zod$, z } from "@builder.io/qwik-city";
import type { InventoryItem, SheetsResponse } from "~/types/inventory";
 
export const useInventory = routeLoader$(async ({ env, query }) => {
  const userKey = env.get("GKIT_USER_KEY");
  const sheetName = env.get("GKIT_SHEET_NAME");
  const apiKey = env.get("GKIT_API_KEY");
 
  const params = new URLSearchParams({
    limit: "25",
    offset: query.get("offset") ?? "0",
    sort: "last_updated",
    order: "desc",
  });
 
  const res = await fetch(
    `https://api.gkit.io/api/spreadsheets/${userKey}/${sheetName}?${params}`,
    {
      headers: {
        Authorization: `Bearer ${apiKey}`,
        "Content-Type": "application/json",
      },
    },
  );
 
  if (!res.ok) {
    throw new Error(`GKit API error: ${res.status}`);
  }
 
  return (await res.json()) as SheetsResponse<InventoryItem>;
});

The loader reads the offset query parameter so pagination works without JavaScript. If a user bookmarks or shares the URL, the server renders the correct page. GKit SheetsAPI supports limit, offset, search, sort, order, and fields as query parameters - the loader can pass any of them through from the incoming request.

Appending rows with routeAction$

routeAction$ handles form submissions on the server. It validates input with Zod, then posts to GKit SheetsAPI to append a new row.

export const useAddItem = routeAction$(
  async (data, { env }) => {
    const userKey = env.get("GKIT_USER_KEY");
    const sheetName = env.get("GKIT_SHEET_NAME");
    const apiKey = env.get("GKIT_API_KEY");
 
    const res = await fetch(`https://api.gkit.io/api/spreadsheets/${userKey}/${sheetName}`, {
      method: "POST",
      headers: {
        Authorization: `Bearer ${apiKey}`,
        "Content-Type": "application/json",
      },
      body: JSON.stringify({
        name: data.name,
        quantity: data.quantity,
        unit: data.unit,
        last_updated: new Date().toISOString(),
      }),
    });
 
    if (!res.ok) {
      return { success: false, error: `Failed to add item: ${res.status}` };
    }
 
    return { success: true };
  },
  zod$({
    name: z.string().min(1),
    quantity: z.coerce.number().int().positive(),
    unit: z.string().min(1),
  }),
);

Zod validation runs before your async function executes. Invalid submissions return field-level errors automatically - no manual validation code needed. The action works as a plain HTML form POST even with JavaScript disabled, which is Qwik City's default progressive enhancement model.

The component with optimistic UI

Qwik's action state updates synchronously after submission, so you can render the new row immediately without waiting for a server round-trip or a loader refetch.

export default component$(() => {
  const inventory = useInventory();
  const addItem = useAddItem();
 
  // Prepend the pending submission as a ghost row while the action runs
  const items: InventoryItem[] =
    addItem.isRunning && addItem.formData
      ? [
          {
            id: "optimistic",
            name: addItem.formData.get("name") as string,
            quantity: Number(addItem.formData.get("quantity")),
            unit: addItem.formData.get("unit") as string,
            last_updated: new Date().toISOString(),
          },
          ...inventory.value.data,
        ]
      : inventory.value.data;
 
  return (
    <main>
      <h1>Inventory</h1>
 
      <Form action={addItem}>
        <input name="name" placeholder="Item name" required />
        <input name="quantity" type="number" placeholder="Qty" required />
        <input name="unit" placeholder="Unit (kg, pcs...)" required />
        <button type="submit" disabled={addItem.isRunning}>
          {addItem.isRunning ? "Adding..." : "Add item"}
        </button>
        {addItem.value?.error && <p role="alert">{addItem.value.error}</p>}
      </Form>
 
      <table>
        <thead>
          <tr>
            <th>Name</th>
            <th>Quantity</th>
            <th>Unit</th>
            <th>Last updated</th>
          </tr>
        </thead>
        <tbody>
          {items.map((item) => (
            <tr
              key={item.id}
              style={item.id === "optimistic" ? { opacity: "0.6" } : {}}
            >
              <td>{item.name}</td>
              <td>{item.quantity}</td>
              <td>{item.unit}</td>
              <td>{new Date(item.last_updated).toLocaleDateString()}</td>
            </tr>
          ))}
        </tbody>
      </table>
 
      <p>{inventory.value.meta.total} total items</p>
    </main>
  );
});

When the form submits, addItem.isRunning flips to true and addItem.formData holds the raw submitted values. The optimistic row appears at the top with reduced opacity. Once the server write completes, isRunning drops to false, the loader refetches, and the real row replaces the ghost. No external state library, no manual cache invalidation.

What this removes from your stack

Combining Qwik City's server primitives with GKit SheetsAPI removes a meaningful amount of infrastructure:

No database to provision or maintain
No OAuth flow - a single bearer token covers all Sheet operations
No client-side data fetching - loaders run on the server and responses are serialised into the HTML stream
No separate API route for pagination - the loader reads query parameters directly
No hydration cost for the data layer - the component renders from serialised state, not a client fetch

The spreadsheet remains editable by anyone with access. Changes made directly in Sheets are visible on the next page load without cache invalidation logic.

Next steps

This example covers reading and appending rows. GKit SheetsAPI also supports filtering via the search parameter, selecting specific columns with fields, and custom sort direction via order - which means sortable, searchable tables can be driven entirely by query parameters and server loaders.

If you want to try it with your own spreadsheet, create a free account at gkit.io/signup. You will have an API key and a connected sheet in under two minutes.

All posts