Access to a private Google Spreadsheet with C#

Question

I have an AngularJS + C#.NET OnePage Application Website. The goal is to retrieve a JSON from a private Google Spreadsheet that I own with C# (not with AngularJS). I read various Google Sheets Documentation and API, OAuth 2.0 etc. and tried some examples, but none of them seem to work for me. I see there are different ways to access and retrieve data from a Google Spreadsheet, still, they don't work for my case. Can anyone help me? Thank you.

Edit: I managed to get a token by creating an Other Application type under Google Developers Console=>Credentials=>Create Client ID. Here's the C# Console Application:

using System;
using Google.GData.Client;
using Google.GData.Spreadsheets;

namespace MySpreadsheetIntegration
{
  class Program
  {
    static void Main(string[] args)
    {
      ////////////////////////////////////////////////////////////////////////////
      // STEP 1: Configure how to perform OAuth 2.0
      ////////////////////////////////////////////////////////////////////////////

      // TODO: Update the following information with that obtained from
      // https://code.google.com/apis/console. After registering
      // your application, these will be provided for you.

      string CLIENT_ID = "12345678.apps.googleusercontent.com";

      // This is the OAuth 2.0 Client Secret retrieved
      // above.  Be sure to store this value securely.  Leaking this
      // value would enable others to act on behalf of your application!
      string CLIENT_SECRET = "Gc0230jdsah01jqpowpgff";

      // Space separated list of scopes for which to request access.
      string SCOPE = "https://spreadsheets.google.com/feeds https://docs.google.com/feeds";

      // This is the Redirect URI for installed applications.
      // If you are building a web application, you have to set your
      // Redirect URI at https://code.google.com/apis/console.
      string REDIRECT_URI = "urn:ietf:wg:oauth:2.0:oob";

      ////////////////////////////////////////////////////////////////////////////
      // STEP 2: Set up the OAuth 2.0 object
      ////////////////////////////////////////////////////////////////////////////

      // OAuth2Parameters holds all the parameters related to OAuth 2.0.
      OAuth2Parameters parameters = new OAuth2Parameters();

      // Set your OAuth 2.0 Client Id (which you can register at
      // https://code.google.com/apis/console).
      parameters.ClientId = CLIENT_ID;

      // Set your OAuth 2.0 Client Secret, which can be obtained at
      // https://code.google.com/apis/console.
      parameters.ClientSecret = CLIENT_SECRET;

      // Set your Redirect URI, which can be registered at
      // https://code.google.com/apis/console.
      parameters.RedirectUri = REDIRECT_URI;

      ////////////////////////////////////////////////////////////////////////////
      // STEP 3: Get the Authorization URL
      ////////////////////////////////////////////////////////////////////////////

      // Set the scope for this particular service.
      parameters.Scope = SCOPE;

      // Get the authorization url.  The user of your application must visit
      // this url in order to authorize with Google.  If you are building a
      // browser-based application, you can redirect the user to the authorization
      // url.
      string authorizationUrl = OAuthUtil.CreateOAuth2AuthorizationUrl(parameters);
      Console.WriteLine(authorizationUrl);
      Console.WriteLine("Please visit the URL above to authorize your OAuth "
        + "request token.  Once that is complete, type in your access code to "
        + "continue...");
      parameters.AccessCode = Console.ReadLine();

      ////////////////////////////////////////////////////////////////////////////
      // STEP 4: Get the Access Token
      ////////////////////////////////////////////////////////////////////////////

      // Once the user authorizes with Google, the request token can be exchanged
      // for a long-lived access token.  If you are building a browser-based
      // application, you should parse the incoming request token from the url and
      // set it in OAuthParameters before calling GetAccessToken().
      OAuthUtil.GetAccessToken(parameters);
      string accessToken = parameters.AccessToken;
      Console.WriteLine("OAuth Access Token: " + accessToken);

      ////////////////////////////////////////////////////////////////////////////
      // STEP 5: Make an OAuth authorized request to Google
      ////////////////////////////////////////////////////////////////////////////

      // Initialize the variables needed to make the request
      GOAuth2RequestFactory requestFactory =
          new GOAuth2RequestFactory(null, "MySpreadsheetIntegration-v1", parameters);
      SpreadsheetsService service = new SpreadsheetsService("MySpreadsheetIntegration-v1");
      service.RequestFactory = requestFactory;

      Console.ReadLine();
    }
  }
}

With this code I have to copy the link I get and paste it in a browser in order to get a token. Is there a way to get this token directly in my app without the need to copy the link manually?

Answer 1

