One Click Spreadsheet Archiving | Google App Script For The Win

I was using Google Sheets for planning, and noted that I was making copies of the various tabs to serve as an impromptu archiving mechanism. But why manually duplicate tabs when you can write code to properly snapshot the data? Below is a the Google App Script to do just this.

function archiveSheet() {
  var pad = function(x) {
    return x < 10 ? ("0"+x) : x;
  }
  
  var archiveDirId = "1GCaGcqsP1FzWe_ZAXJ4N6GHW1vbP0tBk";
  
  var now     = new Date();
  var sourceDoc = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = SpreadsheetApp.getActiveSheet();
  
  var workingSheet = sourceSheet.copyTo(sourceDoc);
  workingSheet.setName(sourceSheet.getName() + " To Archive");
  var data = workingSheet.getDataRange();
  data.copyTo(workingSheet.getDataRange(), {contentsOnly: true});
  
  var archiveName =  sourceDoc.getName() + " - " + sourceSheet.getName() + " - " + 
    now.getFullYear() + pad(now.getMonth()+1) + pad(now.getDate());
  
  var archiveDoc = SpreadsheetApp.create(archiveName);
  var archiveSheet = workingSheet.copyTo(archiveDoc);
  archiveSheet.setName(sourceSheet.getName());
  
  var emptySheet = archiveDoc.getSheetByName("Sheet1");
  archiveDoc.deleteSheet(emptySheet);
  
  sourceDoc.deleteSheet(workingSheet);
  
  
  var archiveFile = DriveApp.getFileById(archiveDoc.getId());
  var archiveFolder = DriveApp.getFolderById(archiveDirId);
  archiveFolder.addFile(archiveFile);
  DriveApp.getRootFolder().removeFile(archiveFile);
  
}

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu("Shortcuts")
      .addItem('Archive current sheet', 'archiveSheet')
      .addToUi();
}

The above code grabs the active sheet and makes a copy of it. It then copies the data back onto itself with contentsOnly set to true. This insures that there won't be broken formula references when the archive is created. The code then creates a new document, removes the unneeded Sheet1 from it, and copies the prepared data into it. The code also moves the archived file from Google Drive's root to a folder of your choice.

Here's a few screenshots and a link to a sample document.

If you make use of Installable Triggers, you can set this archiving function to happen automatically at a given day or time.

It's impressive how easy it was to implement this functionality. App Script really delivered.