Back up a spreadsheet to Drive every week

1 · Prepare

Put these pieces in place

• Open the script from the spreadsheet you want to back up, with Extensions, then Apps Script, so the script belongs to that file.
• Create a Drive folder named Spreadsheet backups. The script stops instead of guessing if it cannot find it.
• Keep both functions in the same Apps Script project.

2 · Review before running

What the script can access and change

3 · Copy

Paste this into the Apps Script editor

Create or open a script file, replace the example values marked above, then save the project.

const BACKUP_FOLDER_NAME = 'Spreadsheet backups'; // The Drive folder that receives the copies. It must already exist.
const BACKUP_DAY = ScriptApp.WeekDay.MONDAY; // The weekday the trigger runs. Other values look like ScriptApp.WeekDay.FRIDAY.
const BACKUP_HOUR = 6; // The hour the trigger runs, in the project time zone. 6 means some time between 6 AM and 7 AM.
const DATE_FORMAT = 'yyyy-MM-dd'; // How the date is written in the copy name, which is also what sorts the backups by date.

function createWeeklyBackupTrigger() {
  const alreadyInstalled = ScriptApp.getProjectTriggers().some((trigger) => {
    return trigger.getHandlerFunction() === 'backUpSpreadsheetToDrive';
  });
  if (alreadyInstalled) return;

  ScriptApp.newTrigger('backUpSpreadsheetToDrive')
    .timeBased()
    .onWeekDay(BACKUP_DAY)
    .atHour(BACKUP_HOUR)
    .create();
}

function backUpSpreadsheetToDrive() {
  const folders = DriveApp.getFoldersByName(BACKUP_FOLDER_NAME);
  if (!folders.hasNext()) throw new Error('Create a Drive folder named ' + BACKUP_FOLDER_NAME + ' before running this function.');
  const folder = folders.next();

  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const stamp = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), DATE_FORMAT);
  const copyName = spreadsheet.getName() + ' backup ' + stamp;

  DriveApp.getFileById(spreadsheet.getId()).makeCopy(copyName, folder);
  Logger.log('Saved a backup named ' + copyName);
}

4 · Run safely

Follow these steps in order

1. Make the folder

   In Drive, create one folder named Spreadsheet backups. Use that exact name, including the capital S and the space.

2. Run the backup by hand first

   Paste both functions and save. Choose backUpSpreadsheetToDrive and press Run. Approve the access when Google asks, then open the folder and check that a dated copy is there.

3. Install the weekly trigger

   Only after the test copy looks right, choose createWeeklyBackupTrigger and run it once. It does nothing when a trigger for backUpSpreadsheetToDrive already exists, so running it twice does not add a second trigger.

4. Check the trigger list

   Open Triggers in the left sidebar of the editor. You should see backUpSpreadsheetToDrive as a week timer. A time trigger runs as the account that created it, and a script attached to a spreadsheet can still reach that file with getActiveSpreadsheet, so there is nothing else to set up.

5 · Confirm

How to tell it worked

The Spreadsheet backups folder holds a copy whose name is the spreadsheet name, the word backup, and the date, such as Orders backup 2026-01-05. A new copy appears each Monday.

If the result is different

Troubleshooting

The script says Create a Drive folder named Spreadsheet backups before running this function.

Create a folder with that exact name, or change the name in the code and in the editable values. If two folders share the name, the script uses the first one Drive returns.

The error mentions getActiveSpreadsheet or a value that is null.

This script has to belong to a spreadsheet. Open the editor from the file with Extensions, then Apps Script, rather than starting a project at script.google.com.

The backup did not run at 6 AM.

A time trigger runs some time inside the hour you choose, and it follows the project time zone. Check the time zone under Project Settings, the gear icon, and open Executions to see when the run happened.