CODEWITHSUNDEEP
c#google-sheetsgoogle-api-dotnet-clientgoogle-data-apigoogle-sheets-api
blitzkriegz
blitzkriegz

Reputation: 9576

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.

Upvotes: 111

Views: 151668

Answers (5)

wescpy
wescpy

Reputation: 11167

(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:

Upvotes: 25

Ogglas
Ogglas

Reputation: 69928

The most upvoted answer from @Kelly is no longer valid as @wescpy says. However after 2020-03-03 it will not work at all since the library used uses Google Sheets v3 API.

The Google Sheets v3 API will be shut down on March 3, 2020

https://developers.google.com/sheets/api/v3

This was announced 2019-09-10 by Google:

https://cloud.google.com/blog/products/g-suite/migrate-your-apps-use-latest-sheets-api

New code sample for Google Sheets v4 API:

Go to

https://developers.google.com/sheets/api/quickstart/dotnet

and generate credentials.json. Then install Google.Apis.Sheets.v4 NuGet and try the following sample:

Note that I got the error Unable to parse range: Class Data!A2:E with the example code but with my spreadsheet. Changing to Sheet1!A2:E worked however since my sheet was named that. Also worked with only A2:E.

using Google.Apis.Auth.OAuth2;
using Google.Apis.Sheets.v4;
using Google.Apis.Sheets.v4.Data;
using Google.Apis.Services;
using Google.Apis.Util.Store;
using System;
using System.Collections.Generic;
using System.IO;
using System.Threading;

namespace SheetsQuickstart
{
    class Program
    {
        // If modifying these scopes, delete your previously saved credentials
        // at ~/.credentials/sheets.googleapis.com-dotnet-quickstart.json
        static string[] Scopes = { SheetsService.Scope.SpreadsheetsReadonly };
        static string ApplicationName = "Google Sheets API .NET Quickstart";

        static void Main(string[] args)
        {
            UserCredential credential;

            using (var stream =
                new FileStream("credentials.json", FileMode.Open, FileAccess.Read))
            {
                // The file token.json stores the user's access and refresh tokens, and is created
                // automatically when the authorization flow completes for the first time.
                string credPath = "token.json";
                credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
                    GoogleClientSecrets.Load(stream).Secrets,
                    Scopes,
                    "user",
                    CancellationToken.None,
                    new FileDataStore(credPath, true)).Result;
                Console.WriteLine("Credential file saved to: " + credPath);
            }

            // Create Google Sheets API service.
            var service = new SheetsService(new BaseClientService.Initializer()
            {
                HttpClientInitializer = credential,
                ApplicationName = ApplicationName,
            });

            // Define request parameters.
            String spreadsheetId = "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms";
            String range = "Class Data!A2:E";
            SpreadsheetsResource.ValuesResource.GetRequest request =
                    service.Spreadsheets.Values.Get(spreadsheetId, range);

            // Prints the names and majors of students in a sample spreadsheet:
            // https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
            ValueRange response = request.Execute();
            IList<IList<Object>> values = response.Values;
            if (values != null && values.Count > 0)
            {
                Console.WriteLine("Name, Major");
                foreach (var row in values)
                {
                    // Print columns A and E, which correspond to indices 0 and 4.
                    Console.WriteLine("{0}, {1}", row[0], row[4]);
                }
            }
            else
            {
                Console.WriteLine("No data found.");
            }
            Console.Read();
        }
    }
}

Upvotes: 4

Kelly
Kelly

Reputation: 3342

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

Upvotes: 187

Mauricio Scheffer
Mauricio Scheffer

Reputation: 99720

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;

Upvotes: 23

tonys
tonys

Reputation: 3984

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.

Upvotes: 3

Related Questions