Taming the Spreadsheet Hydra

There’s a point in every growing company when a spreadsheet stops being a tool and starts behaving like a creature — useful once, now dangerous. At first, it’s just another tab in the ecosystem. Then one day it becomes the “single” source of truth, cobbled together with imports and brittle formulas, and every edit feels like cutting off one head only to watch two more grow back.

You know the signs:

Copy‑pasting late at night to debug numbers that shouldn’t need debugging.
Colleagues asking for “the latest version” of something that should already be live.
Functions like importrange or let failing silently and freezing your whole sheet.

When the Hydra shows up, you don’t need a full data warehouse yet, but you need something sturdier than one person’s heroic copy‑paste rituals. That’s where this script came from: a nightly sync that keeps critical Google Sheets tabs aligned across multiple files. It isn’t glamorous, but it does the job: it keeps the creature contained.

What It Does

Copies select tabs from a “source” Sheet into one or more “destination” Sheets.
Overwrites the entire destination tab to clear stale data.
Runs unattended, scheduled in Google Cloud.
Sends a notification after every run — success or failure, sheet by sheet.

Think of it as lightweight ETL (Extract‑Transform‑Load) for teams that live in Sheets.

A Quick Look Under the Hood

The script is a small Node.js function using the Google Sheets API. Instead of hardcoding IDs and ranges (fine for one‑offs), I keep them in a JSON config file:

{
  "syncJobs": [
    {
      "sourceSheetId": "SOURCE_SHEET_ID",
      "sourceTabName": "FinanceData",
      "destinationSheets": [
        {
          "sheetId": "DEST_SHEET_1_ID",
          "tabName": "FinanceData"
        },
        {
          "sheetId": "DEST_SHEET_2_ID",
          "tabName": "FinanceData"
        }
      ]
    }
  ]
}

Adding a new sync is simple — update the config and the script adjusts without touching a line of code.

The flow is straightforward:

Read from the source tab
Clear the destination tab
Write fresh data

Example snippet for reading from the source tab:

const response = await sheets.spreadsheets.values.get({
  spreadsheetId: sourceSheetId,
  range: `${sourceTabName}!A:Z`,
});
const rows = response.data.values || [];
console.log(`Read ${rows.length} rows from ${sourceTabName}`);

Then clear and write:

await sheets.spreadsheets.values.clear({
  spreadsheetId: destSheetId,
  range: `${destTabName}!A:Z`,
});

await sheets.spreadsheets.values.update({
  spreadsheetId: destSheetId,
  range: `${destTabName}!A1`,
  valueInputOption: "RAW",
  requestBody: { values: rows },
});
console.log(`Synced ${rows.length} rows to ${destTabName} in ${destSheetId}`);

Why It Matters

Anyone who’s worked in business ops, rev ops, or finance knows this pain:

Critical numbers drifting out of sync at the worst possible moment.
Manual fixes introducing new errors.
Board decks derailed by last‑minute “wrong number” surprises.

This script doesn’t solve every problem. But it buys breathing room — pennies a month to replace frantic midnight copy‑pasting with something automatic and boring. The best ops fixes are like that: invisible once they work.

Glue Code That Grows With You

Most durable systems start small. A single script. A single function. Over time, they evolve — multi‑sheet syncs, logging, notifications, even pub/sub triggers or API webhooks. This pattern bridges the awkward gap between “no system” and “too much system.”

It’s not elegant, not even impressive. But it’s enough to keep the Hydra calm and let you get back to the real work.