Recurrently backup Google spreadsheets #backup #google #sheet #backupgooglesheet

recurrentlyBackupGoogleSpreadsheet.js

// Instructions
//
// 1. Open the spreadsheet that you want to backup in Google Drive.
// 2. Go to Extensions -> Apps Script.
// 3. Copy and paste the code below into the editor. Change "Untitled" script name 
//    to something meaningful
// 4. Save and run the script. You will have to authorize access.
// 5. Go to the spreadsheet's parent folder in Google Drive.
// 6. Make sure that the backup folder and the year folder have been created.
// 7. Go to the year folder and make sure that the backup file has been created.
// 8. You can schedule the script to run automatically by going to "Triggers".
// 9. Click "Add Trigger" and select the function "backupSpreadsheet" and the 
//    event source as "Time-driven", type as "Week timer", day of week as 
//    "Every Saturday", and time of day as "4pm to 5pm".
// 10. Select "Notify me immediately" in Failure notification settings.
// 11. Click "Save".

function backupSpreadsheet() {
  var spreadsheet = SpreadsheetApp.getActive();
  var spreadsheetName = spreadsheet.getName();
  var date = new Date();
  var year = date.getFullYear();
  var folderName = spreadsheetName + " - Backups";
  var yearFolder;

  // Get parent folder of the spreadsheet
  var parentFolder = DriveApp.getFileById(spreadsheet.getId()).getParents().next();

  // Create backup folder if it doesn't exist in the parent folder
  var backupFolders = parentFolder.getFoldersByName(folderName);
  if (backupFolders.hasNext()) {
    backupFolder = backupFolders.next();
  } else {
    backupFolder = parentFolder.createFolder(folderName);
  }

  // Create year folder if it doesn't exist in the backup folder
  var yearFolders = backupFolder.getFoldersByName(year.toString());
  if (yearFolders.hasNext()) {
    yearFolder = yearFolders.next();
  } else {
    yearFolder = backupFolder.createFolder(year.toString());
  }
  
  // Export as MS Excel file using UrlFetchApp
  var fileUrl = "https://docs.google.com/spreadsheets/export?id=" + spreadsheet.getId() + "&exportFormat=xlsx";
  var options = {
    headers: {
      Authorization: "Bearer " + ScriptApp.getOAuthToken()
    }
  };
  var response = UrlFetchApp.fetch(fileUrl, options);
  yearFolder.createFile(response.getBlob().setName(spreadsheetName + " - " + date.toISOString().slice(0, 10) + ".xlsx"));
}