Google Docs/Sheets/Slides automated backups script

README.md

Update: I now use a slightly different version of this script, which creates a single zip file instead of one per document, and puts a timestamp in the filename rather than overwriting the previous backup file. That version can be found at https://github.com/brokensandals/export-google-docs.

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.

GoogleDocsBackup.gs

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() && 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;
}

Yeah I've got a python script thatll download all the zips. Now im trying to automate this as much as possible, so is there a way within this Apps Script to create that backup folder at the beginning of the script, and then pull that File ID for the BACKUP_FOLDER_ID? I know there would have to be some checks in place to keep it from breaking should the folder already exist, but Im running into an issue of not being able to pull the file id properly.

is there a way within this Apps Script to create that backup folder at the beginning of the script, and then pull that File ID for the BACKUP_FOLDER_ID?

One approach could be:

• instead of putting the zips directly inside the backup folder, have the Apps Script create a new subfolder inside that folder each time it runs, and put the current date in the folder name
• have the python script list all the subfolders in the backup folder and download from the newest one

See the createFolder method in the Folder documentation.

You could also look at my newer version of the script which puts the entire backup into a single zip file with a date in the name - then your python script would just need to download the newest file.

I'm a complete newbie and am struggling to implement this code.
I've installed it in Google Drive but not sure it's in the correct spot.
I get an error message when I try to run it ...
7:53:26 PM Notice Execution started
7:53:26 PM Error
Exception: No item with the given ID could be found. Possibly because you have not edited this item or you do not have permission to access it.
backupAll @ Code.gs:16

Tried researching the error message but only went further down the rabbit hole.
Any easy fixes?
Thanks
Douglas

@dmorgans0803 Based on the error, I'm guessing maybe you didn't replace the text INSERT_FOLDER_ID_HERE in the script. You need to replace it with the ID of some folder that you've created (this is where the backup files will go), as mentioned in the "Instructions" section here: https://brokensandals.net/technical/backup-tooling/google-docs/

To get the ID, you open the folder in Google Drive in your web browser, then look at the address bar - you should see something similar to "https://drive.google.com/drive/folders/asdflkjjlk23409cdsklmklmnnl290". Notice the long sequence of random-looking letters and numbers after "folders/" - that's what you need to copy. Don't include the slash or anything before it; and if you see a question mark, don't include the question mark or anything after it.

Hope this helps - if the issue is something else, I'm afraid I don't have any immediate ideas.

Issue is something else. Had already inserted my own ID. Script demanding authority to run. ID folder is from Google workplace folder, but script demanding authority from my personal Google email. I've hired an experienced Fiverr to fix my code. Will share when we have a fix. Thanks Douglas

…

On Fri, Jan 7, 2022, 8:23 AM Jacob Williams ***@***.***> wrote: ***@***.**** commented on this gist. ------------------------------ @dmorgans0803 <https://github.com/dmorgans0803> Based on the error, I'm guessing maybe you didn't replace the text INSERT_FOLDER_ID_HERE in the script. You need to replace it with the ID of some folder that you've created (this is where the backup files will go), as mentioned in the "Instructions" section here: https://brokensandals.net/technical/backup-tooling/google-docs/ To get the ID, you open the folder in Google Drive in your web browser, then look at the address bar - you should see something similar to " https://drive.google.com/drive/folders/asdflkjjlk23409cdsklmklmnnl290". Notice the long sequence of random-looking letters and numbers after "folders/" - that's what you need to copy. *Don't* include the slash or anything before it; and if you see a question mark, don't include the question mark or anything after it. Hope this helps - if the issue is something else, I'm afraid I don't have any immediate ideas. — Reply to this email directly, view it on GitHub <https://gist.github.com/6b77f73666323d6e4b94ff1df12a532a#gistcomment-4019509>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AXFTZEYLSNRYECEVE3Z732TUUZ2GHANCNFSM4JPT2IPQ> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>. You are receiving this because you were mentioned.Message ID: ***@***.***>

Hello! Thanks for your fantastic utility. It works nicely!

May I have just one request? Would it be please possible to export my Documents as plain text - as a .txt file?

Thank you!

@Martianno Glad you found it useful!

Would it be please possible to export my Documents as plain text - as a .txt file?

I won't have time to test or troubleshoot this, but, one thing you could try would be to replace MimeType.MICROSOFT_WORD on line 4 with MimeType.PLAIN_TEXT, and replace .docx on line 9 with .txt

Thanks for a super fast reaction! It Works, God bless you!

Hi.. Thank you for the script. If i am not mistaken, the script is to backup all kinds of file and zip it. I am new in this environment and im trying to do the basics for each type of file. Can you help me with the script that can backup google docs file? I was able to make an auto backup for the spreadsheet however, im trying to replicate the code and replaced the var name but i dont know the specific function for google docs. I hope you could help me.. Thank you.. :)

function archiveCopy() {
var file= DriveApp.getFileById("1liz6Kfh4eiXJOR95Ry03m-lVO_6fv6-sylZ3N3RAWCg");
var destination= DriveApp.getFolderById("1y-aYx3SbnlZSde_J57FEj_Jyfo4bj6ED");

var timeZone=Session.getScriptTimeZone();
var formattedDate= Utilities.formatDate(new Date(),timeZone,"yyyy-MM-dd' 'HH:mm:ss");
var name= SpreadsheetApp.getActiveSpreadsheet().getName()+"Copy"+formattedDate;

file.makeCopy(name,destination);

}