Second life for old school scripts
Some time ago we were faced with a need to use Microsoft Excel documents and Google Spreadsheets together. It was a real pain and we decided to make service layer leveraging the API differences and code refactorings.
The story was simple: we had a tool that extracted some data from several excel documents and generated output excel document based on extracted data. The challenge began when some of those source excel documents were migrated to Google Drive.
Since the tool was legacy that used COM to operate with Excel (though we had the source code of that tool), we have implemented several simple vbs scripts over it to tweak results, as we didn’t want to change things much. The ideal solution was to make it work without touching the old code at all. Otherwise, it would be much easier to re-implement all legacy code.
Thus, we decided to create a COM wrapper library for Google Client library in order to re-use the old code with minimal changes to access Google Spreadsheets.
The attentive reader will ask how to provide Google account credentials to make it all work as expected? Well, the good news is that Microsoft added MailLogon method to the Excel. Application interface. So, we use this method for authentication in Google, and, for convenience, we get default credentials from config file.
Our internal project grew up into a GSpreadCOM library that allows accessing (read/write) Google Spreadsheets as normal Microsoft Excel documents.
Example 1 (VBScript)
This example demonstrates the basics:
Set objExcel = CreateObject("GSpreadCOM.Application") objExcel.Visible = True objExcel.Workbooks.Add objExcel.Cells(1, 1).Value = "Test value" objExcel.ActiveWorkbook.Save "test.xls" objExcel.ActiveWorkbook.Close
To make this script work with Google Spreadsheet, you need to change Excel.Application to GSpreadCOM.Application. The name test.xls will be title of the new spreadsheet document.
All the rest code remains the same.
Example 2 (VBScript)
This is a bit more complicated example: cells access
Set objExcel = CreateObject("GSoreadCOM.Application") Set objWorkbook = objExcel.Workbooks.Open("test.xls") intRow = 1 Do Until objExcel.Cells(intRow,1).Value = "" WScript.Echo objExcel.Cells(intRow, 1).Value intRow = intRow + 1 Loop
Again, by using GSpreadCOM.Application instead of Excel.Application and spreadsheet title instead of test.xls, you open the existing spreadsheet and read content from it.
Example 3 (C#)
A nice side-effect of our library is that you can easily use it even in a new code. For example, you can use .NET interop to work with Excel.Application interface. The only change you need is:
Type excelAppType = Type.GetTypeFromProgID("GSpreadCOM.Application"); var app = Activator.CreateInstance(excelAppType) as Microsoft.Office.Interop.Excel.Application;
All the rest code remains the same.
Let’s create a simple table with formulas and styles:
Set app = CreateObject("GSpreadCOM.Application") app.Workbooks.Item("Example2").Worksheets.Item("Sheet1").Activate() Set cells = app.Cells ' --- Data cells(2, 2).Cells.Value = "Product" For i = 1 To 4 cells(2, 2 + i).Cells.Value = "Quarter " & i Next cells(2, 7).Cells.Value = "Year" cells(4, 2).Cells.Value = "Apples" cells(5, 2).Cells.Value = "Pears" cells(6, 2).Cells.Value = "Bananas" cells(7, 2).Cells.Value = "Oranges" cells(8, 2).Cells.Value = "Totals" randomize For row = 4 To 7 For col = 3 To 6 cells(row, col).Cells.Value = (int(rnd * 100) + 1) * 100 Next Next cells.Range("C8:G8").FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)" cells.Range("G4:G7").Formula = "=SUM(C4:F4)" ' --- Styling Set tableRange = cells.Range("B2:G8") tableRange.Borders.Color = "white" tableRange.NumberFormat = "$0,000" ' currency number format cells.Range("B2:B3").Merge() cells.Range("C2:C3").Merge() cells.Range("D2:D3").Merge() cells.Range("E2:E3").Merge() cells.Range("F2:F3").Merge() cells.Range("G2:G3").Merge() Set headerRange = cells.Range("B2:G3") Set footerRange = cells.Range("B8:G8") ' color #0066CC has index 23 in excel color palette headerRange.Interior.ColorIndex = 23 footerRange.Interior.Color = "#0066CC" ' white color has index 2 in excel color palette headerRange.Font.ColorIndex = 2 footerRange.Font.Color = "white" headerRange.Font.Bold = True footerRange.Font.Bold = True footerRange.RowHeight = 30 oddRowColor = 14994616 ' RGB(184, 204, 228) evenRowColor = 15853019 ' RGB(219, 229, 241) cells.Range("B4:G4").Interior.Color = oddRowColor cells.Range("B5:G5").Interior.Color = evenRowColor cells.Range("B6:G6").Interior.Color = oddRowColor cells.Range("B7:G7").Interior.Color = evenRowColor WScript.Echo "Done"
Supported functions and properties
The library is limited and supports a certain subset of Excel functions:
Google account settings
By default, GSpread.NET uses its sample identifiers. But we recommend to change it into your own. In order to do this, please, make the following steps: Go to your Google Drive http://drive.google.com and create a new script (Select “NEW > More > Google Apps Script”):
If there is no such menu option, you can select “Connect more apps”, search for “apps script” in the opened dialog and then connect to Google Apps Script.
Copy the script code from “<Project Folder>/src/GSpreadCOM/Resources/GoogleAppsScript.gs” to the Code.gs you’ve just created. Also, you can find the script here: http://scand.com/download/products/GSpread.NET/GoogleAppsScript.gs
Save script project as e.g. “GSpreadCOM_Script”:
Than, open menu “Publish > Deploy as API executable…”
Set any version, e.g. 1 and press “Deploy”:
Now we need to configure GSpreadCOM_Script project.
Go to Google API Console https://console.developers.google.com and select a project:
Go to “Overview > Google APIs”, search and enable Google Drive API:
and Google Apps Script Execution API:
Now we need to create credentials:
Select “OAuth client ID”:
Application type – “Other” and press “Create”:
Copy “client ID” and “secret ID” into GSpreadCOM.config
You can use this values as the second parameter of Application.MailLogon method in “client_id|client_secret” format:
We believe in freedom! This product is provided "AS IS" under the terms of the GNU GPL v3. Download:
Setup, both 32 and 64 bit: GSpread.NET-1.0-setup.exe
You can request any specific features you need — and order the development of your own custom solution, fast and cost-effective