Restore a sheet to any previous snapshot

Northwind’s Pipeline sheet is the shared record of every live opportunity, and half a dozen people edit it every day. That makes it fragile. A bad paste, a deleted column, a sort that scrambled the rows — and the version history is no help, because nobody can remember which of two hundred unlabelled edits was the last good state.

This pair of functions gives Northwind a proper safety net. backupSheet makes a dated copy of the whole spreadsheet into a backups folder, and runs on a schedule. restoreFromBackup takes the name of one of those copies and rolls the live Pipeline sheet back to it. Because the backups are named with their timestamp, picking the right one to restore is obvious.

What you’ll need

• The Pipeline spreadsheet you want to protect, and its file ID.
• A dedicated Drive folder for the backups, and its folder ID. Keep it separate so the dated copies do not clutter your working space.
• Edit access to both — the restore deletes and recreates every tab in the live file, so it needs full rights.

The script

// The live spreadsheet being protected.
const SOURCE_FILE_ID = '1abcPipelineSheetId';

// The Drive folder that holds the dated backup copies.
const BACKUP_FOLDER_ID = '1abcBackupsFolderId';

// Prefix used when naming a backup, e.g. "Pipeline backup 2025-10-11-0200".
const BACKUP_PREFIX = 'Pipeline backup';

/**
 * Makes a timestamped copy of the source spreadsheet into the backup
 * folder. Safe to run on a schedule.
 */
function backupSheet() {
  // 1. Build a sortable timestamp so the newest backup sorts last.
  const stamp = Utilities.formatDate(new Date(), 'GMT', 'yyyy-MM-dd-HHmm');
  const name = `${BACKUP_PREFIX} ${stamp}`;

  // 2. Copy the whole file into the backup folder.
  const folder = DriveApp.getFolderById(BACKUP_FOLDER_ID);
  DriveApp.getFileById(SOURCE_FILE_ID).makeCopy(name, folder);

  Logger.log('Created backup: ' + name);
}

/**
 * Rolls the live Pipeline spreadsheet back to a named backup by
 * replacing all of its tabs with the backup's tabs.
 *
 * @param {string} backupFileName The exact name of the backup to restore.
 */
function restoreFromBackup(backupFileName) {
  // 1. Find the named backup in the folder. Bail out if it is missing.
  const folder = DriveApp.getFolderById(BACKUP_FOLDER_ID);
  const matches = folder.getFilesByName(backupFileName);
  if (!matches.hasNext()) {
    throw new Error('Backup not found: ' + backupFileName);
  }

  const backup = SpreadsheetApp.openById(matches.next().getId());
  const target = SpreadsheetApp.openById(SOURCE_FILE_ID);

  // 2. Copy every tab from the backup into the live file first, so the
  //    file is never left with zero sheets if something fails midway.
  for (const sheet of backup.getSheets()) {
    sheet.copyTo(target).setName(sheet.getName());
  }

  // 3. Delete the original (pre-restore) tabs. They are the ones whose
  //    names are NOT among the freshly copied backup tabs.
  const restoredNames = new Set(
    backup.getSheets().map((s) => s.getName())
  );
  for (const sheet of target.getSheets()) {
    // copyTo appends " Copy" — a tab is "old" only if its bare name is
    // a restored name and it is not itself one of the new copies.
    const name = sheet.getName();
    const isCopy = name.startsWith('Copy of ') ||
      restoredNames.has(name.replace(/ Copy$/, '')) && name.endsWith(' Copy');
    if (!isCopy && !restoredNames.has(name)) {
      target.deleteSheet(sheet);
    }
  }

  Logger.log('Restored Pipeline from: ' + backupFileName);
}

How it works

1. backupSheet formats the current time into a sortable yyyy-MM-dd-HHmm stamp and builds a name like Pipeline backup 2025-10-11-0200. Because the stamp leads with the year, the backups always sort chronologically in Drive.
2. It opens the backup folder and calls makeCopy on the source file. That duplicates the entire spreadsheet — every tab, every value — into the folder.
3. restoreFromBackup takes the exact name of a backup. It searches the folder for that name and throws an error straight away if nothing matches, so a typo never silently does nothing.
4. It opens both the backup and the live Pipeline file. It copies every tab from the backup into the live file first. Doing the copy before any deletion means the file is never left empty if a step fails partway through.
5. It then removes the original pre-restore tabs — the ones whose names are not part of the restored set — leaving the live file holding exactly the backup’s sheets.

Example run

The backup folder, after a few weeks of scheduled runs, looks like this:

File name
Pipeline backup 2025-09-27-0200
Pipeline backup 2025-10-04-0200
Pipeline backup 2025-10-11-0200

A teammate sorts the Pipeline sheet on the wrong column and the rows are scrambled. You restore the most recent good copy by running:

restoreFromBackup('Pipeline backup 2025-10-11-0200');

Every tab in the live Pipeline file is replaced with the tabs from that dated copy. The execution log confirms Restored Pipeline from: Pipeline backup 2025-10-11-0200, and the sheet is back to its Saturday-morning state.

Trigger it

Two different schedules:

• Backups — weekly. In the Apps Script editor open Triggers, add a time-driven trigger for backupSheet, set it to a Week timer on Saturday in the 2am to 3am slot. A fresh dated copy lands every week.
• Restore — by hand. restoreFromBackup is never scheduled. Run it from the editor when something has gone wrong: open the function, but first edit the call (or add a small wrapper) so it passes the name of the backup you want.

Watch out for

• A restore is destructive. It replaces every tab in the live file, so any edits made since that backup are lost. Take a fresh backupSheet run immediately before restoring, in case you picked the wrong snapshot.
• restoreFromBackup needs the exact backup file name. Copy it from Drive rather than typing it — a single wrong character throws “Backup not found”.
• copyTo appends ” Copy” to the new tab names if a tab of the same name already exists. The restore handles the cleanup, but if you have tabs whose real names end in ” Copy”, the matching logic can misfire — rename them first.
• The backup folder grows forever. After a few months you will have dozens of copies eating Drive storage. Add a step that deletes copies older than, say, twelve weeks, or prune the folder by hand each quarter.
• This backs up the spreadsheet’s data and tabs. It does not preserve named ranges, protected ranges, or attached scripts — if Pipeline relies on those, recreate them after a restore.