Build a JSON API endpoint with doGet and doPost

Northwind keeps plenty of small reference data in Sheets — a price list, a status board, a set of project codes. Now and then another tool needs to read or write that data: a website widget, a Zapier step, a teammate’s script. You do not want to give every one of them edit access to the spreadsheet, and you do not want to stand up a real server.

Apps Script lets you publish a script as a web app, and a web app with doGet and doPost handlers is, effectively, a tiny JSON API. This script exposes one sheet: a GET request reads every row back as JSON, and a POST request with a JSON body appends a new row. No server, no database — just the sheet you already have.

What you’ll need

• A Google Sheet to act as the data store. The first sheet’s row 1 holds the field names — these become the JSON keys, so name them sensibly.
• The spreadsheet’s file ID, pasted into the DATA_SHEET constant.
• The script deployed as a Web app (see “Deploy it” below) — that step is what turns doGet/doPost into a live URL.

The script

// The spreadsheet exposed by this API.
const DATA_SHEET = '1abcDataSheetId';

/**
 * Returns a JSON ContentService response with the given object,
 * serialised. Used by both handlers so the shape stays consistent.
 */
function jsonResponse(obj) {
  return ContentService.createTextOutput(JSON.stringify(obj))
    .setMimeType(ContentService.MimeType.JSON);
}

/**
 * Handles GET requests: reads the whole sheet and returns every row
 * as a JSON array of objects keyed by the header row.
 */
function doGet() {
  const rows = SpreadsheetApp.openById(DATA_SHEET).getSheets()[0]
    .getDataRange().getValues();

  // An empty sheet (or headers only) returns an empty array.
  if (rows.length < 2) return jsonResponse([]);

  // Turn each data row into an object keyed by the header names.
  const [header, ...dataRows] = rows;
  const data = dataRows.map((r) =>
    Object.fromEntries(header.map((key, i) => [key, r[i]]))
  );
  return jsonResponse(data);
}

/**
 * Handles POST requests: parses a JSON body and appends a row,
 * placing each value under its matching header column.
 */
function doPost(e) {
  // Guard against a missing or empty request body.
  if (!e || !e.postData || !e.postData.contents) {
    return jsonResponse({ ok: false, error: 'No request body' });
  }

  // 1. Parse the JSON body sent by the caller.
  let body;
  try {
    body = JSON.parse(e.postData.contents);
  } catch (err) {
    return jsonResponse({ ok: false, error: 'Invalid JSON' });
  }

  // 2. Read the header row to know the column order.
  const sheet = SpreadsheetApp.openById(DATA_SHEET).getSheets()[0];
  const header = sheet.getRange(1, 1, 1, sheet.getLastColumn())
    .getValues()[0];

  // 3. Append a row, taking each field from the body by header name.
  sheet.appendRow(header.map((key) => body[key] ?? ''));
  return jsonResponse({ ok: true });
}

How it works

1. jsonResponse is a small helper both handlers share: it serialises an object and tags the response as JSON so callers parse it correctly.
2. doGet runs on every GET request. It reads the whole sheet with getDataRange, and if there is nothing but a header row (or less) it returns an empty array.
3. Otherwise it splits off the header and maps each data row into an object, pairing each header name with the matching cell — so the API output mirrors the sheet’s columns.
4. doPost runs on every POST request. It first guards against a missing body, then tries to JSON.parse the contents and returns a clear error if that fails — malformed input should not be a stack trace.
5. It reads the header row to learn the column order, then builds the new row by pulling each field from the posted body by name. The ?? '' means a field the caller omits becomes an empty cell rather than undefined.
6. appendRow adds the row and the handler returns { ok: true }.

Example run

With the sheet’s headers as name, email, role, a GET request to the web app URL returns:

[
  { "name": "Priya", "email": "[email protected]", "role": "Designer" },
  { "name": "Awadesh", "email": "[email protected]", "role": "Lead" }
]

A POST to the same URL with this body:

{ "name": "Sam", "email": "[email protected]", "role": "Developer" }

appends a row and returns { "ok": true }. The next GET includes Sam.

Deploy it

doGet and doPost only become reachable once the script is deployed:

1. In the Apps Script editor click Deploy → New deployment.
2. Choose type Web app.
3. Set Execute as to yourself and Who has access to suit the caller — “Anyone” for a public endpoint, or restrict it to your domain.
4. Click Deploy, approve the authorisation prompt, and copy the web app URL.
5. Test it: open the URL in a browser for the GET, and use curl or any HTTP client to send a POST.

Each time you change the code, create a new version of the deployment so the live URL serves the update.

Watch out for

• “Who has access: Anyone” means anyone with the URL can read and write the sheet. There is no authentication here — for anything sensitive, require a shared token in the request and check it before reading or writing.
• doGet returns the entire sheet on every call. For a large sheet that is slow and wasteful — add a query parameter (e.parameter) to filter or paginate.
• A web app processes requests one at a time. Two POSTs arriving together are fine for low traffic, but heavy concurrent writes can race — wrap appendRow in a LockService lock if that is a real risk.
• ContentService cannot set arbitrary response headers, so it does not support custom CORS. Browser fetch from another origin may be blocked depending on the request; server-to-server calls are unaffected.
• Web apps have execution-time and UrlFetchApp-style quotas. A busy public endpoint can hit them — this pattern suits low-to-moderate traffic, not a high-throughput production API.