Accessing Google Spreadsheets with C# using Google Data API

132,203

Solution 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);
}

Solution 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;

Solution 3

(Jun-Nov 2016) The question and its answers are now out-of-date as: 1) GData APIs are the previous generation of Google APIs. While not all GData APIs have been deprecated, all the latest Google APIs do not use the Google Data Protocol; and 2) there is a new Google Sheets API v4 (also not GData).

Moving forward from here, you need to get the Google APIs Client Library for .NET and use the latest Sheets API, which is much more powerful and flexible than any previous API. Here's a C# code sample to help get you started. Also check the .NET reference docs for the Sheets API and the .NET Google APIs Client Library developers guide.

If you're not allergic to Python (if you are, just pretend it's pseudocode ;) ), I made several videos with slightly longer, more "real-world" examples of using the API you can learn from and migrate to C# if desired:

Solution 4

This Twilio blog page made on March 24, 2017 by Marcos Placona may be helpful.

Google Spreadsheets and .NET Core

It references Google.Api.Sheets.v4 and OAuth2.

Solution 5

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.

Share:
132,203

Related videos on Youtube

blitzkriegz
Author by

blitzkriegz

Updated on July 08, 2022

Comments

  • blitzkriegz
    blitzkriegz over 1 year

    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.

  • Ian Davis
    Ian Davis about 9 years
    What should I use for the string value for new SpreadsheetsService("exampleCo-exampleApp-1") ? does it matter what I put in there? Thanks!
  • Mauricio Scheffer
    Mauricio Scheffer almost 9 years
    @Kiquenet What do you mean? The last version I see of Google.GData.* is 2.2.0 nuget.org/packages/Google.GData.Documents
  • Kiquenet
    Kiquenet almost 9 years
    developers.google.com/google-apps/spreadsheets Versión 3.0 API (OAuth, etc)
  • Mauricio Scheffer
    Mauricio Scheffer almost 9 years
    @Kiquenet Let me know when Google updates their .NET libraries. But I think Google.GData.* 2.2.0 already uses API v3.
  • SQLBobScot
    SQLBobScot over 8 years
    Get a list of spreadsheets: "SpreadsheetQuery query = new SpreadsheetQuery();" should read "SpreadsheetFeed feed = myService.Query(query);" Tried to edit not enough characters changed !
  • Kiquenet
    Kiquenet over 8 years
    developers.google.com/google-apps/spreadsheets/authorize Important: OAuth 1.0 is no longer supported and will be disabled on May 5, 2015. If your application uses OAuth 1.0, you must migrate to OAuth 2.0 or your application will cease functioning.
  • Kiquenet
    Kiquenet over 8 years
    developers.google.com/google-apps/spreadsheets/authorize Important: OAuth 1.0 is no longer supported and will be disabled on May 5, 2015. If your application uses OAuth 1.0, you must migrate to OAuth 2.0 or your application will cease functioning.
  • joon
    joon over 7 years
    this link, from @wescpy below, helped me find more relevant info for mid 2016: googleappsdeveloper.blogspot.com/2016/05/…
  • afr0
    afr0 over 5 years
    can these tools be used for accessing Microsoft Excel files as well?
  • wescpy
    wescpy over 5 years
    Unfortunately, both Microsoft & Google are making competitive products which don't adhere to a common standard, so you have to find your own tools to access Excel files. If you're a Python developer, then check out python-excel.org. For other languages, you'll have to search their respective communities. Alternatively, you can import Excel files to Google (using the Drive API), then use the Sheets API to perform the operations you desire. Google APIs support a wide variety of languages... see developers.google.com/api-client-library
  • Ogglas
    Ogglas almost 4 years
    Will not work after 2020-03-03 since the library used uses Google Sheets v3 API cloud.google.com/blog/products/g-suite/…
  • Blake Niemyjski
    Blake Niemyjski almost 4 years
    How do I get around not having to specify client id/secrets and scopes? I've already done the OAuth flow and have an access token and refresh token (think offline mode) and I don't want any of this extra crap. I don't have access to client id and client secret as they are on an oauth relay server which I don't have access too in background services.
  • Ogglas
    Ogglas almost 4 years
    @BlakeNiemyjski Use the rest API directly, link: developers.google.com/sheets/api/reference/rest
  • PastExpiry.com
    PastExpiry.com about 2 years
    Good luck trying to do anything like inserting a row. I have been looking for 2+ hours for something that can be done in c# and not this utterly confusing script language or whatever the heck it is.