Google Sheets with C# 21


Google spead sheetsAccessing Google Sheets with C# after the Google client login shut down is not as easy as you might think.    The problem is that the Google Sheets is part of the old Gdata api and becouse of that you have to use the Gdata library to access it.   The current Google .net client library which supports Oauth2 and Service accounts only supports the newer discovery APIs.   That doesnt mean that it cant be done it just means that you need to merge two diffrent client librarys in the same project to get it to work.

This tutorial is not complte it is a work in progress, please come with feed back so that we can improve it.

 NuGet Packages

You will need the NuGet packages from both client libraries.   The first one is the current Google .net client library which we need to authentcate.   The second is the old Gdata library which we use to access Google SpreadSheets.

PM> Install-Package Google.Apis.Auth
PM> Install-Package Google.GData.Spreadsheets

The Code

Again this is a work in progress I will fix this as I get it working. It should be possible to use OAuth2 and a public API key as well I just haven’t had time to get it working. This should give you a head start if you are like me trying to access Google Sheets with C#.


About Linda Lawton

My name is Linda Lawton I have more than 20 years experience working as an application developer and a database expert. I have also been working with Google APIs since 2012 and I have been contributing to the Google .Net client library since 2013. In 2013 I became a a Google Developer Experts for Google Analytics.

Leave a Reply to Linda Lawton Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

21 thoughts on “Google Sheets with C#

  • Dave

    Hi Linda. Thank you for your effort.

    I am a newbie to Google API. I have followed your example. The program run smooth but no spreadsheets can be retrieved, i.e. feed.Entries.Count is equal to 0. Is there any setting i have missed ? Many Thanks.

  • Preguntón Cojonero Cabrón

    Great post!. marvellous.

    For extending it, my case is:

    I want get columns-rows of a public Spreedsheet, not mine

    For example:
    https://docs.google.com/spreadsheets/d/14ZjJQ_VeT8mKmO8MANA1Os5zwRA3Pu_rpFdfyFtBZ4A/edit?pli=1#gid=0

    I try this, but not solution, only get errors:

    var docURL = “https://docs.google.com/spreadsheets/d/14ZjJQ_VeT8mKmO8MANA1Os5zwRA3Pu_rpFdfyFtBZ4A/edit?pli=1#gid=0”;

    //docURL = “https://spreadsheets.google.com/feeds/worksheets/14ZjJQ_VeT8mKmO8MANA1Os5zwRA3Pu_rpFdfyFtBZ4A/private/full”;

    //docURL = “https://docs.google.com/spreadsheets/d/14ZjJQ_VeT8mKmO8MANA1Os5zwRA3Pu_rpFdfyFtBZ4A/pubhtml”;

    var queryByUrl = new SpreadsheetQuery(docURL);
    WorksheetQuery query2 = new WorksheetQuery(docURL);

    // Make a request to the API and get all spreadsheets.
    feed = service.Query(queryByUrl);
    if (feed.Entries.Count == 0)
    {
    Console.WriteLine(“***** There are no sheets”);
    }

    WorksheetFeed feed2a = service.Query(query2);

    if (feed2a == null)
    {
    Console.WriteLine(“***** There are no WorksheetFeed”);
    return;
    }

    foreach (WorksheetEntry worksheet in feed2a.Entries)
    {
    Console.WriteLine(worksheet.Title.Text);

    }

  • Peter

    Hi Linda, I just wanted to thank you very much for all the efforts you put into exploring the Google API and explaining it. It is very much appreciated!!!
    On a sidenote to this: I put the 2 codes together and did not have any trouble, I just used the ‘new’ lib to get the authentication, took the AccessToken from the credential class and passed it over to the ‘old’ lib. It was a quick and dirty POC so no code to publish, but from what I can see from these first tests, it should work fine.
    Best Regards
    Peter

  • haze

    Thank you!

    Note for other users that are new to this:

    Add your using statements:
    using Google.GData.Client;
    using Google.GData.Spreadsheets;
    using Google.Apis.Auth.OAuth2;
    using System.Security.Cryptography.X509Certificates;

    After you create your Service Account, make sure you grant it access to SpreadSheet(s).

  • John Lee

    Hi I tried searching the web for a way to access google sheets using c# and I think your method is closed to the direction I was heading.
    A tutorial I read wanted me to copy and paste the access code every time the application accessed google sheets. I had to find a way to pass the access code from google to the application automatically. But I couldn’t figure out a good method for it.
    Anyways I’m trying to use your method but I’m not sure how to make the first line work for me. Not sure what type of certificate file you’re using.
    var certificate = new X509Certificate2(@”c:\Diamto Test Everything Project.p12″, “notasecret”, X509KeyStorageFlags.Exportable);

    Thanks for helping.

  • Eva

    Hi Linda,
    Awesome post! Thank you so much for these explanations. I got a problem, could you please help me with it? I have followed example: https://developers.google.com/sheets/quickstart/dotnet , the program ran perfectly on WebApplication, but with the same code not working on windows service. When I’m debugging it vs says that it can’t find GoogleClientSecrets.cs and then NewtonsoftJsonSerializer.cs not found. I couldn’t figure out what happened. Did I miss something? Thank you for helping.

    • Linda Lawton Post author

      sounds like an issue with the dlls. Make sure you have all the referenced libraries set to copy local. Windows services can cause issues also depending upon which user they are running as it needs to have permissions to access the files.

  • marwa

    Hi Linda!
    Thank you for sharing the post!
    I’m currently trying to load data from google spreadsheet which works completely fine,
    I”m using the application within an SSIS package in order to automatically load data from the spreadsheet to the dwh,
    The problem is when executing the task on sql server, it holds on because it requires authentication (mail address and password),
    Is there somehow a solution that could avoid this pop up window asking for authorization since on the server the window cannot be pop up

    • Linda Lawton Post author

      You are going to have to figure out how to retrieve a refresh token. Once you have a refresh token you can then use that to request a new access token which you can do programmaticly it wont require popping up the window then.