In every growing company, there's a moment when a spreadsheet becomes a liability. That moment usually happens right after you realize what you're working on is no longer one part of an interconnected/whole truth, but the "single" source of truth. The symptoms are easy to spot as operational problems, like:

• you're constantly having to debug via copy-pasting from other sheets,
• people ask for "the latest version" of something that should be live, or
• more brittle functions start to fail/time out (importrange, let, as two examples) and kill the usability of your sheet.

Eventually, you need something more than a normal sheet but less than a real data system/warehouse/etc. So, I put together a nightly sync script that keeps critical Google Sheets tabs aligned across different target files^[1].

Specifically, it:

• Copies select tabs from a “source” Sheet into one or more “destination” Sheets.
• Overwrites the entire destination tab, eliminating stale data.
• Runs unattended, scheduled in Google Cloud.
• Notifies me of the success or lack thereof of each attempt, sheet by sheet.

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

A Quick Look Under the Hood

The whole thing boils down to a simple Node.js script using the Google Sheets API. Instead of hardcoding sheet IDs and ranges into the script itself (which is fine and possible, and which I do sometimes), you can use 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"
        }
      ]
    }
  ]
}

This lets you add or change sync targets without touching the script code. New sheet? Just update the config.

The core sync flow works very simply:

• Read source tab
• Clear destination tab
• Write data to destination

Example snippet for reading from a 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 to destination:

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

If you work in business or revenue operations, or finance, this solves a lot of problems all at once:

• Eliminates manual copy-paste errors.
• Keeps shared sheets aligned daily.
• Simple to extend - one config file for multiple syncs.
• Runs reliably for cents per month on Google Cloud Functions.

It's not a crowning achievement in computer science, but it's the kind of small automation that saves hours of work and prevents embarrassing/frustrating “wrong number” moments.

The script runs as a Google Cloud Function triggered by Cloud Scheduler, serverless and cheap.

A Useful Pattern: “Glue Code” That Grows With You

What I like about this pattern:

1. Starts simple (one script, one function).
2. Grows with complexity (multi-sheet sync, logging, notifications).
3. Can scale or hook into bigger systems (pub/sub triggers, API webhooks) if needed.

Most importantly, it easily bridges the gap between no system at all and too much system.

Sometimes, successful ops work is just writing easy to understand, automated "glue."