Sync Google Sheets to a Database in Real Time with Apps Script

By Swiftools Team · Published February 18, 2026 · 9 min read

Programming code on a dark monitor

Google Sheets is, often, the right interface for a small piece of business data. A team can edit it without permissions or migrations, the formulas are powerful enough for ad hoc analysis, and you don't need to build a CRUD app for something that has 50 rows. But Sheets is wrong for the second job most teams ask of it: being the source of truth that other software queries against.

The pattern below gives you the best of both: people keep editing in Sheets, and every change replicates within seconds to a real database (Postgres, MySQL, Supabase, anything with an HTTP API), which is then queryable by whatever needs to consume it. It uses an Apps Script onEdit trigger plus a small webhook endpoint. Total code: under 100 lines.

Why this is usually the right architecture

The classic alternative is to either (a) move the data fully out of Sheets and build a real admin UI - expensive, slow, and resented by the people who liked Sheets - or (b) periodically export the sheet to CSV and re-import it into the database. Option (b) is fine until your second consumer needs the data 5 minutes after an edit and the next batch export isn't for another 50 minutes.

Real-time sync from Sheets to a database keeps the editing experience that teams actually like, while making the data immediately available to dashboards, APIs, automated workflows, and other systems. The cost is the small operational footprint of running the webhook endpoint - typically a serverless function on Vercel, Cloudflare Workers, or AWS Lambda, costing pennies per month at modest volume.

Architecture overview

[Google Sheet] -- onEdit trigger --> [Apps Script]
                                         |
                                         | HTTPS POST (JSON)
                                         v
                              [Webhook endpoint]
                                         |
                                         | INSERT/UPDATE
                                         v
                                  [Database]

Three components. The Sheet is unchanged from a user's perspective. The Apps Script is a few dozen lines of glue. The webhook endpoint is a small piece of code (Node, Python, Go - your choice) that receives the JSON payload and writes to your database.

Step 1: The Apps Script side

In your Sheet, open Apps Script (Extensions → Apps Script). Paste this and replace the constants:

const WEBHOOK_URL = 'https://your-webhook.example.com/sheet-sync';
const SHARED_SECRET = 'long-random-string-shared-with-server';

function onEdit(e) {
  // Sanity guard - only react to actual edits
  if (!e || !e.range) return;

  const sheet = e.source.getActiveSheet();
  const range = e.range;
  const row = range.getRow();

  // Ignore header row
  if (row === 1) return;

  // Read the full row that was changed
  const lastCol = sheet.getLastColumn();
  const headerRange = sheet.getRange(1, 1, 1, lastCol);
  const headers = headerRange.getValues()[0];
  const rowValues = sheet.getRange(row, 1, 1, lastCol).getValues()[0];

  // Build a payload object
  const payload = { sheet: sheet.getName(), row: row, data: {} };
  headers.forEach((h, i) => { payload.data[h] = rowValues[i]; });

  // Fire-and-forget POST to the webhook
  try {
    UrlFetchApp.fetch(WEBHOOK_URL, {
      method: 'post',
      contentType: 'application/json',
      headers: { 'X-Shared-Secret': SHARED_SECRET },
      payload: JSON.stringify(payload),
      muteHttpExceptions: true
    });
  } catch (err) {
    // Log to a 'sync_errors' tab so you can audit later
    const errSheet = e.source.getSheetByName('sync_errors')
      || e.source.insertSheet('sync_errors');
    errSheet.appendRow([new Date(), sheet.getName(), row, String(err)]);
  }
}

That's the entire Sheets side. Every time someone edits a non-header cell, the script fires the full updated row to your webhook within milliseconds.

Step 2: The webhook endpoint

A minimal Node.js (Express) version, deployable to Vercel, Cloudflare Workers, or any serverless platform:

// /api/sheet-sync.js
import { Pool } from 'pg';
const pool = new Pool({ connectionString: process.env.DATABASE_URL });

