Accessing Google Spreadsheets with C# using Google Data API

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.

From stackoverflow

• I'm pretty sure there'll be some C# SDKs / toolkits on Google Code for this. I found this one, but there may be others so it's worth having a browse around.

• http://code.google.com/apis/gdata/articles/dotnet_client_lib.html

  This should get you started. I haven't played with it lately but I downloaded a very old version a while back and it seemed pretty solid. This one is updated to Visual Studio 2008 as well so check out the docs!

• You can do what you're asking several ways:

  1. Using Google's spreadsheet C# library (as in Tacoman667's answer) to fetch a ListFeed which can return a list of rows (ListEntry in Google parlance) each of which has a list of name-value pairs. The Google spreadsheet API (http://code.google.com/apis/spreadsheets/code.html) documentation has more than enough information to get you started.

  2. Using the Google visualization API which lets you submit more sophisticated (almost like SQL) queries to fetch only the rows/columns you require.

  3. The spreadsheet contents are returned as Atom feeds so you can use XPath or SAX parsing to extract the contents of a list feed. There is an example of doing it this way (in Java and Javascript only though I'm afraid) at http://gqlx.twyst.co.za.

• According to the .Net user guide: http://code.google.com/apis/spreadsheets/docs/2.0/developers_guide_dotnet.html

  Download the .Net client library: http://code.google.com/p/google-gdata/

  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("jo@gmail.com", "mypassword");
  

  Get a list of spreadsheets:

  SpreadsheetQuery query = new SpreadsheetQuery();
  SpreadsheetFeed feed = service.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);
  }
  

• 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("you@gmail.com", "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;