Apps Script is a powerful tool for automating routine tasks with Google Sheets, Docs, Gmail, and other Google services. On a recent job, I needed to create a daily backup of a Google Sheet for a client, and I was looking for a simple, reusable solution.

This sounded like a perfect job for Apps Script! Here's what I came up with. Jump to the end to see the final script.

🧐 This post will cover

• Writing a basic Apps Script function
• Using SpreadsheetApp & DriveApp Classes
• Creating a time-driven Trigger to run the function

👉 Project Requirements

1. Backup multiple sheets on a schedule
2. Different backup destinations for each sheet
3. Read/write between different Google accounts

⚙️ Setup

I started out with a new sheet, and added columns for the source sheet Id, destination folder Id, and a link to the latest backup file.

And created a new script: Tools>Script Editor

Next, I loaded up the list of source sheet Ids into an array so I could loop through them for backing up:

📌 NOTE: getLastRow() - 1 is used to offset the the header row.

Now, what should I name the backup file?🤔

Well, I wanted the original sheet name included, and a timestamp to make each name unique.

Something like this: const backupName = sourceName +'_BAK' + dateTimeStr

So to get a string with the current timestamp, I used Utilities.formatDate() .

const dateTimeStr = Utilities.formatDate(new Date(),'GMT-4','yyyyMMdd_HHmmss');
const backupName = sourceName +'_BAK' + dateTimeStr

Now the backupName can be used in the next step, which creates the actual backup file.

// COPY EACH SOURCE SHEET TO DESTINATION FOLDER
  sourceIds.forEach((sourceId, index) => {
    const source = SpreadsheetApp.openById(sourceId);
    const sourceName = source.getName();
    const dateTimeStr = Utilities.formatDate(new Date(),'GMT-4','yyyyMMdd_HHmmss');
    const backupName = sourceName +'_BAK' + dateTimeStr;
    const backupId = source.copy(backupName).getId();  // File created in My Drive by default
    const destinationId = sh.getRange(index + 2, destinationIdCol).getValue();  // Folder Id for destination sheet
    const destination = DriveApp.getFolderById(destinationId);
    DriveApp.getFileById(backupId).moveTo(destination);

📌 NOTE: Here the offset is -2 because the index of the forEach() loop is zero-based. So to match the loop index with the correct row number (one-based), the offsets are combined.

For the last step, I wanted to save a link to the sheet for the latest backup file. Saving the link text was pretty easy, but that only shows the Id, and not the name. https://docs.google.com/spreadsheets/d/{SPREADSHEET_ID}/edit#gid=0

So to get the new filename as the link text, the cell value has to be a hyperlink. I tried the normal setValue() method but this doesn't work with hyperlinks. They have to be created using the newRichTextValue() method.

Lastly, to set the script on a timer

Triggers (Left sidebar in script editor) > Add Trigger

Now all sheets will be backed up daily to their specified folder. This even works across Google accounts! All you need is READ access to the source sheet, and WRITE access to the destination folder.

FINISHED SCRIPT

function backupSheets() {
  // CONFIGURE FOR GSHEET
  const spreadsheetId     = '{SPREADSHEET_ID}'  //  list of sheet/folder Ids
  const sheetName         = 'sheetlist' // sheet with the list of Ids
  const sourceIdCol       = 1;  // source spreadsheet Id
  const destinationIdCol  = 2;  // destination folder Id
  const newFileURLCol     = 3;  // link to new file [OUTPUT TO SHEET]

  // LOAD SOURCE SHEET IDS FOR BACKUP
  const ss = SpreadsheetApp.openById(spreadsheetId); 
  const sh = ss.getSheetByName(sheetName);
  const lastRow   = sh.getLastRow();
  const sourceIds = sh.getRange(2, sourceIdCol, lastRow - 1, 1).getValues(); // Array of Ids for source sheets

  // COPY EACH SOURCE SHEET TO DESTINATION FOLDER
  sourceIds.forEach((sourceId, index) => {
    const source        = SpreadsheetApp.openById(sourceId);
    const sourceName    = source.getName();
    const dateTimeStr   = Utilities.formatDate(new Date(),'GMT-4','yyyyMMdd_HHmmss');
    const backupName    = sourceName +'_BAK' + dateTimeStr;
    const backupId      = source.copy(backupName).getId();  // File created in My Drive by default
    const destinationId = sh.getRange(index + 2, destinationIdCol).getValue();  // Folder Id for destination sheet
    const destination   = DriveApp.getFolderById(destinationId);
    DriveApp.getFileById(backupId).moveTo(destination);

    // SAVE NEW FILE LINK TO SHEET
    const backupURL = 'https://docs.google.com/spreadsheets/d/' + backupId +'/edit#gid=0';
    const hyperlink = SpreadsheetApp.newRichTextValue().setText(backupName).setLinkUrl(backupURL).build();
    sh.getRange(index + 2,newFileURLCol).setRichTextValue(hyperlink);    // link to last backup file
  });
}