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