Accessing Google Spreadsheets with C# using Google Data API

Question

I'm having some information in Google Spreadsheets as a single sheet. Is there any way by which I can read this information from .NET by providing the google credentials and spreadsheet address. Is it possible using Google Data APIs. Ultimately I need to get the information from Google spreadsheet in a DataTable. How can I do it? If anyone has attempted it, pls share some information.

Answer 1

According to the .NET user guide:

Download the .NET client library:

Add these using statements:

using Google.GData.Client; using Google.GData.Extensions; using Google.GData.Spreadsheets; 

Authenticate:

SpreadsheetsService myService = new SpreadsheetsService("exampleCo-exampleApp-1"); myService.setUserCredentials("[email protected]", "mypassword"); 

Get a list of spreadsheets:

SpreadsheetQuery query = new SpreadsheetQuery(); SpreadsheetFeed feed = myService.Query(query);  Console.WriteLine("Your spreadsheets: "); foreach (SpreadsheetEntry entry in feed.Entries) {     Console.WriteLine(entry.Title.Text); } 

Given a SpreadsheetEntry you've already retrieved, you can get a list of all worksheets in this spreadsheet as follows:

AtomLink link = entry.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);  WorksheetQuery query = new WorksheetQuery(link.HRef.ToString()); WorksheetFeed feed = service.Query(query);  foreach (WorksheetEntry worksheet in feed.Entries) {     Console.WriteLine(worksheet.Title.Text); } 

And get a cell based feed:

AtomLink cellFeedLink = worksheetentry.Links.FindService(GDataSpreadsheetsNameTable.CellRel, null);  CellQuery query = new CellQuery(cellFeedLink.HRef.ToString()); CellFeed feed = service.Query(query);  Console.WriteLine("Cells in this worksheet:"); foreach (CellEntry curCell in feed.Entries) {     Console.WriteLine("Row {0}, column {1}: {2}", curCell.Cell.Row,         curCell.Cell.Column, curCell.Cell.Value); } 

Answer 2

I wrote a simple wrapper around Google's .Net client library, it exposes a simpler database-like interface, with strongly-typed record types. Here's some sample code:

public class Entity {     public int IntProp { get; set; }     public string StringProp { get; set; } }  var e1 = new Entity { IntProp = 2 }; var e2 = new Entity { StringProp = "hello" }; var client = new DatabaseClient("[email protected]", "password"); const string dbName = "IntegrationTests"; Console.WriteLine("Opening or creating database"); db = client.GetDatabase(dbName) ?? client.CreateDatabase(dbName); // databases are spreadsheets const string tableName = "IntegrationTests"; Console.WriteLine("Opening or creating table"); table = db.GetTable<Entity>(tableName) ?? db.CreateTable<Entity>(tableName); // tables are worksheets table.DeleteAll(); table.Add(e1); table.Add(e2); var r1 = table.Get(1); 

There's also a LINQ provider that translates to google's structured query operators:

var q = from r in table.AsQueryable()         where r.IntProp > -1000 && r.StringProp == "hello"         orderby r.IntProp         select r;