Blog

Using Google Sheets as a database with SolidJS

Fetch and display Google Sheets data in a SolidJS app using SheetsAPI - createResource, server functions, and reactive filtering.

Jun 27, 20263 min read

#solidjs #google-sheets #tutorial

Google Sheets makes a surprisingly capable backend for read-heavy apps: non-technical collaborators can update it, you get a free GUI, and with SheetsAPI you get a proper REST interface on top. SolidJS is a great pairing because its fine-grained reactivity model eliminates the boilerplate that plagues React data-fetching patterns. No useEffect, no stale closure bugs, no manual dependency arrays.

SheetsAPI at a glance

Every request hits:

GET https://api.sheetsapi.io/api/spreadsheets/{userKey}/{sheetName}

Query parameters:

Param	Purpose
`search`	Filter rows - `field:value`
`sort`	Order results - `field:asc` or `field:desc`
`limit`	Page size
`offset`	Page offset for pagination
`fields`	Comma-separated list of columns to return

Authentication is a bearer token in the Authorization header:

Authorization: Bearer sk_...

Responses follow a consistent shape:

{
  "data": [{ "name": "Alice", "role": "Engineer" }],
  "meta": { "total": 142, "limit": 20, "offset": 0 }
}

Project setup

npx create-solidstart@latest sheets-demo
cd sheets-demo
npm install

Add your API key to .env:

SHEETS_API_KEY=sk_your_secret_key

Keeping the key secret with a server function

Never expose your API key to the browser. SolidStart's "use server" directive runs a function exclusively on the server - it compiles away from the client bundle entirely.

// src/lib/sheets.ts
import { cache } from "@solidjs/router";
 
export type SheetParams = {
  search?: string;
  sort?: string;
  limit?: number;
  offset?: number;
};
 
export const fetchEmployees = cache(async (params: SheetParams) => {
  "use server";
 
  const url = new URL(`https://api.sheetsapi.io/api/spreadsheets/YOUR_USER_KEY/employees`);
 
  if (params.search) url.searchParams.set("search", params.search);
  if (params.sort) url.searchParams.set("sort", params.sort);
  if (params.limit) url.searchParams.set("limit", String(params.limit));
  if (params.offset) url.searchParams.set("offset", String(params.offset));
 
  const res = await fetch(url.toString(), {
    headers: {
      Authorization: `Bearer ${process.env.SHEETS_API_KEY}`,
    },
  });
 
  if (!res.ok) throw new Error(`SheetsAPI error: ${res.status}`);
  return res.json() as Promise<{
    data: Record<string, string>[];
    meta: { total: number; limit: number; offset: number };
  }>;
}, "employees");

cache from @solidjs/router de-duplicates concurrent calls with the same arguments - useful when multiple components trigger the same fetch during SSR.

Reactive filtering with createResource

createResource accepts a reactive source as its first argument. When the source signal changes, SolidJS automatically re-runs the fetcher. No useEffect, no manual re-fetch triggers.

// src/routes/index.tsx
import { createResource, createSignal, For, Show, Suspense } from "solid-js";
import { fetchEmployees } from "~/lib/sheets";
 
const PAGE_SIZE = 20;
 
export default function EmployeesPage() {
  const [search, setSearch] = createSignal("");
  const [page, setPage] = createSignal(0);
 
  // source object - recomputed whenever search or page changes
  const params = () => ({
    search: search() ? `name:${search()}` : undefined,
    sort: "name:asc",
    limit: PAGE_SIZE,
    offset: page() * PAGE_SIZE,
  });
 
  const [result] = createResource(params, fetchEmployees);
 
  return (
    <main>
      <input
        placeholder="Search by name…"
        value={search()}
        onInput={(e) => {
          setSearch(e.currentTarget.value);
          setPage(0); // reset pagination on new search
        }}
      />
 
      <Suspense fallback={<p>Loading…</p>}>
        <Show when={result()} keyed>
          {(res) => (
            <>
              <table>
                <thead>
                  <tr>
                    <th>Name</th>
                    <th>Role</th>
                    <th>Department</th>
                  </tr>
                </thead>
                <tbody>
                  <For each={res.data}>
                    {(row) => (
                      <tr>
                        <td>{row.name}</td>
                        <td>{row.role}</td>
                        <td>{row.department}</td>
                      </tr>
                    )}
                  </For>
                </tbody>
              </table>
 
              <Pagination
                total={res.meta.total}
                page={page()}
                pageSize={PAGE_SIZE}
                onPageChange={setPage}
              />
            </>
          )}
        </Show>
      </Suspense>
    </main>
  );
}

A key SolidJS advantage here: For only re-renders the rows that actually changed. React would diff the entire list; SolidJS tracks each item independently.

Derived state with createMemo

createMemo computes a value from signals and caches it until a dependency changes - ideal for expensive derivations like stats over your fetched rows.

import { createMemo } from "solid-js";
 
const departmentCounts = createMemo(() => {
  const rows = result()?.data ?? [];
  return rows.reduce<Record<string, number>>((acc, row) => {
    acc[row.department] = (acc[row.department] ?? 0) + 1;
    return acc;
  }, {});
});
 
// Use in JSX - only re-runs when result() changes
<pre>{JSON.stringify(departmentCounts(), null, 2)}</pre>;

Pagination component

function Pagination(props: {
  total: number;
  page: number;
  pageSize: number;
  onPageChange: (p: number) => void;
}) {
  const totalPages = () => Math.ceil(props.total / props.pageSize);
 
  return (
    <div>
      <button disabled={props.page === 0} onClick={() => props.onPageChange(props.page - 1)}>
        Previous
      </button>
      <span>
        Page {props.page + 1} of {totalPages()}
      </span>
      <button
        disabled={props.page >= totalPages() - 1}
        onClick={() => props.onPageChange(props.page + 1)}
      >
        Next
      </button>
    </div>
  );
}

Because totalPages is a signal-derived function, the button disabled states update automatically when props.total or props.pageSize change - no extra wiring needed.

SolidJS vs React for this pattern

The same feature in React requires useEffect to watch search and page, manual loading state, and careful dependency arrays to avoid infinite loops. SolidJS handles all of that through its reactive graph.

// React equivalent - more ceremony
const [data, setData] = useState(null);
const [loading, setLoading] = useState(false);
 
useEffect(() => {
  setLoading(true);
  fetchEmployees({ search, page }).then((res) => {
    setData(res);
    setLoading(false);
  });
}, [search, page]); // forget a dep → stale data

Client vs server approaches

Approach	Key secret safe	SSR support	Re-fetches on signal change	Best for
`createResource` + server function	Yes	Yes	Yes, automatic	Production apps, sensitive keys
`createResource` + client fetch	No	No	Yes, automatic	Public APIs, prototyping
`onMount` + `fetch`	No	No	Manual	One-time loads
SolidStart route loader	Yes	Yes	Page navigation only	Initial page data

For most cases: use a server function wrapped with cache, expose it through createResource with a reactive source signal. You get automatic refetching, SSR, and zero key exposure with minimal code.

All posts