Last weekend I found myself needing to make use of the Google Spreadsheet API using a service account to manage spreadsheets belonging to users of a Google Apps domain from within a .NET application.

API Libraries

Google provide two different .NET libraries for using their various APIs:

• google-gdata - this library is used to talk to the older GData-based APIs such as the documents list API and the spreadsheet API
• google-api-dotnet-client - this library is newer and can be used to talk to newer APIs such as the Google Drive API

The latter library has built-in support for using a service account to access APIs on behalf of any given user of a Google Apps domain, as illustrated in this Google Drive example.

The Problem

Essentially my problem was how to do this using the gdata library as well. My application wanted to be able to place a new spreadsheet into a user's drive and fill it with pertinent information. Reading through the documentation for the spreadsheets API makes it quite clear that the purpose of the API is to read and modify existing spreadsheets and not to create/delete them.

It is possible to create a new spreadsheet by uploading a spreadsheet file via the Google Documents List API. The Spreadsheets API does not currently provide a way to delete a spreadsheet, but this is also provided in the Documents List API. For testing purposes, you may create a spreadsheet manually or upload one.

Since the documents list API is now deprecated in favour of the new Drive API, my first step is to create a blank spreadsheet in the user's drive using this API. Essentially this was a case of using the Google Drive example above and tweaking it so instead of uploading a text file, it created a new Google spreadsheet.

Authenticating with the Service

