GSpread.NET
Google Spreadsheets .NET API
Description
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")
Main Functions
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.
Why GSpread.NET
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.
Conclusion
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.
|
1
2
3
4
|
// Name - User name, any you like// ClientIdAndSecret - `client_id|client_secret` format// ScriptId - Google Apps script IDapp.MailLogon(Name, ClientIdAndSecret, ScriptId); |
Tutorial
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.
Example 4
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[0]:R[-1]C[0])"
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:
| Object | Methods | Properties |
|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
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
|
1
2
3
4
5
6
7
|
<?xml version="1.0" encoding="utf-8"?><config><user name="any random name"/><client id="52444250292-02d5nuj5osdf268h605djiukijd5i4m5.apps.googleusercontent.com"secret="q2IOcXHThZ3LPc5nfFCcA_r"/><script id="Mvi58b-XTLQOC7mbLFOvENmbpWLEH-geZ"/></config> |
You can use this values as the second parameter of Application.MailLogon method in “client_id|client_secret” format:
|
1
2
3
|
app.MailLogon("any random name", "52444250292-02d5nuj5osdf268h605djiukijd5i4m5.apps.googleusercontent.com|q2IOcXH-ThZ3LPc5nfFCcA_r", "Mx1qw1DNjz3vrzoYbLfc5H0sBksCqgDyL"); |
Download
We believe in freedom! This product is provided "AS IS" under the terms of the GNU GPL v3.
Setup, both 32 and 64 bit: GSpread.NET-1.0-setup.exe
You can also request any specific features you need — order our custom software development services, fast and cost-effective.
Need a Similar Solution?
Related Products
DbFS.NET
DbFS.NET is a high-level abstraction and implementation, which supports files (with versions and branches) in MS SQL database.
.NET FontManager
With the help of .NET FontManager you can use the common type of font files, get contours of every glyph and draw them as you need.
ASP.NET MVC Modules
Open source MVC framework for .NET to enable packing multiple resources, including views, icons, scripts, etc. in one DLL.