Google Sheets as a backend API: what actually works

Google Sheets Can Serve JSON to a Frontend

Most developers treat Google Sheets as a reporting tool. Fewer know it can function as a live JSON API. Here is how it works: you write a Google Apps Script that handles HTTP GET requests and returns data from the spreadsheet as JSON, then deploy that script as a web app. Your frontend fetches from the deployment URL and gets structured data back.

The endpoint looks like this:

function doGet(e) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Products');
  const rows = sheet.getDataRange().getValues();
  const headers = rows[0];
  const data = rows.slice(1).map(row => {
    const obj = {};
    headers.forEach((h, i) => obj[h] = row[i]);
    return obj;
  });
  return ContentService
    .createTextOutput(JSON.stringify(data))
    .setMimeType(ContentService.MimeType.JSON);
}

A non-technical team member can edit the spreadsheet. The frontend reflects those changes on the next fetch. No backend infrastructure, no deployment pipeline, no database migrations.

The 5 Million Cell Limit Is Real but Rarely a Problem

Google Sheets has a hard cap of 5 million cells per spreadsheet. That sounds restrictive until you do the math: a sheet with 100 columns and 50,000 rows uses exactly 5 million cells. For most internal tools, reporting dashboards, and small product catalogs, you will never get close.

When you do get close, the standard pattern is to keep the admin-facing interface in Sheets while offloading the actual data storage to BigQuery or Firestore. Apps Script can write directly to both via their respective APIs.

LAMBDA Functions Make Sheets Act Like a Programming Language

The LAMBDA function, which arrived in Google Sheets in 2022, fundamentally changed what is possible in a spreadsheet. Before LAMBDA, if you wanted to reuse a complex formula logic across a sheet, you had to copy-paste it and manually update each instance. LAMBDA lets you define named functions with parameters.

You can write something like:

=LAMBDA(price, tax_rate, price * (1 + tax_rate))(B2, 0.11)

Then save that as a named function called PRICE_WITH_TAX. From that point, you call it like any built-in formula. Combined with REDUCE, MAP, and SCAN, you can write recursive logic directly in a cell. Sheets starts to look less like a spreadsheet and more like a functional programming environment.

Sheets Can Send Emails, Post to Slack, and Call Webhooks

Apps Script gives you access to Gmail, Calendar, Drive, and UrlFetchApp, which is an HTTP client. That last one is the key. UrlFetchApp.fetch() can call any URL, which means any Sheets automation can also call a Slack webhook, trigger a Zapier zap, send data to an n8n instance, or hit your own backend API.

A practical example: a sales team keeps a pipeline sheet. When a deal stage changes to Closed Won, an onEdit trigger fires, checks the new value, and posts a celebration message to the team Slack channel with the deal name and value. The whole thing is 15 lines of script.

Google Sheets Has a Built-in Version History Going Back 30 Days

Every change to a Google Sheet is tracked automatically. You can see who changed what cell, when, and what the previous value was. File version history goes back 30 days for regular accounts and indefinitely for Google Workspace accounts. For data stored in Sheets, this is a free audit trail that most database systems require paid plugins or manual implementation to replicate.

Developers who use Sheets as a lightweight database often do not realize this exists, and then are pleasantly surprised when a client accidentally deletes a column and they can restore it in 30 seconds.

---

Sources

• Google Sheets cell limit: Google Support, Google Sheets limits - support.google.com
• LAMBDA function release: Google Workspace Updates, LAMBDA and related functions now available in Google Sheets (2022) - workspaceupdates.googleblog.com
• Apps Script web app documentation: Google Developers, Web Apps - developers.google.com/apps-script/guides/web
• UrlFetchApp reference: Google Apps Script API - developers.google.com/apps-script/reference/url-fetch
• Sheets version history: Google Support, See earlier versions of a file - support.google.com