Use Google Sheets as a Backend with Express.js

// lib/sheetsClient.js
const BASE_URL = "https://api.gkit.io/api/spreadsheets";
 
const USER_KEY = process.env.SHEETS_USER_KEY;
const API_KEY = process.env.SHEETS_API_KEY;
 
if (!USER_KEY || !API_KEY) {
  throw new Error("SHEETS_USER_KEY and SHEETS_API_KEY must be set");
}
 
/**
 * Fetch rows from a sheet with optional query params.
 * @param {string} sheetName
 * @param {Record<string, string | number>} params
 * @returns {Promise<{ data: object[], meta: { total: number, limit: number, offset: number } }>}
 */
export async function querySheet(sheetName, params = {}) {
  const url = new URL(`${BASE_URL}/${USER_KEY}/${sheetName}`);
 
  for (const [key, value] of Object.entries(params)) {
    if (value !== undefined && value !== null) {
      url.searchParams.set(key, String(value));
    }
  }
 
  const res = await fetch(url.toString(), {
    headers: { Authorization: `Bearer ${API_KEY}` },
  });
 
  if (!res.ok) {
    const body = await res.text();
    throw new Error(`SheetsAPI ${res.status}: ${body}`);
  }
 
  return res.json();
}

// routes/jobs.js
import { Router } from "express";
import { querySheet } from "../lib/sheetsClient.js";
 
const router = Router();
 
// GET /jobs
// Optional query params: search, sort, limit, offset, remote
router.get("/", async (req, res, next) => {
  try {
    const { search, sort = "-posted_date", limit = 20, offset = 0, remote } = req.query;
 
    const params = { sort, limit, offset };
    if (search) params.search = search;
    if (remote !== undefined) params.search = `remote:${remote}`;
 
    const result = await querySheet("Jobs", params);
    res.json(result);
  } catch (err) {
    next(err);
  }
});
 
// GET /jobs/:company
// Returns all listings for a specific company (case-insensitive match via SheetsAPI)
router.get("/:company", async (req, res, next) => {
  try {
    const { company } = req.params;
    const { sort = "-posted_date", limit = 50 } = req.query;
 
    const result = await querySheet("Jobs", {
      search: `company:${company}`,
      sort,
      limit,
    });
 
    if (result.data.length === 0) {
      return res.status(404).json({ error: `No jobs found for company: ${company}` });
    }
 
    res.json(result);
  } catch (err) {
    next(err);
  }
});
 
export default router;

// app.js
import express from "express";
import jobsRouter from "./routes/jobs.js";
 
const app = express();
app.use(express.json());
 
app.use("/jobs", jobsRouter);
 
app.use((err, _req, res, _next) => {
  console.error(err.message);
  res.status(502).json({ error: err.message });
});
 
app.listen(3000, () => console.log("Listening on http://localhost:3000"));

# All jobs, newest first
curl http://localhost:3000/jobs
 
# Remote jobs only
curl "http://localhost:3000/jobs?remote=true"
 
# Jobs at Acme Corp
curl http://localhost:3000/jobs/Acme%20Corp
 
# Keyword search + limit
curl "http://localhost:3000/jobs?search=title:Engineer&limit=5"

// lib/cache.js
const store = new Map();
 
/**
 * Return a cached value, or call `fn` to populate it.
 * @param {string} key
 * @param {number} ttlMs   Time-to-live in milliseconds
 * @param {() => Promise<unknown>} fn
 */
export async function withCache(key, ttlMs, fn) {
  const hit = store.get(key);
  if (hit && hit.expiresAt > Date.now()) {
    return hit.value;
  }
 
  const value = await fn();
  store.set(key, { value, expiresAt: Date.now() + ttlMs });
  return value;
}
 
/** Manually invalidate a cache key (useful after a write). */
export function invalidate(key) {
  store.delete(key);
}

// lib/sheetsClient.js  (updated)
import { withCache } from "./cache.js";
 
const CACHE_TTL = 60 * 1000; // 60 seconds
 
export async function querySheet(sheetName, params = {}) {
  const url = new URL(`${BASE_URL}/${USER_KEY}/${sheetName}`);
  for (const [key, value] of Object.entries(params)) {
    if (value !== undefined && value !== null) {
      url.searchParams.set(key, String(value));
    }
  }
 
  const cacheKey = url.toString();
 
  return withCache(cacheKey, CACHE_TTL, async () => {
    const res = await fetch(url.toString(), {
      headers: { Authorization: `Bearer ${API_KEY}` },
    });
 
    if (!res.ok) {
      const body = await res.text();
      throw new Error(`SheetsAPI ${res.status}: ${body}`);
    }
 
    return res.json();
  });
}