A Better Way to Share Spreadsheets With a Living Data Set

I love Microsoft Excel. Unfortunately Excel on the Mac is not as easy to work with as Excel on the PC. One major difference is the lack of ODBC connections without third party software. This has caused me to look to web-based spreadsheets like Google Docs, but unfortunately those leave me wanting as well.

Here is the business problem:

I need to create a spreadsheet that allows multiple people in various locations to see and play with the same data set (in this case the sales information for our retail stores).

Using Excel the data become stale. We have to either manually update the information on a regular basis or we have to regularly run exports and import CSV files. Both scenarios create a situation where the spreadsheet is out of date when we need to answer a question or curiosity and it takes too much work to update it.

We started using Google Docs as an alternative. I wrote some custom scripts that updated the Google Doc each night. It is a delicate process though and it is easy for the data to get out of whack. Also, there are some serious limitations to the number of calculations you can have in a Google Spreadsheet. This meant that we had to limit the amount of information (i.e. detail and accuracy) that we stored in the spreadsheets. Also, Google Spreadsheets are slow (especially if you are pushing the limits). After a while the spreadsheets became unreliable.

Enter Google App engine.

Since I have become a big fan of Google App engine I decided that it would be better to use a BigTable data store in AppEngine. This eliminated the restrictions on the level of detail and accuracy that we could store within our data set. It also reduced the propensity for errors in the updating process. The new problem was then how to put that data into a spreadsheet that could be shared and manipulated by the stake holders.

Google Spreadsheets has a function that allows you to pull in a web-based CSV into the spreadsheet: ImportCSV(url). I was able to create a set of CSVs on the AppEngine account that pulled out the desired information and placed it in the spreadsheet. Unfortunately, the Google Doc was still slow and did a poor job of providing feedback that the information was being updated. If only there was a way to pull web-based data into an Excel Spreadsheet. (This would have been easy to do with ODBC, but we are all on Macs.)

Turns out, there is a way. It is not exactly documented and publicized. There isn’t really a provided user interface, but it can be done.

The end result is an Excel spreadsheet that updates the data every time it is opened. I am still storing the data in Google AppEngine (as a free web-based data store), but the actual source of information could come from any system. Also, once everything is setup the spreadsheet can be emailed to new people and the data will still update without them having to do any setup on their side. I am even able to create calculated columns from the remote data and have them update automatically. So far, this seems like the best solution for our problem.

In my next post I will detail how you can create a web-enabled spreadsheet too.