I found another working solution where you don't have to open a browser window which is similar to the solution with the p12-key above.

First, create in the Google Developer Console under Create Credentials a Service account key.

Then, you have to choose a service account type, you can choose App Engine Default Service account and you can download a json file that contains the private key.

With this file, you can create programmatically Google credentials (where "sheets-test.json" is the downloaded json file):

var credential = GoogleCredential.FromStream(new FileStream("Sheets-test.json", FileMode.Open)).CreateScoped(Scopes);

Give the service user (you can find it in the json file in the client_email field) access to your spreadsheet.

Here is the full code that enables you to append a value to the first free row in a spreadsheet, I modified the google .NET quickstart project:

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

namespace SheetApiTest
{
    public class SheetApiWithGoogleCredentials
    {
        static string[] Scopes = { SheetsService.Scope.Spreadsheets };
        static string ApplicationName = "Google Sheets API .NET Quickstart";

        public void AppendData()
        {
            // the downloaded jsonn file with private key
            var credential = GoogleCredential.FromStream(new FileStream("Sheets-test.json", FileMode.Open)).CreateScoped(Scopes);

            var service = new SheetsService(new BaseClientService.Initializer()
            {
                HttpClientInitializer = credential,
                ApplicationName = ApplicationName,
            });

            // spreadsheet id - your own spreadsheet id
            var spreadsheetId = "11AwV7d1pEPq4x-rx9WeZHNwGJa0ehfRhh760";

            var valueRange = new ValueRange { Values = new List<IList<object>> { new List<object>() } };
            valueRange.Values[0].Add(DateTime.Now.ToLongTimeString());

            // insert here the name of your spreadsheet table
            var rangeToWrite = "Tabellenblatt1";

            var appendRequest = service.Spreadsheets.Values.Append(valueRange, spreadsheetId, rangeToWrite);
            appendRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
            var appendReponse = appendRequest.Execute();
        }
    }
}

Answer 2

There is a way for server-to-server communication using certificate instead of copy/paste the provided by Google AccessCode for the communication to be established.

First you will need to get your certificate from Google Console. If you haven't already created a project do so and give it a name. Then:

1. Go to "Credentials" section (found on the left menu) and
2. Click on the button "Create Credentials" and choose "Service account key"
3. Choose the service account to be "App Engine default service account" and
4. Choose the "key type" to be "p.12" which is the certificate type.

Image for reference:

After these steps are completed, the certificate will be automatically downloaded. Upload it in some folder in your project and use it as shown below.

Note: for the sake of simplicity the code is placed in the controller.

    public async Task<ActionResult> ServerAuth()
    {
        ViewBag.Message = "Server to server authentication";
        List<string> records = new List<string>();

        const string ServiceAccountEmail = "your-account@appspot.gserviceaccount.com";
        string fullKeyPath = HttpContext.Server.MapPath("~/Key/MyProjectKey.p12"); // The certificate generated by Google and uploaded in the project.

        var certificate = new X509Certificate2(fullKeyPath, "notasecret", X509KeyStorageFlags.Exportable); // "notasecret" is the password for the certificate

        var serviceAccountCredentialInitializer = new ServiceAccountCredential.Initializer(ServiceAccountEmail)
        {
            Scopes = new[] { "https://spreadsheets.google.com/feeds", "http://spreadsheets.google.com/feeds/spreadsheets/private/full" }  
        }.FromCertificate(certificate);

        var credential = new ServiceAccountCredential(serviceAccountCredentialInitializer);

        if (!await credential.RequestAccessTokenAsync(System.Threading.CancellationToken.None))
        {
            throw new InvalidOperationException("Access token request failed.");
        }

        var requestFactory = new GDataRequestFactory(null);
        requestFactory.CustomHeaders.Add("Authorization: Bearer " + credential.Token.AccessToken);

        var service = new SpreadsheetsService(null) { RequestFactory = requestFactory };

        SpreadsheetQuery query = new SpreadsheetQuery();
        query.Title = "Test Sheet"; // The exact name of the sheet you want to read
        query.Exact = true;

        var feed = service.Query(query);

        foreach (SpreadsheetEntry entry in feed.Entries)
        {
            foreach (WorksheetEntry worksheet in entry.Worksheets.Entries.Cast<WorksheetEntry>())
            {
                CellQuery cellQuery = new CellQuery(worksheet.CellFeedLink);
                CellFeed cellFeed = service.Query(cellQuery);

                foreach (CellEntry cell in cellFeed.Entries)
                {
                    records.Add(cell.InputValue);
                }
            }
        }

        return View(records);
    }