Creating a Scheduled Backup Service for Google Sheets using Apps Script.
Beginner Apps Script tutorial
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
- Backup multiple sheets on a schedule
- Different backup destinations for each sheet
- 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 theindex
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
});
}