Category Archives: Programming

How to backup Google Docs & Sheets as DOCX and XLSX automatically

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

Could not load file or assembly sapnco, Version=3.0.0.42

Recently I had the following error:

Could not load file or assembly ‘sapnco, Version=3.0.0.42, Culture=neutral, PublicKeyToken=50436dca5c7f7d23’ or one of its dependencies. The system cannot find the file specified.

The version of the SAP-Connector depends on .Net Framework 2.0 and needs “Microsoft Visual C++ 2005 Redistributable Package”. After installing this packaged, the error was solved. You have to install the correct version (x86 or x64).

The “VerifyClickOnceSigningSettings” task failed unexpectedly.

This error occurs when you are trying to develop office applications.

Error        The “VerifyClickOnceSigningSettings” task failed unexpectedly.
System.IO.FileNotFoundException: Could not load file or assembly ‘Microsoft.VisualStudio.Tools.Applications.Hosting, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a’ or one of its dependencies. Das System kann die angegebene Datei nicht finden.

This error means that he can’t find an assembly which is contained in VSTA-SDK. The SDK can be download from microsoft website.