AutomationResearched. Verified against official documentation (3 sources), not personally tested.

Back up a Google Sheet every week without thinking about it

Set up a free Google Apps Script that copies your sheet to a Drive folder every week, so you always have an older version to restore.

2 min read

It takes seconds to overwrite or delete the wrong tab in a Google Sheet, and undo history does not last forever. If you only remember to back up when something goes wrong, it is already too late.

You can set up Google Apps Script — a free tool that runs small programs inside Google apps — to copy your sheet to a Drive folder every week. You do not need to remember anything after the first ten minutes.

Open the script editor

Open your Google Sheet. Open the Extensions menu and choose Apps Script. A new tab opens with a blank code file attached to that spreadsheet.

Reference: Extend Google Sheets with Apps Script

Paste the backup script

Delete any code already in the file and paste this:

function weeklySheetBackup() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var today = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "dd-MM-yyyy");
  var backupName = sheet.getName() + " backup " + today;

  var folders = DriveApp.getFoldersByName("Sheet backups");
  var folder;
  if (folders.hasNext()) {
    folder = folders.next();
  } else {
    folder = DriveApp.createFolder("Sheet backups");
  }

  folder.addFile(sheet.copy(backupName));
}

The script looks for a folder called Sheet backups in your Google Drive. If it does not exist, the script makes one. It then copies your spreadsheet, adds today’s date to the name, and puts the copy in that folder.

Reference: Class SpreadsheetApp

Save the project

Click the disk icon or press Ctrl+S (Cmd+S on Mac). Give the project a name, such as Weekly sheet backup, and click Save.

Add a weekly timer

A trigger is a rule that tells Google to run your script on a schedule. Click the clock icon on the left to open Triggers. Click Add Trigger. Choose:

  • Choose which function to run: weeklySheetBackup
  • Select event source: Time-driven
  • Select type of time based timer: Week timer
  • Select day of week: pick any day
  • Select time of day: pick a time

Click Save.

Reference: Installable triggers

Allow the script to run

The first time the trigger fires, Google will ask you to authorise the script. Choose your Google account, click Advanced, then Go to Weekly sheet backup (unsafe), and click Allow. Google labels it “unsafe” only because the script is new and not verified by Google; it is your own code running in your own account.

Expected result

Every week, a new file appears in the Sheet backups folder in Google Drive. Each file is a complete copy of your spreadsheet as it looked at that time. To restore, open any backup, click File, then Make a copy.

Sources

  1. https://developers.google.com/apps-script/guides/sheets
  2. https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app
  3. https://developers.google.com/apps-script/guides/triggers/installable

Stay Ahead. Work Smarter.

Get practical AI tips, automation ideas and useful technology tricks — one useful thing at a time. Follow the feed in any reader.