Blog

Using Google Sheets as a backend with Netlify Functions

Proxy SheetsAPI through Netlify Functions to keep your user key server-side - build a static site with real database reads and writes.

Jun 27, 20263 min read

#netlify #serverless #google-sheets #tutorial

Why Netlify Functions fit this pattern

Static sites are fast and cheap to host, but they have an obvious problem: there is nowhere to put a secret. If you call SheetsAPI directly from the browser your sk_... key is visible to anyone who opens DevTools. Netlify Functions run server-side Node.js at the edge, so you can store the key as an environment variable and proxy every SheetsAPI request through them. Your frontend only ever talks to your own domain.

The SheetsAPI contract

Every request to SheetsAPI follows the same shape:

GET  https://api.sheetsapi.io/api/spreadsheets/{userKey}/{sheetName}
POST https://api.sheetsapi.io/api/spreadsheets/{userKey}/{sheetName}
Authorization: Bearer sk_...

Read parameters (query string):

Param	Example	Effect
`search`	`status:active`	Filter rows where field equals value
`sort`	`name:asc`	Sort by field, ascending or descending
`limit`	`20`	Rows per page (default 100)
`offset`	`40`	Skip N rows for pagination
`fields`	`name,email`	Return only these columns

Write body: a JSON object whose keys match your sheet's column headers.

Response shape:

{
  "data": [{ "name": "Alice", "status": "active" }],
  "meta": { "total": 84, "limit": 20, "offset": 0 }
}

Project structure

my-site/
├── netlify/
│   └── functions/
│       ├── sheets-read.js
│       └── sheets-write.js
├── netlify.toml
└── index.html

The read function

// netlify/functions/sheets-read.js
const SHEETSAPI_BASE = "https://api.sheetsapi.io/api/spreadsheets";
 
export async function handler(event) {
  const { sheetName, ...rest } = event.queryStringParameters ?? {};
 
  if (!sheetName) {
    return {
      statusCode: 400,
      body: JSON.stringify({ error: "sheetName required" }),
    };
  }
 
  const params = new URLSearchParams(rest).toString();
  const url = `${SHEETSAPI_BASE}/${process.env.SHEETSAPI_KEY}/${sheetName}${params ? `?${params}` : ""}`;
 
  const res = await fetch(url, {
    headers: { Authorization: `Bearer ${process.env.SHEETSAPI_KEY}` },
  });
 
  const data = await res.json();
  return {
    statusCode: res.status,
    headers: { "Content-Type": "application/json" },
    body: JSON.stringify(data),
  };
}

The function forwards every query param except sheetName straight to SheetsAPI. search, sort, limit, offset, and fields all pass through without any extra wiring.

The write function

// netlify/functions/sheets-write.js
const SHEETSAPI_BASE = "https://api.sheetsapi.io/api/spreadsheets";
 
export async function handler(event) {
  if (event.httpMethod !== "POST") {
    return { statusCode: 405, body: "Method Not Allowed" };
  }
 
  const { sheetName } = event.queryStringParameters ?? {};
  if (!sheetName) {
    return {
      statusCode: 400,
      body: JSON.stringify({ error: "sheetName required" }),
    };
  }
 
  const res = await fetch(`${SHEETSAPI_BASE}/${process.env.SHEETSAPI_KEY}/${sheetName}`, {
    method: "POST",
    headers: {
      Authorization: `Bearer ${process.env.SHEETSAPI_KEY}`,
      "Content-Type": "application/json",
    },
    body: event.body,
  });
 
  const data = await res.json();
  return {
    statusCode: res.status,
    headers: { "Content-Type": "application/json" },
    body: JSON.stringify(data),
  };
}

Clean URLs with netlify.toml redirects

Without redirects your frontend has to call /.netlify/functions/sheets-read. A two-line redirect gives you nicer paths:

# netlify.toml
[build]
  publish = "."
 
[[redirects]]
  from = "/api/sheets/read"
  to   = "/.netlify/functions/sheets-read"
  status = 200
 
[[redirects]]
  from = "/api/sheets/write"
  to   = "/.netlify/functions/sheets-write"
  status = 200

Calling from the browser

Reading on page load (onMount pattern - works in vanilla JS, React, Svelte, etc.):

async function loadProducts() {
  const res = await fetch("/api/sheets/read?sheetName=products&sort=name:asc&limit=20");
  const { data, meta } = await res.json();
  console.log(`Showing ${data.length} of ${meta.total} products`);
  renderTable(data);
}
 
// Vanilla JS
document.addEventListener("DOMContentLoaded", loadProducts);

Writing from a form submit:

document.getElementById("contact-form").addEventListener("submit", async (e) => {
  e.preventDefault();
  const form = new FormData(e.target);
 
  await fetch("/api/sheets/write?sheetName=leads", {
    method: "POST",
    headers: { "Content-Type": "application/json" },
    body: JSON.stringify(Object.fromEntries(form)),
  });
 
  alert("Submitted!");
});

Setting SHEETSAPI_KEY in Netlify

Open your site in the Netlify dashboard.
Go to Site configuration → Environment variables.
Click Add a variable, set the key to SHEETSAPI_KEY and paste your sk_... value.
Redeploy - the variable is available to all functions immediately.

You can also set it locally for netlify dev:

# .env  (add to .gitignore)
SHEETSAPI_KEY=sk_YOUR_USER_KEY

Caching reads with On-demand Builders

If your sheet data changes infrequently, wrap the read function as an On-demand Builder so Netlify caches the response at the CDN edge:

import { builder } from "@netlify/functions";
 
async function handler(event) {
  // ... same read logic ...
}
 
// Cache for 60 seconds, then revalidate in background
export const config = { cache: "manual" };
export default builder(handler);

This turns each unique ?sheetName=...&search=... URL into a cached edge response. The first visitor pays the SheetsAPI latency; everyone after them gets the CDN copy until the TTL expires.

Which approach should you use?

Approach	Key stays secret	Caching	Latency	Best for
Direct browser → SheetsAPI	No	No	~200 ms	Internal tools, no secrets needed
Netlify Function (this guide)	Yes	No (by default)	~250 ms	Most production sites
Netlify Edge Function	Yes	No	~50 ms	High-traffic, latency-sensitive reads
On-demand Builder	Yes	Yes (CDN)	~10 ms cached	Mostly-static data, public pages

For most projects the plain Netlify Function is the right default. Add On-demand Builders when a particular sheet-backed page is under load. Reach for Edge Functions only if you are already measuring function cold-start latency as a problem.

Next steps

Add a simple HMAC or shared secret between your frontend and function if you want to prevent other callers from hitting your write endpoint.
Use search=status:pending to build a lightweight admin queue that your team manages directly in Sheets.
Combine limit and offset query params with a pagination component to page through large datasets without pulling all rows at once.

All posts