I have many Google Sheets and Docs in my Drive Folder and I use them frequently. They are stored in Google Cloud and on my PC. But I am totally dependent from Google with that both two file-formats. If you have a look in your Drive Folder the files are not really there. You can watch and edit them offline, but I wanted a full backup of these files in a different format.

So I decided to wirte a script which will make a copy of these files to xlsx and docx. In the internet I only found solutions for a single file or there were some scripts in Python. I wanted that script to run automatically and efficient. Efficient means, that I only want to make a new backup of a file if it was changed since the last backup run.

With Google Apps Script it was possible to write a script which can perform my backup-task, handles the authorization and to define triggers. I set up a trigger everey night.

My script actually works for docs and spreadsheets. All files in my Drive are selected. If the file changed since the last trigger, it is backuped in a directory “_BACKUP”. If you use my script you have to create this folder and insert the folder-id in the consts-variable. Also you need to insert the directory-id of your root-directory / my-drive. You can determine it by debugging the script.

The script creates a backup-info-file, where it logs how many files where updated. Also the timestamp of the last change in the file is used to determine if a file changed between the triggers.

Hope someone can use this scripts. Feel free to ask if there are any problems.

const BACKUPFOLDERID = 'xxxxxxxxxxx';
const BASEFOLDERID = 'xxxxxxxxxxx';
const BACKUPINFOFILENAME = '_BACKUPINFO';

function runBackup() {

  var backupInfoDocument;
  var backupInfoFile;
  var backupFolder = DriveApp.getFolderById(BACKUPFOLDERID);
  var backupCount = 0;
  var isInitialBackup = false;

  var bi = backupFolder.getFilesByName(BACKUPINFOFILENAME);
  if (!bi.hasNext()) {
    backupInfoDocument = DocumentApp.create(BACKUPINFOFILENAME);
    backupInfoFile = DriveApp.getFileById(backupInfoDocument.getId());
    backupFolder.addFile(backupInfoFile);
    isInitialBackup = true;
  } else {
    backupInfoDocument = DocumentApp.openById(bi.next().getId());
    backupInfoFile = DriveApp.getFileById(backupInfoDocument.getId())
  }

  backupCount += backupFiles("docx", MimeType.GOOGLE_DOCS, isInitialBackup, backupInfoFile.getId());
  backupCount += backupFiles("xlsx", MimeType.GOOGLE_SHEETS, isInitialBackup, backupInfoFile.getId());

  var body = backupInfoDocument.getBody();
  body.insertParagraph(0, Utilities.formatDate(new Date(), "GMT+1", "yyyy-MM-dd HH:mm:ss") + ' - Backup erfolgreich (' + backupCount + ' Datei(en))')
  backupInfoDocument.saveAndClose();

}

function backupFiles(targetExtension, mimeType, isInitialBackup, backupInfoFileId) {

  var files = DriveApp.getFilesByType(mimeType)
  var backupCount = 0;
  var backupInfoFile = DriveApp.getFileById(backupInfoFileId);

  while (files.hasNext()) {
    var file = files.next();

    if (file.getId() == backupInfoFile.getId()) { continue; }
    if (file.getName() == BACKUPINFOFILENAME) { continue; }
    
    var p = file.getParents();
    if (p.hasNext()) {
      if (p.next().getId() == BACKUPFOLDERID) { continue; }
    } 

    // If File is older than last backup-run, skip
    if (!isInitialBackup && file.getLastUpdated() < backupInfoFile.getLastUpdated()) { continue; }

    removeOlderVersion(file, targetExtension);
    exportFile(file, targetExtension);
    backupCount++;
  }

  return backupCount;

}


function exportFile(file, targetExtension) {

  var token = ScriptApp.getOAuthToken();
  var url;

  if (targetExtension == 'docx') {
    url = 'https://docs.google.com/feeds/download/documents/export/Export?id=' + file.getId() + '&exportFormat=docx';
  } else {
    url = 'https://docs.google.com/feeds/download/spreadsheets/Export?key=' + file.getId() + '&exportFormat=xlsx'
  }

  var myBlob = UrlFetchApp.fetch(url,
    {
      headers: {
        Authorization: 'Bearer ' + token
      }
    }).getBlob();

  var newFile = DriveApp.createFile(myBlob).setName('BACKUP_' + file.getName() + '.' + targetExtension);

  var backupFolder = DriveApp.getFolderById(getBackupFolderId(file));
  backupFolder.addFile(newFile);

}

function getParentFolder(arr, id, isFile) {

  var parents;

  if (isFile) {
    var file = DriveApp.getFileById(id);
    parents = file.getParents();
  } else {
    var folder = DriveApp.getFolderById(id);
    parents = folder.getParents();
  }

  if (!parents.hasNext()) {
    return arr;
  }

  var p = parents.next();

  if (p.getId() == BASEFOLDERID) {
    return arr;
  }

  arr.push(p.getName());
  return getParentFolder(arr, p.getId(), false);

}

function getBackupFolderId(file) {

  var arr = [];
  var currentFolder = DriveApp.getFolderById(BACKUPFOLDERID);

  arr.concat(getParentFolder(arr, file.getId(), true));
  arr.reverse();

  for (i = 0; i < arr.length; i++) {

    var f = currentFolder.getFoldersByName(arr[i])

    if (f.hasNext()) {
      currentFolder = f.next();
    } else {
      currentFolder = currentFolder.createFolder(arr[i]);
    }

  }

  return currentFolder.getId();

}

function removeOlderVersion(sourceFile, targetExtension) {

  var files = DriveApp.getFolderById(getBackupFolderId(sourceFile)).getFilesByName('BACKUP_' + sourceFile.getName() + '.' + targetExtension)

  while (files.hasNext()) {
    var file = files.next();
    file.setTrashed(true);
  }
}