export default async function handler(req, res) {
  if (req.method !== 'POST') return res.status(405).end();
  if (req.headers['x-shared-secret'] !== process.env.SHARED_SECRET) {
    return res.status(401).end();
  }

  const { sheet, row, data } = req.body;
  if (!sheet || !row || !data) return res.status(400).end();

  // Upsert into a table named after the sheet (sanitize first!)
  const allowedSheets = ['customers', 'orders', 'inventory'];
  if (!allowedSheets.includes(sheet)) return res.status(400).end();

  // Convert keys/values to columns/params
  const cols = Object.keys(data);
  const vals = Object.values(data);
  const placeholders = vals.map((_, i) => `$${i + 2}`).join(', ');
  const updateSet = cols.map((c, i) => `"${c}" = $${i + 2}`).join(', ');

  const sql = `
    INSERT INTO "${sheet}" (sheet_row, ${cols.map(c => `"${c}"`).join(', ')})
    VALUES ($1, ${placeholders})
    ON CONFLICT (sheet_row) DO UPDATE SET ${updateSet}
  `;
  await pool.query(sql, [row, ...vals]);
  res.status(200).json({ ok: true });
}

The key idea: sheet_row is the primary key, matching the spreadsheet row number. ON CONFLICT ... DO UPDATE handles both new rows and edits to existing rows with the same SQL statement (an upsert). Postgres syntax shown; MySQL's equivalent is INSERT ... ON DUPLICATE KEY UPDATE.

Step 3: Handle deletes (the tricky part)

Apps Script's onEdit only fires when an edit happens - not when a row is deleted. If someone deletes row 7, the script doesn't notice. Two reasonable patterns to deal with this:

Pattern A: soft delete. Add a "deleted" column to the sheet. Instead of right-clicking → Delete, the convention becomes "set deleted = TRUE." The sync sees the edit, the database row keeps existing but is marked deleted, and any consumer queries can filter WHERE NOT deleted. Simplest by far.

Pattern B: periodic reconciliation. Add a time-based trigger that runs every 10 minutes, reads all current row IDs from the sheet, and deletes any database rows whose sheet_row isn't in that list. More work, but handles the case where you really do need hard deletes without changing user behavior.

Step 4: Initial backfill

The script above only catches future edits. To populate the database with what's already in the sheet, run this once from the Apps Script editor:

function backfillAll() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const data = sheet.getDataRange().getValues();
  const headers = data[0];
  for (let i = 1; i < data.length; i++) {
    if (data[i].every(v => v === '')) continue;  // skip empty rows
    const payload = { sheet: sheet.getName(), row: i + 1, data: {} };
    headers.forEach((h, j) => { payload.data[h] = data[i][j]; });
    UrlFetchApp.fetch(WEBHOOK_URL, {
      method: 'post',
      contentType: 'application/json',
      headers: { 'X-Shared-Secret': SHARED_SECRET },
      payload: JSON.stringify(payload)
    });
    Utilities.sleep(200);  // gentle throttling
  }
}

Runs once. Replays every existing row through the same webhook. Takes about 10-15 minutes for 1,000 rows.

Operational notes

Authentication. The X-Shared-Secret header is the minimum bar - both sides know a secret string, and the endpoint rejects requests without it. For higher security, use HMAC signatures on the payload. GitHub's webhook signature spec is a good reference implementation.

Rate limits. Apps Script can make about 20,000 URL fetches per day on a free account, which is plenty for normal editing volume. If you're seeing more than that, you probably have a runaway script or someone is doing a bulk paste of 10,000 rows.

Retries. The script above is fire-and-forget. If the webhook returns a 500, the edit is logged to sync_errors but not retried. For most use cases this is fine; for higher reliability, use a small retry loop with exponential backoff before falling back to the error log.

Latency. End-to-end latency from edit to database write is typically 1-3 seconds. onEdit is a "simple" trigger and runs synchronously, but Apps Script execution time itself isn't deterministic - expect occasional spikes to 5+ seconds.

When to use this pattern (and when not to)

Use it when: the Sheet is the canonical editing interface and you need the data available to other systems quickly. Internal CRMs, ops dashboards, lightweight admin tools, and small business apps where Sheets is the customer-facing surface area.

Don't use it when: the data needs strong consistency guarantees (Sheets is eventually-consistent, not transactional). Don't use it for financial transactions, inventory with reservation logic, or anything where two simultaneous edits could conflict and cause data loss.

Don't use it when: the volume is high enough that a real database editor would be cheaper. If 20 people are editing the sheet constantly and you're paying for engineering time to maintain the sync, build the real app.

What we use this for ourselves

An internal "feature flags" sheet where the product team can toggle flags without an engineer, with the changes appearing in our backend within seconds. A "support response templates" sheet where the customer success team owns the canonical copy, and the help app reads it live. A "pricing experiments" sheet where the growth team controls the pricing variants without a deploy. Each one of these started as "let me just put it in a sheet for now" and the sync pattern is what let it stay there.

Sources & Further Reading

Related Articles