Accessing 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#.
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.
The code is using a service account so unless you have had it create a sheet first it wont be able to retrieve anything.
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);
}
You can add the service account user to the editors of the sheet if you want them to edit it.
Same with the ones shared with you, email the service account email address to the owner and have them add it as an editor.
-Troy
Hi Preguntón, I obtain ALWAYS feed.Entries.Count =0
With both method, your or Linda.
Any ideas?
sorry my english!
did you create a sheet first on the service account?
Yes, is a public sheet!
https://docs.google.com/spreadsheets/d/1jWExVw52NQm5jWuPM6fJlvYDA5Trdal42qlGn_ra3sU/
Have you tried giving the service account access to the sheet instead of setting the sheet public?
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
Linda.
Thank you soooo much for this post. I can’t tell you how much I appreciate it.
Don.
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).
How ot grant aceess to Spreadsheet?
Go to Google drive share the sheet with the Service account email address.
is there anyway around not sharing the sheet?
you could probably do it with oauth2 as well. the user will then have to authenticate your code.
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.
Its service account credentials you have to create it on Google Developers console.
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.
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.
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
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.