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. If the folder is missing, the script stops with an error.
• 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

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

   Choose Extensions, then Apps Script. Paste both functions over the starter code and save. Choose backUpSpreadsheetToDrive in the function dropdown above the code, then click Run. Approve access when Google asks, then open the folder in Drive and check that a dated copy is there.

3. Install the weekly trigger

   When the test copy looks right, choose createWeeklyBackupTrigger and click Run 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

   Click Triggers, the clock icon in the left sidebar. backUpSpreadsheetToDrive is listed as a week timer. The 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 run stops with "Create a Drive folder named Spreadsheet backups before running this function."

Create a folder with that exact name, or change BACKUP_FOLDER_NAME in the code to the name of your folder. If two folders share the name, the script uses the first one Drive returns.

The run stops with "TypeError: Cannot read properties of null" and mentions getActiveSpreadsheet.

Open the editor from the spreadsheet with Extensions, then Apps Script, and paste the code there. A project started at script.google.com has no active spreadsheet, so getActiveSpreadsheet returns null.

The backup did not run at 6 AM.

Open Executions in the left sidebar to see when the run happened, and check the time zone under Project Settings, the gear icon. A time trigger runs some time inside the hour you choose, in the project time zone.