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!

Solution 1

Google Docs is the ideal tool to edit and collaborate on your documents online. Although Google Docs does not provide feature for automatically backup, people that use Dropbox storage at the same time as Google Docs have solution for that.

The solution is cloudHQ (10$+/pm) service that provides real time synchronization between Google Docs and Dropbox. For example, while user is editing some Google Docs web document, the document is automatically modified in Dropbox. That means, cloudHQ automatically copies file from Google Docs to Dropbox.

I would like to propose the following link to cloudHQ Quick Tour. CloudHQ also provides Google Chrome extension. With cloudHQ Chrome browser extension you can sync or replicate anything in your Dropbox or Basecamp account with Google Docs - directly from a Google Docs interface. Extension is available in Google Chrome web Store.

I should be grateful if anyone could provide me some tip or opinion about data synchronization between cloud services.

Solution 2

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!

Solution 3

Two solutions that don't involve a for-pay subscription: 1) write a script or short app (pick your language) that exports a Google Sheet using the Google Drive API as CSV. Why the Drive API? The Sheets API is for spreadsheet-oriented functionality, i.e., data formatting, column resize, creating charts, cell validation, etc., while the Drive API is for file-oriented functionality, i.e., import/export.

If you do Python, here's a complete example which you can run as a cron job on your server or if app-hosting on Google App Engine. If you don't, you can use it as pseudocode and pick any language supported by the Google APIs Client Libraries. Here's core piece of code from that example (assume the most current Sheet named 'inventory'):

FILENAME = 'inventory'
SRC_MIMETYPE = 'application/vnd.google-apps.spreadsheet'
DST_MIMETYPE = 'text/csv'

files = DRIVE.files().list(
    q='name="%s" and mimeType="%s"' % (FILENAME, SRC_MIMETYPE),
    orderBy='modifiedTime desc,name').execute().get('files', [])

if files:
    fn = '%s.csv' % os.path.splitext(files[0]['name'].replace(' ', '_'))[0]
    print('Exporting "%s" as "%s"... ' % (files[0]['name'], fn), end='')
    data = DRIVE.files().export(fileId=files[0]['id'], mimeType=DST_MIMETYPE).execute()
    if data:
        with open(fn, 'wb') as f:
            f.write(data)
        print('DONE')

If your Sheet is large, you may have to export it in chunks -- see this page on how to do that. You can also email the file contents to yourself with the Gmail API. If you're generally new to Google APIs, I have a (somewhat dated but) user-friendly intro video for you. (There are 2 videos after that maybe useful too.)

2) The other solution is for those who are "allergic" to using APIs, and that alternative is Google Apps Script, Javascript outside of the browser. Like Node, it runs server-side but on Google's servers. With Apps Script, you can use DriveApp or the advanced Drive service to access your Sheet, then use MailApp or GmailApp to email it to yourself, or use the UrlFetch service to send it to some server of your choosing. To run it at a normal interval, you'll need to create a script as a time-driven installable trigger. In either case, you don't need to host+execute your app yourself.

ps. The latest Drive API version is v3, but if you access Drive from Apps Script, it uses v2 (not deprecated yet).

Comments

[Ryan]

This sounds perfect! I am looking into this now and from the sounds of it, it will do the trick. Thanks so much!

[Ryan]

I just wanted to confirm that I was easily able to set this up. Thanks again, that solution works perfectly!

[M at]

It deserves it's own medium post and videos. Thanks for writing with this much details. I was able to replicate it and It was great.