November 20, 2017

Git workflow for Excel

Posted by Björn Stiel Comments

This guide presents a simple recipe for how to use Git for Excel workbooks to support small Excel teams in their existing workflow and simplify collaboration.

You probably know how to use Git and are familiar with git add, git commit and git push. This is all there is to it to create an audit log for your Excel workbook in Git:

  1. Save your workbook changes in Book1.xlsx
  2. git add Book1.xlsx
  3. git commit -m "some changes"
  4. git push origin master

Collaborating and merge conflicts

However, as soon as you start collaborating (or push from multiple locations), you are likely to run into this kind of conflict:

 ! [rejected]        master -> master (fetch first)
error: failed to push some refs to 'https://cloud.xltrail:3000/test/workflow.git'
hint: Updates were rejected because the remote contains work that you do
hint: not have locally. This is usually caused by another repository pushing
hint: to the same ref. You may want to first integrate the remote changes
hint: (e.g., 'git pull ...') before pushing again.
hint: See the 'Note about fast-forwards' in 'git push --help' for details.

Someone pushed their changes before we did. Git now refuses to accept our push, because our local repository is out of sync with remote. If we were dealing with text files, we could simply pull the changes (git pull origin), merge them locally and push back the merged repository. As we are dealing with an Excel (binary) file, we have to choose our local (git pull -X ours) or the remote version (git pull -X theirs) to work around the merge conflict.

A basic single-branch workflow

The goal of our simple workflow is to create a linear workbook commit history. Think more audit log and less collaborative pull request workflow:

  • we only work on a single branch (master)
  • we always want to push our new head version (even if there have been pushs to remote in the meantime)

Therefore, before pushing, we have to fetch the latest changes and merge so that our changes stay on top:

git pull -X ours origin

This pulls the change set and resolves merge conflicts by choosing our version over the remote. In other words, our version (ours) becomes the new head and the remote version (theirs) its predecessor. This command resolves the merge conflict by creating a separate merge commit so that you have full transparency over what happened here for future reference.

We are good to push without conflicts now:

git push origin master

Automate the workflow using a pre-push hook

We can simplify our two-step workflow further by instructing Git to automatically perform a git pull -X ours before every push. We need to go to the .git/hooks directory in our repository and add a file named pre-push:

#!/bin/bash

echo "Pre-push hook: Pulling changes remote..."
git pull -X ours

This brings us back to the simple git push origin master and git takes care of pulling and resolving merge conflicts before performing the push.

Note: On Mac you need to run chmod +x .git/hooks/pre-push, otherwise it will not trigger the hook.

How to apply this to your workflow

This workflow is a basic recipe for simple collaboration and audit logging of your workbook changes. Give it a try and let us know how it works out for you. If you get stuck, have questions or suggestions, or would like to share your workflow, we would love to hear from you!

Do you want more free Git Excel tips?
About Us
Free Products
Contact Details

Zoomer Analytics GmbH
Eichbühlstrasse 19
8004 Zurich
Switzerland

info@xltrail.com

© 2018 Zoomer Analytics LLC. All rights reserved.