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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 lt Entity gt  tableName     db CreateTable lt Entity gt  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  gt  -1000  amp  amp  r StringProp     hello          orderby r IntProp         select r

User · Answer

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  jo gmail com    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

User · Answer

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

User · Answer

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 lt IList lt Object gt  gt  values   response Values              if  values    null  amp  amp  values Count  gt  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

User · Answer

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

[c#] Accessing Google Spreadsheets with C# using Google Data API

Examples related to c#

Examples related to google-sheets

Examples related to google-api-dotnet-client

Examples related to google-data-api

Examples related to google-sheets-api