Google Sheets API (stub)

Status: stub. Production depth pending.

Common operations

import { google } from 'googleapis';
const sheets = google.sheets({ version: 'v4', auth });

// Read a range (A1 notation)
const { data } = await sheets.spreadsheets.values.get({
  spreadsheetId: 'SHEET_ID',
  range: 'Intake!A2:F',
});
const rows = data.values ?? [];

// Append a row (idiomatic log-style write)
await sheets.spreadsheets.values.append({
  spreadsheetId: 'SHEET_ID',
  range: 'Intake!A:F',
  valueInputOption: 'USER_ENTERED',    // evaluate formulas; 'RAW' stores literally
  requestBody: { values: [['2n-014', 'POPIA audit', new Date().toISOString(), 'open', '', '']] },
});

// Batch read multiple ranges
await sheets.spreadsheets.values.batchGet({
  spreadsheetId: 'SHEET_ID',
  ranges: ['Intake!A:F', 'Tasks!A:D', 'Clients!A:C'],
});

// Batch update — clear + write + format in one round trip
await sheets.spreadsheets.batchUpdate({
  spreadsheetId: 'SHEET_ID',
  requestBody: {
    requests: [
      { updateCells: { range: { sheetId: 0 }, fields: 'userEnteredValue' } },      // clear sheet
      { appendCells: { sheetId: 0, rows: [{ values: [{ userEnteredValue: { stringValue: 'Client' } }] }], fields: '*' } },
    ],
  },
});

Sheets as CMS pattern

Lightweight sites / dashboards that read a Sheet on the edge, cache in KV, and render SSR:

Cron → Cloud Run / Cloudflare Worker → sheets.values.get → KV cache → HTML render

Good for: low-traffic ops dashboards, client-editable FAQ content, AI-assisted report spreadsheets.

Gotchas (high-level)

• valueInputOption: USER_ENTERED evaluates formulas. RAW treats strings literally. Wrong choice = =2+2 shown as text, or "hello" becoming #NAME?.
• append with insertDataOption: OVERWRITE replaces existing rows below the target — usually NOT what you want. Default is INSERT_ROWS (safe).
• Read quota: 100 requests / 100 sec / user. batchGet on 10 ranges = 1 request, not 10.
• Sheets aren't databases. Above ~10k rows of read-write, move to Firestore or BigQuery.

See Also

• Workspace parent
• Drive API (for creating Sheets files)