Answers / Question 13

Automatic daily backup of a Google Sheet, keeping the last 30 copies

A short Apps Script that saves a dated copy of your spreadsheet to a Drive folder every night and trashes copies older than the newest 30.

The problem

You want a safety net: a snapshot of an important spreadsheet taken automatically every day, so you can go back if something is overwritten, without the backup folder growing forever.

Inspired by a public question on Stack Overflow. The problem above is described in our own words and the solution below is ours.

The fix

Create a Drive folder for backups and copy its ID into folderId.
Paste the code into Extensions → Apps Script.
Run dailyBackup once to approve permissions and check a copy appears.
Run installDailyBackup once to schedule it nightly.

Code.gsApps Script

// Make a dated copy of this spreadsheet in a Drive folder every day, keeping the newest 30.
const DAILY_BACKUP = {
  folderId: 'PASTE_FOLDER_ID',
  keep: 30,
  hour: 2                     // roughly 2-3 AM in the script's time zone
};

function backupsToTrash(files, keep) {
  return files.slice()
    .sort(function (a, b) { return b.created - a.created; })
    .slice(keep)
    .map(function (f) { return f.id; });
}

function dailyBackup() {
  const ss = SpreadsheetApp.getActive();
  const folder = DriveApp.getFolderById(DAILY_BACKUP.folderId);
  const prefix = ss.getName() + ' backup ';
  const stamp = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd');
  DriveApp.getFileById(ss.getId()).makeCopy(prefix + stamp, folder);
  const existing = [];
  const it = folder.getFiles();
  while (it.hasNext()) {
    const f = it.next();
    if (f.getName().indexOf(prefix) === 0) existing.push({ id: f.getId(), created: f.getDateCreated().getTime() });
  }
  backupsToTrash(existing, DAILY_BACKUP.keep).forEach(function (id) { DriveApp.getFileById(id).setTrashed(true); });
}

// Run once to authorize and schedule.
function installDailyBackup() {
  ScriptApp.newTrigger('dailyBackup').timeBased().everyDays(1).atHour(DAILY_BACKUP.hour).create();
}

Just one tab, as values?

If you only need yesterday's numbers from one tab, copying values into another tab is lighter than copying the whole file. The scheduled backups recipe covers both styles.

Good to know

Only copies whose names start with this spreadsheet's name plus "backup" are ever trashed. Trashed files stay in Drive's trash for 30 days.
Copies count toward your Drive storage.
Version history (File → Version history) is also there, but a separate copy survives someone deleting the original.

The script's logic is unit-tested against a simulated spreadsheet before publishing. Always try new automations on a copy of your sheet first.