Taming the Spreadsheet Hydra

There is a category of spreadsheet that is something more-than-spreadsheet. You probably have one somewhere in yoru 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 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 looking. 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 happend 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 that is being referenced probably somewhere. And then you discover something depends on one particular range in DATA_3, not the range you thought but its there, so you make a temporary tab to see what's going on. Someone tells you DATA_3 actually replaced DATA_2 after an import problem reached its natural end six months ago, and although DATA_2 has since disappeared without a trace, you would very much like to know where it went. Twenty minutes into your effort to remove one tab, you have created two tabs.

Hydra.

Eventually, I realized I don't have the same constraints as Hercules. I don't have to kill the Hydra at all.

Here's what I told my monster: take this tab and put it here, and take that one and put it over there, but clear this first and do it all again tomorrow night. I honestly don't care if seventeen heads remain seventeen heads. I just wanted to know which ones would be where in the morning.

Which would be a deeply disappointing ending to the Greek myth. Hercules is at peace with the Hydra, writes some JavaScript, and goes home.

But I'm good if the heads show up where they're supposed to.

The logic is four steps: read a source tab, clear the destination, write fresh data, run it again tomorrow night. 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.