Wednesday, October 8, 2008

CRUD API for Google Spreadsheets

For a little project I'm coding, I needed to programatically post new rows to a Google spreadsheet. Luckily, there are .NET bindings to the Google Data APIs. But they're still too low-level if you want to use the spreadsheet as a database. I looked around the source repo for awhile and found this little gem in python. In the words of its creator, it helps:

Make the Google Documents API feel more like using a database.

This module contains a client and other classes which make working with the
Google Documents List Data API and the Google Spreadsheets Data API look a
bit more like working with a heirarchical database. Using the DatabaseClient,
you can create or find spreadsheets and use them like a database, with
worksheets representing tables and rows representing records.

Just what I needed! So I ported it to .net, and here is the result. The biggest difference with the python version is that tables in .net are strongly typed. When you define a Table<Entity>, the Entity's public properties are serialized when posting to the spreadsheet.

The sample app is pretty much self-explanatory. You can do CRUD operations on the rows stored on a worksheet. The structured query operators are limited, but I didn't need more. I even threw in a LINQ provider (base classes courtesy of Matt Warren), so you can do strongly typed queries:

class Entity {
  public int Amount { get; set; }
Table<Entity> t = ...
var rows = from e in t.AsQueryable()
           where e.Amount == 5
           select e;

It's still a bit rough around the edges, but usable.

UPDATE 11/03/2010: released GDataDB 0.2

Basic Quartz.Net-Windsor integration

A couple of months or so ago, I had to code some periodic maintenance jobs to be run in-process. So I was faced with the same options that Ayende had a year ago (except the out-of-process options). I finally decided on using Quartz.NET, since I needed the dynamic update capabilities (that is, it watches the config file for changes and re-schedules accordingly) which Castle.Components.Scheduler doesn't have yet as far as I know... (BTW Castle.Components.Scheduler is now part of the trunk).

So I set to code the wrappers needed to make it play with Windsor, and the resulting code is here. There's a sample app that shows how to set up job and trigger listeners (both global and job-specific). The actual scheduling configuration is managed by Quartz.NET of course. In the sample I used the external quartz_jobs.xml config with the default RAMJobStore, but you could easily change that to a ADOJobStore or anything, just by setting the props dictionary on the QuartzNetScheduler component, that dictionary is passed as-is to Quartz.NET.

Why did I write "basic" integration on the title? Well, I just needed the basic features of Quartz.NET, so I didn't even try to integrate stuff like clustering, remote servers, etc. I have no idea if those will work with my code. If anyone gives it a try, I'd love to hear about it :-)

UPDATE 4/3/2009: I wrapped the components in a facility for easier usage and configuration.