We’re exposing the API into COM, so you have freedom to use our component wherever you have used MS Excel API before, including scripts and applications.
You don’t need to make new scripts from scratch or rewrite old programs with the new Google API usage. Just instantiate our COM object instead of Microsoft Excel and our component will do the rest:
We support MS Excel API from MS Excel 2007 SP3 – MS Excel 2016 and Windows platform from Windows 7.
There are zillions of scripts and programs working with MS Excel out there in the programming world. With our GSpread.NET we provide you a new possibility to work with Google Spreadsheets in the same way (and using the same API!) as you have done with MS Excel for the last dozens of years.
Just try one line of the code to use our implementation instead of the Microsoft one.
The library supports the subset of MS Excel functions, so you won’t find email management, pivot tables or complicated charts. If you need to manage certain data and compute something or, perhaps, add formulas, colors and fonts, or even generate spreadsheet from scratch – GSpread.NET will certainly help you.
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.
We believe in freedom! This product is provided “AS IS” under the terms of the GNU GPL v3. Download:
In case you need it under another (for example, commercial) license, please contact us using the contact form. Commercial support is available as well.
We hope that you will enjoy using our product. Feel free to submit your feedback, comments or ideas to email@example.com.
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"
The library is limited and supports a certain subset of Excel functions:
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: