Creating a Live Updating Google Sheet Backup On a Secondary Account

Apr 10, 2026

—

6mn read

Lately we have been seeing a bigger push from google in banning and deactivating sex workers accounts. We already know that using google drive to distribute adult content is banned. so most of us avoid using it for that. However, if you are like me, you use it for a variety of other reasons. My biggest use of google outside of gmail was google sheets. My goal was to find a way to automatically create a updating backup of certain sheets on a secondary backup google account. I use it for everything from my daily income tracker, my subreddit verification tracker, and most importantly was my CRM, the client and order tracker. This document has all of my sales data since 2022. It would be a detrimental loss. The problem I keep coming across in creating a backup is my goldfish brain. I was not going to remember to regularly create new updated backups every time I updated my CRM. So i needed something that will automatically update itself, without me having to remember to do anything. Here is the method I followed for creating a live updating google sheet backup on a seperate account, that auto updates in the background.

Setting Up

To run this properly you need to set up a few key things first:

Account and Sheet Set Up

Primary Account

This account is the original google account that owns the spreadsheet you want to backup. On this account you need:

The original spreadsheet (source spreadsheet) open
You need to give the secondary account edit access to this google sheet.
- Click Share.
  - Add the Backup account email.
  - Set it to Editor.
  - Save.

Second Account

This account is your back up google account. On this account you need:

A new blank google sheet (backup spreadsheet) open.
You need to give the primary account edit access to this google sheet.
- Click Share.
  - Add the Backup account email.
  - Set it to Editor.
  - Save.

Gathering Sheet IDs

You need to gather the sheet ID for both the source spreadsheet and the backup spreadsheet. Every Google Sheet has an ID in its URL.

Go the the sheets URL and locate the ID for both the source and the backup spreadsheet and note them down.
The sheet ID is the chunk between: /d/ and /edit

Creating the Script

Writing the Code

The following code should be ready to paste and go almost exactly.

First, you must Replace SOURCE_ID (source spreadsheet) and BACKUP_ID (backup spreadsheet) with your real spreadsheet IDs.
Once you have replaced those copy the code in full

const SOURCE_ID = "SOURCE_ID";
const BACKUP_ID = "BACKUP_ID";

const OLD_PREFIX = "__OLD__"; // temp prefix for old backup tabs we are about to delete

function syncNow() {
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(30000)) return;

  const runId = new Date().toISOString().replace(/[:.]/g, "");
  const toDelete = [];

  try {
    const srcSS = SpreadsheetApp.openById(SOURCE_ID);
    const dstSS = SpreadsheetApp.openById(BACKUP_ID);

    const dstByName = new Map(dstSS.getSheets().map(s => [s.getName(), s]));
    const srcSheets = srcSS.getSheets();

    srcSheets.forEach((srcSheet, i) => {
      const name = srcSheet.getName();

      // If a tab with the same name exists in BACKUP, rename it temporarily
      // so we can create a new copy with the real name.
      const existing = dstByName.get(name);
      if (existing) {
        // Sheet names have a length limit, keep temp name short enough
        const tempNameRaw = `${OLD_PREFIX}${runId}__${i + 1}__${name}`;
        const tempName = tempNameRaw.slice(0, 99);
        existing.setName(tempName);
        toDelete.push(existing);
        dstByName.delete(name);
      }

      // Copy the entire sheet into the BACKUP spreadsheet
      const copied = srcSheet.copyTo(dstSS);

      // The copied sheet is named like "Copy of X", rename it to the original name
      copied.setName(name);

      // Keep hidden state matching the source
      if (srcSheet.isSheetHidden()) {
        copied.hideSheet();
      } else {
        copied.showSheet();
      }

      // Optional: keep tab order matching the source order
      dstSS.setActiveSheet(copied);
      dstSS.moveActiveSheet(i + 1);
    });

    // Delete the old backup sheets that were replaced
    toDelete.forEach(s => {
      try {
        dstSS.deleteSheet(s);
      } catch (e) {
        // ignore if it cannot be deleted for some reason
      }
    });

  } finally {
    lock.releaseLock();
  }
}

/*
  Automation options:

  Option A (recommended for stability): time-based trigger (every 1 minute)
  Option B: edit + change triggers on the SOURCE spreadsheet (runs on each edit)

  You can install either one. Do not install both.
*/

function installTimeTriggerEvery1Min() {
  uninstallTriggers_();
  ScriptApp.newTrigger("syncNow")
    .timeBased()
    .everyMinutes(1)
    .create();
}

function installEditAndChangeTriggersOnSource() {
  uninstallTriggers_();
  const srcSS = SpreadsheetApp.openById(SOURCE_ID);

  ScriptApp.newTrigger("syncNow")
    .forSpreadsheet(srcSS)
    .onEdit()
    .create();

  ScriptApp.newTrigger("syncNow")
    .forSpreadsheet(srcSS)
    .onChange()
    .create();
}

function uninstallTriggers_() {
  const triggers = ScriptApp.getProjectTriggers();
  triggers.forEach(t => {
    if (t.getHandlerFunction() === "syncNow") {
      ScriptApp.deleteTrigger(t);
    }
  });
}

Create a Standalone Apps Script Project

The following steps create the script for the backup automation. You must ensure you are logged into the backup account when following the below process:

Go to script.google.com
Click New project
You’ll see a file called Code.gs

Implementing the Code

Delete everything currently in Code.gs, then paste the code we wrote above in full with no changes
Click Save (disk icon) or press Ctrl + S.

Run one manual test sync
1. At the top, select function syncNow and click Run.

Approve permissions if prompted.
This will overwrite the backup tabs to match the source tabs.
Turn on automation
1. auto sync every 1 minute
  1. Select installTimeTriggerEvery1Min

Click Run
1. This uses a time-driven trigger, which is reliable and does not require the trigger to fire on every single keystroke

Test it
- Make a tiny edit in the source sheet (something harmless).
- Wait for the next trigger cycle (or just run syncNow manually once).
- Confirm the backup sheet updates.

Final Thoughts

Best practice for not accidentally wrecking your backup

Treat the backup spreadsheet as read only in your own habits, even if you technically can edit it.
If you need to review data, review it in the backup.
If you need to change data, change it in the source.
Let the automation do the copying.

What happens if the Source sheet gets deleted, or the Source account gets banned?

If you are using this true backup method, your Backup does not disappear. The Backup spreadsheet is a separate file owned by a different account. It stays in that account’s Drive.

What will happen is:

The backup stops updating because the script can no longer read the Source.
The last successfully copied version remains intact in the Backup.

The one thing this does not protect you from

If the Backup account also gets locked, or if Google blocks access to that Drive, you could lose the backup too.

If you want real peace of mind, add an extra layer:

Download a periodic offline copy of the Backup as an Excel file and store it somewhere else (hard drive, encrypted storage, whatever your vibe is). That’s your disaster recovery layer.