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 hungry.

At first it’s just another tab in the ecosystem, tracking something that needed tracking. Then one day it becomes the “single source of truth,” stitched together with imports and brittle formulas, and every edit feels like cutting off one head only to watch two grow back.

The signs arrive gradually. You’re copy-pasting at 11pm to debug numbers that shouldn’t need debugging. Colleagues ask for “the latest version,” and you realize you’re not sure which one that is. IMPORTRANGE fails silently and no one notices until the board deck looks wrong.

The creature doesn’t announce itself. It accumulates.

I inherited one like this. A finance sheet that had become load-bearing without anyone deciding it should be. Three teams depended on it. Two more had built sheets on top of it, importing ranges that imported other ranges. The original author had left. Tabs referenced ghosts. Still, every month, someone had to get the numbers out.

The instinct is to rip it out and replace it with something proper. A real database. A real pipeline. That assumes time and patience you usually don’t have. What you have is next Monday.

So you build a cage.

Mine was a small script. Node.js. The Google Sheets API. A JSON file listing which tabs to copy where. Nothing clever. Read from the source, clear the destination, write fresh data. Run nightly. Send a notification when it finishes so someone knows whether it held.

The point wasn’t elegance. It was containment.

Within a few weeks, the midnight copy-paste sessions stopped. No one asked which version was current. The original sheet still existed, labyrinth intact, but it had been reduced to a feeder. The fragile logic lived upstream. The outputs lived somewhere sturdier.

This is the strange thing about operational work. The success state is silence. When it runs correctly, nothing happens. No alerts. No emergencies. Just numbers moving where they need to go while you sleep.

Most durable systems start this way. A small script built to hold the line until something bigger makes sense. Sometimes it evolves. Sometimes it quietly runs for years. Either way, the creature stays calm.

And you get your evenings back.

The logic is four steps: read a source tab, clear the destination, write fresh data, run it again tomorrow night. Or: read, clear, write, repeat.

I put the targets in a JSON file so that adding a new job doesn't mean touching the script at 11pm when something breaks:

{
  "syncJobs": [
    {
      "sourceSheetId": "SOURCE_ID",
      "sourceTabName": "FinanceData",
      "destinationSheets": [
        { "sheetId": "DEST_ID_1", "tabName": "FinanceData" }
      ]
    }
  ]
}

The core loop is intentionally boring:

const response = await sheets.spreadsheets.values.get({
  spreadsheetId: sourceSheetId,
  range: `${sourceTabName}!A:Z`,
});

const rows = response.data.values || [];

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 },
});

GitHub repo here: https://github.com/mpspradlin/sheet-sync.