Turn a Google Sheet into a REST API, and cache it properly

The easy part of using a Google Sheet as an app's data source is letting people edit it. The hard part starts when the app needs stable JSON, named fields, filtering, pagination, and enough caching that a traffic spike does not turn into a wall of Google quota errors.

I kept seeing the same small backend built around that gap. It fetched a cell range, treated the first row as headers, zipped every other row into an object, then added CORS and a little cache. The spreadsheet was simple. The adapter around it was the part that became infrastructure.

That is the job PasteSheet handles. You publish a sheet as a read-only REST endpoint, and consumers get row-oriented JSON instead of Google's cell ranges. In this context, an endpoint is just a URL an app can request over HTTP to receive the current rows as data.

Start with the API shape you actually want

Google's official Sheets API is cell-oriented. You request a range such as Products!A1:D50 and receive a two-dimensional array. If row one contains headers, your application still has to combine those headers with every later row.

Most applications want records instead:

{
  "data": [
    { "id": 1, "name": "Blue Widget", "price": 19.99, "in_stock": true },
    { "id": 2, "name": "Red Widget", "price": 24.5, "in_stock": false }
  ],
  "total": 128,
  "limit": 2,
  "offset": 0
}

Column names become keys, values keep useful types, and pagination metadata sits beside the rows. That shape is easy to consume from a browser, a server, or a script without a Sheets-specific SDK.

If you only need a one-time snapshot, the free Google Sheets to JSON converter is enough. An endpoint solves a different problem. It gives the app a URL that stays current when someone edits the source sheet.

Four ways to get there

The first decision is whether you want to connect an account or publish a data source. Google's API connects an application to Google through a Cloud project and credentials. A published endpoint exposes the rows that consumers need, without giving them access to the rest of the account.

Raw CSV is underrated when the task is genuinely small. Apps Script is a good choice when the request must call other Google services or write back to the document. The official API is the right foundation when each user needs their own Google permissions.

The hosted route earns its keep when the sheet is a source of truth that many consumers only need to read. That is the publish side of the line: one sheet, one stable data contract, many readers.

Publish the first endpoint

For the URL-paste flow, give the sheet a header row and set its general access to Anyone with the link, Viewer. Then paste the share URL into PasteSheet and save the endpoint. The full sheet-to-REST walkthrough covers the setup screen and common sharing mistakes.

The first request can be as small as this:

curl 'https://pastesheet.com/api/your-endpoint-id?limit=2'

A specific sheet tab becomes another path segment:

curl 'https://pastesheet.com/api/your-endpoint-id/Products?limit=2'

There is no key on a public endpoint. If the endpoint itself is private, send its ps_ key as a bearer token instead of putting a secret into the browser:

curl 'https://pastesheet.com/api/your-endpoint-id' \
  -H 'Authorization: Bearer ps_your_api_key'

The source sheet and the endpoint are separate security decisions. The URL-paste flow reads a link-shared source. A private endpoint controls who can read the hosted result.

Read it from JavaScript and Python

The response is plain JSON and public endpoints allow cross-origin browser requests. A front end can fetch active products directly:

const response = await fetch(
  "https://pastesheet.com/api/your-endpoint-id" +
  "?status=active&sort=price&order=asc&limit=20",
);

if (!response.ok) {
  throw new Error(`Sheet request failed: ${response.status}`);
}

const { data, total } = await response.json();

console.log(`${total} active products`);
data.forEach((product) => console.log(product.name, product.price));

Python needs no Google client library either:

import requests

response = requests.get(
    "https://pastesheet.com/api/your-endpoint-id",
    params={
        "status": "active",
        "sort": "price",
        "order": "asc",
        "limit": 20,
    },
    timeout=10,
)
response.raise_for_status()

payload = response.json()

for product in payload["data"]:
    print(product["name"], product["price"])

Exact filters, sorting, and limit/offset pagination all use ordinary URL parameters. The server applies them to the row set, so every client does not need to download the entire sheet and repeat the same work.

Put the cache in front of Google

A sheet-backed endpoint fails when its traffic pattern is allowed to become Google's traffic pattern. If 1,000 page views each trigger a fresh Sheets read, the application has not really built an API. It has built a request multiplier.

The useful architecture is small:

apps and browsers
       |
       v
REST endpoint --> cached rows
                     |
                     | cache miss or refresh
                     v
                Google Sheet

The first request after expiry refreshes the row set. Requests during the cache window filter, sort, and paginate those cached rows without reading Google again. Ten thousand visitors can therefore become one upstream sheet read per cache window instead of ten thousand upstream reads.

This is more than a performance optimization. Google enforces per-minute read quotas, and a backend using one service account concentrates every visitor into the same quota bucket. Retries can smooth a brief spike, but only caching removes the repeated reads.

PasteSheet uses a five-minute cache on the Free plan. Paid plans can set a TTL from 30 seconds to one hour, and a manual refresh can make an important edit visible immediately. The detailed Google Sheets JSON caching guide covers TTL choice, stale data, and why an in-memory cache is not enough once an app runs on several instances.

Choose the TTL from the data's freshness requirement, not from the number of visitors. A public event schedule might tolerate five minutes. Inventory shown during checkout probably cannot. If the application needs every cell edit to be visible synchronously, a cached spreadsheet is the wrong storage layer.

Where this pattern stops working

A published sheet endpoint is a strong fit for catalogs, directories, FAQs, schedules, configuration, and other flat data that changes less often than it is read. It is not a replacement for a transactional database.

PasteSheet is read-only by design. It will not handle concurrent writes, relational joins, per-row permissions, or a workflow where the application must update the spreadsheet. The cache also means readers can briefly see an older copy after an edit. Those are useful boundaries, not details to discover after launch.

If the job is to let many apps or people read data that a small team maintains in a sheet, publishing a cached endpoint removes a surprising amount of glue code. If the job is collaborative writes or strict real-time consistency, use Google's authenticated API or a database built for that workload.

I build PasteSheet: paste a Google Sheet URL and get a cached JSON API plus a read-only MCP server your AI agent can query. Free tier, no credit card, no Google Cloud project.