Google Spreadsheets .NET API
GSpread.NET is a fast and easy to use library designed for working with Google Spreadsheets by using the well-known API from Microsoft Excel.
We’re exposing the API into COM, so you have the 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:
1 ––– Set objExcel = CreateObject("Excel.Application")
2 +++ Set objExcel = CreateObject("GSpreadCOM.Application")
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 spreadsheets from scratch – GSpread.NET will certainly help you.
A 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 the 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 documents based on extracted data. The challenge began when some of that 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 the 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 the MailLogon method to Excel. Application interface. So, we use this method for authentication in Google, and, for convenience, we get default credentials from the 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 the 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 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"
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.
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 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: https://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: