How can I automatically download backups of a Google Docs Spreadsheet?

Question

I am currently using a Google Form to allow people to submit information that is stored in a Google Docs Spreadsheet. I was wondering if it is possible to automate a backup of the spreadsheet at regular intervals. For example, every Friday the spreadsheet will be exported as a CSV and either emailed to me or stored on in a password protected directory on the server.

Thanks for reading, any ideas are appreciated!

Answer 1

Here is a solution to:

• automatically create backups of a Google Spreadsheet (daily / weekly etc.)
• as an Excel file (XLSX) in a given folder from your Google Drive
• which you can then configure to automatically sync to your computer

Step 1

Create a folder for backup files in your Google Drive (e.g. "My Drive > Docs > Backups"). Open it in your browser, and make a note of its "folder ID" from the URL. For example, the folder ID from the following URL would be 1234abcdefgh_98765ijklmnopqrs_0XY

https://drive.google.com/drive/u/0/folders/1234abcdefgh_98765ijklmnopqrs_0XY?ths=true

Step 2

Open the Google Spreadsheet you wish to automatically backup. From the top menu, choose "Tools" > "Script editor". In the new window that opens, replace the default code with the code below, and make sure to:

• enable the Advanced Drive Service if you haven't already: from the top menu, choose "Resources" > "Advanced Google services..." > "Drive API" > toggle "ON"

• UPDATE THE FOLDER ID, by replacing xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx with your backup folder ID, in the line with var backupFolder = ...

• EDIT @ 2020-04-22: Added an option to export a single sheet, by updating the value of sheetName in the code below.

// function to backup the current Spreadsheet as an Excel file (XLSX) in a given folder
// -- requires "Advanced Drive Service" which must be enabled in "Resources" > "Advanced Google services..." > "Drive API" > toggle "ON"
function backupSheet() {
  // UPDATE THE FOLDER ID for e.g. "My Drive > Docs > Backups"
  var backupFolder = DriveApp.getFolderById("xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx");
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var spreadsheetId = spreadsheet.getId();
  var file = Drive.Files.get(spreadsheetId);
  var url = file.exportLinks[MimeType.MICROSOFT_EXCEL];

  // UPDATE THE SHEET-NAME TO BE EXPORTED - e.g. sheetName = "Malawi Ops"
  // -- LEAVE IT BLANK TO EXPORT THE ENTIRE SPREADSHEET - i.e. sheetName = ""
  var sheetName = "";
  if (sheetName.length) {
    var sheet = spreadsheet.getSheetByName(sheetName);
    var sheetId = sheet.getSheetId();
    url += "&gid=" + sheetId;
  }

  var token = ScriptApp.getOAuthToken();
  var options = { headers: { Authorization: "Bearer " + token } };
  var response = UrlFetchApp.fetch(url, options);
  var doc = response.getBlob();
  var backupDate = Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd' 'HH-mm-ss");
  var backupName = spreadsheet.getName() + ".backup " + backupDate + ".xlsx";
  var tempFile = DriveApp.createFile(doc).setName(backupName);
  tempFile.makeCopy(backupName, backupFolder);
  tempFile.setTrashed(true);
}

If you wish to test the script, click on the "Run" icon above the code. This will create a backup Excel file of your spreadsheet, in the backup folder you configured (based on the folder ID mentioned earlier).

Step 3

Save your script (from the top menu, choose "File" > "Save"), and then click on the "Triggers" icon above the code (the clock-shaped icon). In the new window that opens, click on "+ Add Trigger" (in the bottom-right corner). You should see an overlay titled "Add Trigger for backupSheet", where you can plan the execution of your automatic backup. For example, if you want it to run every week on Monday, you should configure these settings:

• Choose which function to run: backupSheet
• Choose which deployment should run: Head
• Select event source: Time-driven
• Select type of time based trigger: Week timer
• Select day of week: Every Monday

Save the trigger when you're done configuring it. The Excel backups will now automatically be created in the desired folder from your Google Drive.

Step 4

Finally, install Backup and Sync from Google on your computer (if you haven't already), and configure it to sync the backup folder from your Google Drive account - in "Preferences" > "Google Drive" > enable "Sync My Drive to this computer", and make sure the backup folder is among the folders being synced.

The app will now automatically download the Excel backups of your Google Spreadsheet, for your offline pleasure!