Taming the Spreadsheet Hydra

There is a category of spreadsheet that is something more-than-spreadsheet. You probably have one somewhere in your life. That particular workbook or sheet or Google Sheet at some point stopped being a spreadsheet in any normal sense of the word, although it continues to open and display rows and columns and headers and therefore everybody agrees to keep calling it a spreadsheet. It has a set of dependent sheets, and maybe updating scripts on their own obscure schedule, that feed feeder sheets that power something important. Tabs that aren't opened but nobody will delete because somewhere, another sheet may be looking to columns C:AZ. And there's a suboptimal formula written by a person who left last year, and within are three conditional formatting rules apparently fighting a private, digital war.

And you can't touch the blue tabs, or the green tabs. And if the import breaks, someone asks Sam, although Sam will tell you all that she hasn't ever owned the sheet, but she'll take a look. Somebody has written "DO NOT DELETE" in a cell with strong color coding and bold bordering. Which of course raises the question of what happens when you delete, and what exactly happened when somebody did delete it once? There is usually one tab called something like DATA, another called DATA_3 (where is DATA_2), and another summary tab that everybody actually looks at. That one matters a lot.

At some point nobody really understands the spreadsheet, really.

As a kid, I loved Greek mythology. Somewhere around realizing no one really gets the key spreadsheet and its workings end to end, my mind brings up the Hydra, the many-headed thing Hercules has to kill. He cuts off one head, and two more take its place. To a kid, this seemed like an incredibly unfair monster. He was doing the thing that normally solves any given monster problem, quite successfully in practically every other myth, and somehow the monster couldn't care less.

It's maybe a stupid analogy, but it works. Because somehow, despite your best intentions, every problem eventually results in another tab.

DATA_3 is ridiculous and you want to understand whether you can get rid of it, so first you make a copy of DATA_3 because obviously you're not going to experiment on the real one. Uh oh, something depends on one particular range in DATA_3, not the range you thought but it's there, so you make another temporary tab to see what's going on and copy-paste some values. You're kind of figuring out that DATA_3 actually replaced DATA_2 after a problem lost to time reached what looks like a natural end. Although DATA_2 has since disappeared without a trace, you'd very much like to know exactly where it went. Twenty minutes into your effort to remove one tab, you now have two.

Hydra.

Eventually, I realized I don't care. It's fine, it works. I don't have to kill the Hydra at all.

Here's what I told my monster: take this tab, put it here, then take that one and put it over there, but clear this first and do it every day unless you hear from me. I'm good if the heads show up where they're supposed to.

At this point, the interesting part is mostly over. The Hydra has been leashed and has daily chores now: read, clear, write, repeat. New heads are stored in JSON.

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

The core loop is readable and 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 },
});

For anyone who wants the leash: https://github.com/mpspradlin/sheet-sync.