This is an almost exact copy of the code used in the above-mentioned example. It simply creates an OAuth2Authenticator<AssertionFlowClient> which carries all the details required (application's service account credentials and the name of the user we wish to work on behalf of).

var certificate = new X509Certificate2(KeyFile, KeyPass, X509KeyStorageFlags.Exportable);

var afc = new AssertionFlowClient(GoogleAuthenticationServer.Description, certificate) {
    ServiceAccountId = ServiceAccount,
    Scope = DriveService.Scopes.Drive.GetStringValue(),
    ServiceAccountUser = User
};
var auth = new OAuth2Authenticator<AssertionFlowClient>(afc, AssertionFlowClient.GetState);

Creating the Spreadsheet

Thanks to this very helpful Stack Overflow question from someone doing a similar thing in Java, I was able to establish both that what I wanted was possible and how I should go about creating the spreadsheet.

var driveService = new DriveService(auth);

var file = new File();
file.Title = "Test spreadsheet";
file.Description = string.Format("Created via {0} at {1}", ApplicationName, DateTime.Now.ToString());
file.MimeType = "application/vnd.google-apps.spreadsheet";

var request = driveService.Files.Insert(file);
var result = request.Fetch();
var spreadsheetLink = "https://spreadsheets.google.com/feeds/spreadsheets/"  + result.Id;

Console.WriteLine("Created at " + spreadsheetLink);

The key thing here is the MIME type which identifies the file as a spreadsheet and the fact that we are only ever creating the file resource - never uploading any file contents.

Filling the Spreadsheet

This is where the real problem starts: we need to manipulate the spreadsheet using the Spreadsheets API. Using the .NET libraries, this means we now need to switch over to using the gdata library since the spreadsheet API is one of the older APIs which the newer library doesn't support.

Actually filling the spreadsheet with data isn't going to be a problem, as that is very well documented. However we need to authorise first and there appears to be no way to do that using OAuth2 and a service account (for the .NET library at least).

After a lot of fruitless searching on Google, I posted a very detailed question about the issue on Stack Overflow hoping that someone else had been here before and found a solution. Alas, there was no answer.

Keeping it DRY

After spending some time poking around in both libraries, particularly the gdata one, it struck me that for my particular use-case, I don't really want to have both libraries doing the same thing anyway. As far as I can tell, the older library has absolutely no support for using a service account but since the newer one has, there must be some way to make use of that.

It looked to me that my best bet was to subclass the GDataRequestFactory and GDataRequest classes so it was able to take an instance of IAuthenticator (which is what my already-working OAuth2Authenticator<AssertionFlowClient> implements). Certainly all the work which has gone into making all that stuff work isn't something I want to recreate - and in terms of the factory and request classes, I basically want to add just enough to have the IAuthenticator perform authentication on the request. The rest isn't something I care about changing.

Sadly as I started down this route, I slammed to a halt finding that the constructor for GDataRequest was internal. I was able to create my own subclass of GDataRequestFactory without issue, but without the accompanying request class, it would all be for nothing. I guess nobody ever thought they'd actually want to instantiate their own or subclass it. I guess given normal usage of the library, they're quite right.

After some more hunting around, it looked like there was no way to get around this apart from reflection. Very well... if that is how it must be. I created my own implementation of the request class which implemented all the same interfaces. It is essentially a wrapper around a normal GDataRequest instance, instantiated via reflection, but with a couple of additional lines added to authenticate the request prior to making it.

The Final Solution

These are the two classes I created, as described above. The reflection approach does tie it somewhat to the particular version of the library I used (at time of writing, the latest version), but I am not too worried about it - I can always update it in the future if necessary.

public class ServiceAccountGDataRequestFactory : GDataRequestFactory {
    private IAuthenticator _authenticator;

    public ServiceAccountGDataRequestFactory(string userAgent, IAuthenticator auth) : base(userAgent) {
        _authenticator = auth;
    }

    public override IGDataRequest CreateRequest(GDataRequestType type, Uri uriTarget) {
        return new ServiceAccountGDataRequest(type, uriTarget, this);
    }

    public IAuthenticator Authenticator {
        get { return _authenticator; }
    }
}

public class ServiceAccountGDataRequest : IGDataRequest, IDisposable, ISupportsEtag {
    private GDataRequest _underlying;
    private ServiceAccountGDataRequestFactory _factory;

    public ServiceAccountGDataRequest(GDataRequestType type, Uri uriTarget, ServiceAccountGDataRequestFactory factory) {
        _factory = factory;

        var ctor = typeof(GDataRequest).GetConstructors(BindingFlags.Instance | BindingFlags.NonPublic)[0];
        _underlying = (GDataRequest)ctor.Invoke(new object[] { type, uriTarget, factory });
    }


    public GDataCredentials Credentials {
        get {
            return _underlying.Credentials;
        }
        set {
            _underlying.Credentials = value;
        }
    }

    public void Execute() {
        var request = _underlying.GetFinalizedRequest();

        // do auth
        _factory.Authenticator.ApplyAuthenticationToRequest(request);

        _underlying.Execute();
    }

    public System.IO.Stream GetRequestStream() {
        return _underlying.GetRequestStream();
    }

    public System.IO.Stream GetResponseStream() {
        return _underlying.GetResponseStream();
    }

    public DateTime IfModifiedSince {
        get {
            return _underlying.IfModifiedSince;
        }
        set {
            _underlying.IfModifiedSince = value;
        }
    }

    public bool UseGZip {
        get {
            return _underlying.UseGZip;
        }
        set {
            _underlying.UseGZip = value;
        }
    }

    public void Dispose() {
        _underlying.Dispose();
    }

    public string Etag {
        get {
            return _underlying.Etag;
        }
        set {
            _underlying.Etag = value;
        }
    }
}

This can then be used when working with the spreadsheets API in code as below. Mote that 'auth' is still the same instance of OAuth2Authenticator<AssertionFlowClient> we used above. It will need the scope expanding to include the spreadsheets API scope (and any others you need).

var spreadsheetsService = new SpreadsheetsService(ApplicationName);
// This is the key line for authenticating...    
spreadsheetsService.RequestFactory = new ServiceAccountGDataRequestFactory(ApplicationName, auth);

// Now use spreadsheetsService as required.

Job done.

Check out a fully working example in this Gist. You'll need to make sure to add the correct path to your certificate and provide proper credentials (see the top of Program.cs. You'll also need to reference the necessary libraries. Feel free to use this code (BSD licence), and let me know if you find a better way.