Accessing Google Spreadsheets with C# using Google Data API
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:
- Migrating SQL data to a Sheet (code deep dive post)
- Formatting text using the Sheets API (code deep dive post)
- Generating slides from spreadsheet data (code deep dive post)
- Those and others in the Sheets API video library
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:
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.
Using the Google visualization API which lets you submit more sophisticated (almost like SQL) queries to fetch only the rows/columns you require.
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.
Related videos on Youtube
blitzkriegz
Updated on July 08, 2022Comments
-
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.
-
Mohamad Mahmoud Darwish about 5 yearscheck my answer stackoverflow.com/questions/48432846/…
-
-
Ian Davis about 9 yearsWhat should I use for the string value for new SpreadsheetsService("
exampleCo-exampleApp-1
") ? does it matter what I put in there? Thanks! -
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 almost 9 yearsdevelopers.google.com/google-apps/spreadsheets Versión 3.0 API (OAuth, etc)
-
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 over 8 yearsGet a list of spreadsheets: "SpreadsheetQuery query = new SpreadsheetQuery();" should read "SpreadsheetFeed feed = myService.Query(query);" Tried to edit not enough characters changed !
-
Kiquenet over 8 yearsdevelopers.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 over 8 yearsdevelopers.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 over 7 yearsthis link, from @wescpy below, helped me find more relevant info for mid 2016: googleappsdeveloper.blogspot.com/2016/05/…
-
afr0 over 5 yearscan these tools be used for accessing Microsoft Excel files as well?
-
wescpy over 5 yearsUnfortunately, 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 almost 4 yearsWill not work after 2020-03-03 since the library used uses Google Sheets v3 API cloud.google.com/blog/products/g-suite/…
-
Blake Niemyjski almost 4 yearsHow 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 almost 4 years@BlakeNiemyjski Use the rest API directly, link: developers.google.com/sheets/api/reference/rest
-
PastExpiry.com about 2 yearsGood 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.