Category Archives: IT

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

Logoff Shortcut

It can be really annoying to repeatly clicking on the windows-icon, then on user-icon and then on “Logoff”. Especially when you are working on several servers and virtual machines this step is done several times a day and uncountable times a year.

If you want to save time and clicks you can simply add a shortcut on your desktop:

Right-click and select “New” and then “Shortcut”.

Logoff Shortcut

Type “logoff.exe” in your shortcut und press Enter. After that you can give the shortcut a name and a nice icon.

After creating the shortcut it will look like that:

Logoff Shortcut

For me it is really a timesaving thing and it saves a lot of clicks. Especially when you are stressed 😉

How To Remove Microsoft Account From Windows 10

Recently I faced the problem on how to remove a “onmicrosoft”-Account from my company’s windows 10 pc. It was not possible to remove it in this screen:

How To Remove Microsoft Account From Windows 10

There was only the option to “Manage” this account. But somewhere in the depths of the internet I found the solution where to remove this account: In the same screen you find the menu “access work and school”. There you can find all accounts and you are able to delete or disconnect them. But be careful to choose the right account!

How To Remove Microsoft Account From Windows 10

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.