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

Question

I am currently using a Google form so that people can submit information stored in a Google Docs spreadsheet. I was wondering if it is possible to automate backing up a spreadsheet at regular intervals. For example, every Friday the spreadsheet will be exported as CSV and sent to me by e-mail or saved in a password-protected directory on the server.

Thanks for reading, any ideas appreciated!

Answer 1

Google Docs is the perfect tool for editing and collaborating with your documents online. Although Google Docs does not provide an automatic backup feature, for users using Dropbox storage along with Google Docs, there is a solution for this.

CloudHQ solution ($ 10 + / pm), providing real-time synchronization between Google Docs and Dropbox . For example, while a user is editing a Google Docs web document, the document automatically changes to Dropbox. This means that cloudHQ automatically copies the file from Google Docs to Dropbox.

I would like to offer the following link to cloudHQ Quick Tour . CloudHQ also provides the Google Chrome extension. With the cloudHQ Chrome extension, you can sync or copy anything in your Dropbox or Basecamp account using Google Docs - directly from the Google Docs interface. The extension is available on the Google Chrome Web Store.

I would appreciate it if someone could give me a hint or opinion on data synchronization between cloud services.

Answer 2

Here is a solution for:

• automatically back up your Google spreadsheet (daily / weekly, etc.)
• as an Excel file (.xlsx) in the specified folder on your Google Drive
• which you can then configure to automatically sync with your computer

Step 1

Create a folder for the backup files in your Google Drive (for example, "My Drive> Docs> Backups). Open it in a browser and write down its “ folder identifier ” from the URL. For example, the folder identifier 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 that you want to automatically back up. 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 that:

• enable the Advanced Drive Service if you haven’t already done so: in the top menu, select "Resources"> "Advanced Google services ..."> "Drive API"> to enable "ON"
• UPDATE FOLDER ID , replacing xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx identifier of your backup folder, in the line with var backupFolder = ...

 // 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 eg "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]; 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 want to test the script, click the Run icon on the code. This will create an Excel backup file of your spreadsheet in the backup folder that you configured (based on the folder ID mentioned earlier).

Step 3

Save your script (in the top menu, choose File> Save), and then click the Triggers icon above the code (clock-shaped icon). In the new window that opens, click " + Add Trigger " (in the lower right corner). You should see an overlay called “Add Trigger for backupSheet”, where you can schedule your automatic backup. For example, if you want it to start every week on Monday, you need to configure the following parameters:

• Choose which function to run: backupSheet
• Choose which deployment should be performed: Head
• Select event source: Time-driven
• Select a trigger type based on time: Weekly timer
• Choose a day of the week: Every Monday

Save the trigger when you are done configuring it. Excel backups will now be automatically created in the desired folder on your Google Drive.

Step 4

Finally, install Backup and Sync from Google on your computer (if you have not already done so) and configure it to synchronize the backup folder from your Google Drive account - in "Settings"> "Google Drive"> enable "Sync my drive with this computer "and make sure that the backup folder is located among the synchronized folders.

Now the application will automatically download Excel backups from your Google spreadsheet for your offline pleasure!

Answer 3

Two solutions that do not include a payment subscription: 1) write a script or short application ( choose your language ) that exports the Google Sheet using the Google Drive API as a CSV. Why Drive API? The Sheets API is designed for spreadsheet- oriented functionality, that is, formatting data, resizing columns, creating charts, checking cells, etc., while the Drive API for file- oriented functionality, that is, import / export.

If you are using Python, here is a complete example that you can run as a cron job on your server or if the application is hosting on Google App Engine . If you do not, you can use it as a pseudo-code and choose any language supported by the Google API client libraries . Here is the main code snippet from this example (suppose the very last sheet is called "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 need to export it to pieces - see this page on how to do this. You can also send the contents of the file to yourself using the Gmail API . If you're new to the Google APIs at all, I have a (somewhat outdated, but) convenient intro video for you. (After that, there are 2 videos that may be useful.)

2) Another solution for those who are "allergic" to using the API, and that the alternative to Google Apps Script , Javascript is outside the browser. Like Node, it works on the server side, but on Google servers. Using Script applications, you can use DriveApp or advanced Drive services to access your sheet, then use MailApp or GmailApp to send it by email or use UrlFetch to send it to the server of your choice. To run it at a normal interval, you need to create a script as a time-driven installation trigger . In any case, you do not need to post + your application yourself.

ps. the latest version of the API for drives is v3 , but if you connect to Drive from Script applications, it uses v2 (not yet deprecated).

Answer 4

Google Docs is a free tool that allows users to automatically save and share any data in real time. However, it does not have a built-in function for securely backing up Google documents or real-time automatic backups.

Google has officially stated in its Terms of Service that:

Google and Googles suppliers and distributors will not be liable for loss of profits, revenues or data, financial losses or indirect, special, indirect, approximate or punitive damages.

Their disclaimer is applicable not only for personal use, but also for commercial use:

If you use our Services on behalf of a business, this business accepts these conditions. This will safeguard and protect Google and its branches ... including any liability or expenses arising out of the claim, damages, damages, lawsuits, court decisions, legal costs and attorney fees.

Today, there are other options besides paying for synchronization with Dropbox to automate backup of Google Docs. Your decision on how to automate backing up Google documents depends on several factors:

1. How confidential your data is and whether it is intended for personal or official use. e- How much should you be liable in case of data loss or security breach.

2. Do you have developer knowledge to encode a script or application that uses any of the methods previously described by wescpy in detail:

a) using Google Drive an API that automatically exports Google sheets b) use Python to run a cron job on your server to export a Google spreadsheet sheet in CSV format or c) write Javascript without an API that runs outside of your browser using a script google apps

If you need complete security and data protection, and you have chosen a free subscription solution for automatic backup of Google documents, it’s enough to say that you must be sure that your code is reliable and provides the security / encryption necessary for your project.