GSpread.NET - 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 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 spreadsheet from scratch – GSpread.NET will certainly help you.

Why GSpread.NET

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.

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 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.

1
2
3
4
// Name - User name, any you like
// ClientIdAndSecret - `client_id|client_secret` format
// ScriptId - Google Apps script ID
app.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 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.

Example 4

Let’s create a simple table with formulas and styles:

GSpread .NET

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:

ObjectMethodsProperties
  • Excel.Application
  • MailLogon
  • Quit
  • Save
  • SaveWorkspace
  • ActiveCell
  • ActiveSheet
  • ActiveWorkbook
  • Cells
  • Columns
  • DecimalSeparator
  • Range
  • Rows
  • Sheets
  • ThousandsSeparator
  • Workbooks
  • Worksheets
  • Excel.Workbooks
  • Add – templates will not be supported
  • Open – limited options set will be supported to open only google spreadsheets by name
  • Close
  • Count
  • Item
  • Excel.Workbook
  • Activate
  • Close
  • ExportAsFixedFormat – only PDF format will be supported (may be excluded from 1st version)
  • Save
  • SaveAs – only google spreadsheet format is supported)
  • ActiveSheet
  • Application
  • FullName
  • Name
  • Sheets
  • Worksheets
  • Excel.Worksheets
  • Add – only Worksheet can be added
  • Count
  • Item
  • Excel.Worksheet
  • Activate
  • Delete
  • Application
  • Cells
  • Columns
  • Name
  • Range
  • Rows
  • Excel.Range
  • BorderAround
  • Clear
  • ClearContents
  • ClearFormats
  • Insert
  • Merge
  • UnMerge
  • Address
  • Borders
  • Cells
  • Column
  • Columns
  • ColumnWidth – implemented in pixels
  • Count
  • EntireColumn
  • EntireRow
  • Font
  • Formula
  • FormulaR1C1
  • HorizontalAlignment
  • Interior
  • Item
  • NumberFormat
  • Offset
  • Range
  • Row
  • Rows
  • RowHeight
  • Style
  • Text
  • Value
  • Value2
  • VerticalAlignment
  • Worksheet
  • WrapText
  • Excel.Style
  • Borders
  • Font
  • HorizontalAlignment
  • Interior
  • NumberFormat
  • VerticalAlignment
  • WrapText
  • Excel.Font
  • Bold
  • Color
  • ColorIndex
  • Italic
  • Size
  • Strikethrough
  • Underline
  • Excel.Interior
  • Color
  • ColorIndex
  • Excel.Borders
  • Color
  • ColorIndex
  • Count
  • Item
  • LineStyle
  • Weight
  • Excel.Border
  • Color
  • ColorIndex
  • LineStyle
  • Weight

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”):

Google Settings

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.

GSpread .NET

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

GSpread .NET

Save script project as e.g. “GSpreadCOM_Script”:

GSpread .NET

Than, open menu “Publish > Deploy as API executable…”

GSpread .NET

Set any version, e.g. 1 and press “Deploy”:

GSpread .NET

GSpread .NET

Now we need to configure GSpreadCOM_Script project.
Go to Google API Console https://console.developers.google.com and select a project:

GSpread .NET

Go to “Overview > Google APIs”, search and enable Google Drive API:

GSpread .NET

GSpread .NET

and Google Apps Script Execution API:

GSpread .NET

GSpread .NET

Now we need to create credentials:

GSpread .NET

Select “OAuth client ID”:

GSpread .NET

Application type – “Other” and press “Create”:

GSpread .NET

GSpread .NET

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");

Downloads

We believe in freedom! This product is provided "AS IS" under the terms of the GNU GPL v3. Download:

32/64 bit

Setup, both 32 and 64 bit: GSpread.NET-1.0-setup.exe

Source code

Customization

You can request any specific features you need — and order the development of your own custom solution, fast and cost-effective

Require a similar product development?

Support

Browse
Attachment: filename ×

Thank you for your interest in our products! We have received your message and will reply to you as soon as possible. Please check your mailbox for a confirmation letter from us.