Blog

Google Sheets as a Backend in Deno Fresh

Fetch and display Google Sheets data in Deno Fresh - server-side rendering, islands, and edge deployment on Deno Deploy.

Jun 27, 20263 min read

#google-sheets #fresh #deno #typescript #rest-api

Fresh is Deno's full-stack web framework - server-rendered by default, zero build step, and deployed to Deno Deploy's edge network in seconds. Its island architecture ships zero JavaScript by default, making it fast to serve and easy to progressively enhance.

Prerequisites

Deno 1.40+ (curl -fsSL https://deno.land/install.sh | sh)
deno run -A -r https://fresh.deno.dev my-app
A Google Sheet with a header row
A SheetsAPI account and a YOUR_USER_KEY

1. Environment variables

# .env
SHEETS_USER_KEY=YOUR_USER_KEY
SHEETS_API_KEY=sk_your_api_key_here
SHEETS_BASE=https://sheetsapi.gkit.mreshank.com/api/spreadsheets

Deno reads .env with:

import "jsr:@std/dotenv/load";

Or use Deno Deploy environment variables set in the dashboard.

2. Shared sheets client

// lib/sheets.ts
const BASE = Deno.env.get("SHEETS_BASE")!;
const USER_KEY = Deno.env.get("SHEETS_USER_KEY")!;
const API_KEY = Deno.env.get("SHEETS_API_KEY")!;
 
export interface SheetRow {
  [key: string]: string;
}
export interface SheetMeta {
  total: number;
  limit: number;
  offset: number;
}
export interface SheetResponse<T> {
  data: T[];
  meta: SheetMeta;
}
 
export async function getSheet<T = SheetRow>(
  sheet: string,
  params: Record<string, string | number> = {},
): Promise<SheetResponse<T>> {
  const url = new URL(`${BASE}/${USER_KEY}/${sheet}`);
  for (const [k, v] of Object.entries(params)) url.searchParams.set(k, String(v));
 
  const resp = await fetch(url, {
    headers: { Authorization: `Bearer ${API_KEY}` },
  });
 
  if (!resp.ok) throw new Error(`SheetsAPI ${resp.status}: ${await resp.text()}`);
  return resp.json() as Promise<SheetResponse<T>>;
}
 
export async function postRow(sheet: string, row: SheetRow): Promise<SheetRow> {
  const resp = await fetch(`${BASE}/${USER_KEY}/${sheet}`, {
    method: "POST",
    headers: {
      Authorization: `Bearer ${API_KEY}`,
      "Content-Type": "application/json",
    },
    body: JSON.stringify(row),
  });
  if (!resp.ok) throw new Error(`SheetsAPI ${resp.status}: ${await resp.text()}`);
  return resp.json() as Promise<SheetRow>;
}

3. Route handler - server-rendered product list

Fresh routes export a handler for server logic and a default component for rendering:

// routes/products/index.tsx
import { define } from "../../utils.ts";
import { getSheet } from "../../lib/sheets.ts";
import type { PageProps } from "fresh";
 
interface Product {
  id: string;
  name: string;
  price: string;
  category: string;
}
 
export const handler = define.handlers<{ products: Product[]; total: number }>({
  async GET(ctx) {
    const url = new URL(ctx.req.url);
    const search = url.searchParams.get("search") ?? undefined;
    const page = parseInt(url.searchParams.get("page") ?? "1");
    const limit = 20;
 
    const { data, meta } = await getSheet<Product>("Products", {
      limit,
      offset: (page - 1) * limit,
      ...(search ? { search: `name:${search}` } : {}),
    });
 
    return ctx.render({ products: data, total: meta.total });
  },
});
 
export default function Products({ data }: PageProps<{ products: Product[]; total: number }>) {
  return (
    <div>
      <h1>Products ({data.total})</h1>
      <ul>
        {data.products.map((p) => (
          <li key={p.id}>
            <strong>{p.name}</strong> - ${p.price}
            <span class="tag">{p.category}</span>
          </li>
        ))}
      </ul>
    </div>
  );
}

Fresh renders this entirely on the server - the browser receives plain HTML with no JS.

4. Route handler - individual product page

// routes/products/[id].tsx
import { define } from "../../utils.ts";
import { getSheet } from "../../lib/sheets.ts";
 
interface Product {
  id: string;
  name: string;
  price: string;
  description: string;
}
 
export const handler = define.handlers<{ product: Product | null }>({
  async GET(ctx) {
    const { data } = await getSheet<Product>("Products", {
      search: `id:${ctx.params.id}`,
      limit: 1,
    });
    if (data.length === 0) return ctx.renderNotFound();
    return ctx.render({ product: data[0] });
  },
});
 
export default function ProductPage({ data }: { data: { product: Product | null } }) {
  if (!data.product) return <p>Not found.</p>;
  const { product: p } = data;
  return (
    <article>
      <h1>{p.name}</h1>
      <p class="price">${p.price}</p>
      <p>{p.description}</p>
    </article>
  );
}

5. API route - JSON endpoint

Fresh API routes live in routes/api/:

// routes/api/products.ts
import { define } from "../../utils.ts";
import { getSheet } from "../../lib/sheets.ts";
 
export const handler = define.handlers({
  async GET(ctx) {
    const url = new URL(ctx.req.url);
    const search = url.searchParams.get("search") ?? undefined;
    const limit = parseInt(url.searchParams.get("limit") ?? "20");
    const offset = parseInt(url.searchParams.get("offset") ?? "0");
 
    const result = await getSheet("Products", {
      limit,
      offset,
      ...(search ? { search } : {}),
    });
 
    return Response.json(result);
  },
});

6. Island - interactive search (client-side)

Fresh islands are the only components that ship JavaScript to the browser:

// islands/ProductSearch.tsx
import { useSignal } from "@preact/signals";
 
interface Product {
  id: string;
  name: string;
  price: string;
}
 
export default function ProductSearch({ initial }: { initial: Product[] }) {
  const query = useSignal("");
  const products = useSignal<Product[]>(initial);
  const loading = useSignal(false);
 
  async function search(q: string) {
    loading.value = true;
    const resp = await fetch(`/api/products?search=${encodeURIComponent(q)}&limit=20`);
    const data = await resp.json();
    products.value = data.data;
    loading.value = false;
  }
 
  return (
    <div>
      <input
        type="search"
        placeholder="Search products…"
        value={query.value}
        onInput={(e) => {
          query.value = (e.target as HTMLInputElement).value;
          search(query.value);
        }}
      />
      {loading.value && <p>Loading…</p>}
      <ul>
        {products.value.map((p) => (
          <li key={p.id}>
            {p.name} - ${p.price}
          </li>
        ))}
      </ul>
    </div>
  );
}

Use it in a route by passing initial data from the server:

// routes/products/search.tsx
import ProductSearch from "../../islands/ProductSearch.tsx";
import { getSheet } from "../../lib/sheets.ts";
 
export const handler = define.handlers({
  async GET(ctx) {
    const { data } = await getSheet("Products", { limit: 20 });
    return ctx.render({ initial: data });
  },
});
 
export default function Page({ data }: { data: { initial: unknown[] } }) {
  return <ProductSearch initial={data.initial as never} />;
}

7. Deploy to Deno Deploy

deno run -A jsr:@deno/deployctl deploy --project=my-app main.ts

Or connect your GitHub repo in the Deno Deploy dashboard - it deploys on every push to main. No Docker, no config files.

Summary

Fresh concept	SheetsAPI usage
Route handler `GET`	Fetch rows, pass to render
Route handler `POST`	Write rows from form data
API route	JSON endpoint for islands
Island	Client-side search via `/api`
Deno Deploy	Edge hosting, env vars in dashboard

Fresh's server-first philosophy means your SheetsAPI key never touches the browser - every getSheet() call happens inside a route handler, not an island.

All posts