There's a few different reasons for creating data snapshots of your Google Sheet. One of them is just to make sure you have a copy of the data in case someone deletes the file permanently. Another reason is so you can use the data snapshots as a way to measure changes in the data over time.
It's also very easy to set up - all you need are these 3 functions:
- createSnapshot()
- getDataBackupFolder()
- createLogFile()
The createLogFile() function is the one that you'll run on a schedule to generate your logs automatically.
The createSnapshot() function simply makes a copy of your sheet's data:
function createSnapshot() {
var ss = SpreadsheetApp.getActiveSpreadsheet()
var sheet = ss.getSheetByName('SHEET_NAME')
return {
sheetData: sheet.getDataRange().getValues(),
sheetName: sheet.getName()
}
}
The getDataBackupFolder() function creates a reference to the Google Drive folder you'll be storing the data snapshots in:
function getDataBackupFolder() {
var folder = DriveApp.getFolderById('Folder_ID');
return folder;
}
The createLogFile() function is the one that ties everything together. It references the other functions to get the data it needs and it also generates verbose filenames to make it easy for you to find the data snapshot file later on:
function createLogFile() {
let snapshot = createSnapshot()
let sheetName = snapshot.sheetName
let dataBackup = snapshot.sheetData
let filename = `'${sheetName}' Snapshot - ${Utilities.formatDate(new Date(), 'GMT', 'yyyy-MM-dd HH:mm')}`
let log_file = SpreadsheetApp.create(filename);
log_file.getSheetByName('Sheet1').setName(`${sheetName} Snapshot`).getRange(1, 1, dataBackup.length, dataBackup[0].length).setValues(dataBackup);
DriveApp.getFileById(log_file.getId()).moveTo(getDataBackupFolder())
}
Once you have this set up, you can set it to run on a schedule by creating a trigger for it through your Apps Script Editor UI. See our Event Triggers Tutorial Video for step-by-step instructions on how to use event triggers.
