Simon and I have been having some similar thoughts regarding hooking Google Docs up to a client side app. While he has been looking at an OpenOffice/Google Docs combo, I’ve been thinking about a Microsoft Excel/Google Docs combo.
I have always been a supporter of Excel Services. I think that Excel Services has lots of potential. That said, my biggest beef with Excel Services is that it isn’t widely available. No MOSS, no Excel Services. Recently I’ve been experimenting with Google Docs and it became very clear to me that some of Excel Services potential (in regards to scenarios that I’ve been envisioning) is already available by combining Excel with Google Docs. Furthermore, since Google Docs doesn’t require a substantial investment in server software, it is approachable by a much broader audience than Excel Services.
The scenario I’ve been thinking about is basically a sales forecasting application for a manufacturing company. The manufacturing company goes through an annual planning process that involves collecting sales forecasts from dozens of independent distributors. Then the forecasts need to be consolidated and analyzed. Like anything in IT, there are several different ways you can go about facilitating this process. One of the constraints in this case is that the manufacturer has no control over the software used by the distributors.
My goal was to see how easy this type of scenario could be addressed using Google Docs in conjunction with Excel. In a nutshell, I found that a basic solution could be implemented very easily that addresses the needs of this scenario without requiring much in terms of specialized help. Here is what I did:
- Setup a Google Docs account
- Download the Google Data API
- Create a Google Docs sales forecast template (one for each distributor)
- Create an Excel 2007 Add-in project using VSTO 2005 SE (once released, it’d probably make more sense to make this a doc-level solution using VSTO 3.0 with Visual Studio 2008).
- Use the Google Docs API to retrieve data into an Excel workbook used for consolidation.
First impression? Favorable. I’m going to keep digging. I’ll post more of the technical details in a follow-up post.
6 Responses to “Google Docs & Microsoft Excel working in harmony”
Leave a Reply
You must be logged in to post a comment.
October 3rd, 2007 at 8:53 pm
Excel Services isn’t widely available, but neither is Office 2007. Can step 4 be replaced by the following?
“4. Create an Excel 2003 Add-in project using VBA or VB6.”
That is, can the Google Data API be used in a non-dot-net programming environment? If so, then I’m going to look into the Google Data API.
October 3rd, 2007 at 9:17 pm
Hi Jon,
Yep - I hear you - though the approach above would work fine for Office 2003 - a VBA solution would open up a lot more doors. That’s the next path I’m going to go down. Anyway, to answer your question, I don’t see why not, it’ll just take some more work.
Off the top of my head I can think of a few options for a VBA version. First, you could develop a COM object that handles the details of working directly against the raw XML/HTTP exposed by the Google Data API as documented on the Protocol tab (http://code.google.com/apis/spreadsheets/developers_guide_protocol.html).
Another option would be to use Google’s .NET library (http://code.google.com/apis/spreadsheets/developers_guide_dotnet.html) and then create a COM callable wrapper for it.
I’ve also thought about some UDFs for retrieving info from Google Docs…
October 4th, 2007 at 2:47 pm
Steve
It doesn’t need to be VSTO does it?
You could just do a COM add-in in VS2005 (with shim?). For UDFs an automation project would do it - there is a base C# (VS2003) one on the codematic site somewhere.
I’m keen to look into this too, maybe next week I’ll get some time - email me and we can compare notes.
A VBA version looks like a lot of leg work I reckon, unless like you say you can wrap up a .net component.
cheers simon
October 4th, 2007 at 3:07 pm
Hey Simon - Correct - it wouldn’t have to be VSTO. I’ll get some basic code up soon to save you a few minutes when you start experimenting with it.
October 30th, 2007 at 5:33 am
Hi.
Nice demo.
>>I’ve also thought about some UDFs for retrieving info from Google Docs…
I wrote a VBA UDF for getting geo-codes back from Google maps API, it was not too hard, although i didn’t do any xml formating as i pulled back the results in CSV format.
December 12th, 2007 at 2:02 am
Steve, can you contact me? You’re mailaddress is bouncing
-= Maarten =-