Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Google Docs/Sheets/Slides automated backups script

Google Apps Script that exports all your Google Docs/Sheets/Slides into docx/xlsx/pptx files and PDFs into a folder in your Google Drive. For more info and step-by-step setup instructions, see here: http://brokensandals.net/google-docs-backup

Replace INSERT_FOLDER_ID_HERE with the ID of the folder you want backups to be placed in.

Create a trigger to run the backupAll function if you want to do this on a schedule (e.g. nightly).

Notes:

  • By default, only files that you own (as opposed to files others have shared with you) will be backed up. Remove the file.getOwner() check from the backupAll method if you want to change that.
  • For each file, both an Office file (docx/xlsx/pptx) and a PDF are generated, and combined into a zip file that's placed in the backup folder. Zipping the backup files ensures that they don't clutter up the recent activity list for Docs/Sheets/Slides.
  • The script depends on the lastUpdated dates being correct on both the input files and the files in the backup directory. If that seems problematic, you could change the createOrUpdateFileForBlob method to delete existing backup files rather than updating them.

As always, this code may have defects that prevent it from working properly. Use at your own risk and remember to periodically verify that your backups are actually working as expected.

var BACKUP_FOLDER_ID = 'INSERT_FOLDER_ID_HERE';
var NATIVE_MIME_TYPES = {};
NATIVE_MIME_TYPES[MimeType.GOOGLE_DOCS] = MimeType.MICROSOFT_WORD;
NATIVE_MIME_TYPES[MimeType.GOOGLE_SHEETS] = MimeType.MICROSOFT_EXCEL;
NATIVE_MIME_TYPES[MimeType.GOOGLE_SLIDES] = MimeType.MICROSOFT_POWERPOINT;
var NATIVE_EXTENSIONS = {};
NATIVE_EXTENSIONS[MimeType.GOOGLE_DOCS] = '.docx';
NATIVE_EXTENSIONS[MimeType.GOOGLE_SHEETS] = '.xlsx';
NATIVE_EXTENSIONS[MimeType.GOOGLE_SLIDES] = '.pptx';
var BACKUP_MIME_TYPES = Object.keys(NATIVE_MIME_TYPES);
function backupAll() {
const backupFolder = DriveApp.getFolderById(BACKUP_FOLDER_ID);
BACKUP_MIME_TYPES.forEach(function(mimeType) {
var files = DriveApp.getFilesByType(mimeType);
while (files.hasNext()) {
var file = files.next();
if (file.getOwner().getEmail() == Session.getActiveUser().getEmail()) {
backup(file, backupFolder);
}
}
});
}
function backup(file, folder) {
var targetName = file.getName() + ' ' + file.getId();
var lastUpdated = file.getLastUpdated();
var pdf = getPdfBlob(file);
var native = getNativeBlob(file);
var zip = Utilities.zip([pdf, native], targetName + '.zip');
createOrUpdateFileForBlob(zip, folder, lastUpdated);
}
function createOrUpdateFileForBlob(blob, folder, ifOlderThan) {
var existingFiles = folder.getFilesByName(blob.getName());
if (existingFiles.hasNext()) {
var file = existingFiles.next();
if (file.getLastUpdated() < ifOlderThan) {
updateFile(file, blob);
}
} else {
folder.createFile(blob);
}
}
function updateFile(file, blob) {
const url = 'https://www.googleapis.com/upload/drive/v2/files/' + file.getId() + '?uploadType=media';
const params = {
method: 'put',
headers: { Authorization: 'Bearer ' + ScriptApp.getOAuthToken() },
payload: blob
};
var response = UrlFetchApp.fetch(url, params);
if (response.getResponseCode() < 200 || response.getResponseCode() > 299) {
throw 'Failed to update file named ' + file.getName();
}
}
function getPdfBlob(file) {
var blob = file.getAs('application/pdf');
return blob;
}
function getNativeBlob(file) {
const nativeMimeType = NATIVE_MIME_TYPES[file.getMimeType()];
const extension = NATIVE_EXTENSIONS[file.getMimeType()];
const url = 'https://www.googleapis.com/drive/v2/files/' + file.getId() + '/export?mimeType=' + nativeMimeType;
const params = {
method: 'get',
headers: { Authorization: 'Bearer ' + ScriptApp.getOAuthToken() }
};
const blob = UrlFetchApp.fetch(url, params).getBlob();
blob.setName(file.getName() + extension);
return blob;
}
@felix11h

This comment has been minimized.

Copy link

felix11h commented Nov 20, 2019

Exactly what I've been looking for and works precisely as described. I don't have any further comments, just wanted to say thanks for sharing this!

@brokensandals

This comment has been minimized.

Copy link
Owner Author

brokensandals commented Nov 20, 2019

@felix11h awesome, glad you found it useful!

I did run into one limitation of this approach: the API has a file size limit for exports (10MB if I remember right). So for example a document with lots of photos in it may fail to export. I haven’t put much time into looking for a workaround since I rarely keep large files in Google Docs.

@felix11h

This comment has been minimized.

Copy link

felix11h commented Nov 21, 2019

@brokensandals thanks for pointing that out. I'll keep it in mind as it might become important later on, but certainly not a problem for me right now either!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.