An Instant Admin Interface

I'm starting on a new site for a client that calls for a fairly significant amount of back end data. Unfortunately, the client's budget doesn't cover the creation of an admin area where she can enter this data in. My first thought was that I'd have her send me the data as a csv file and I'd cram it into the database myself. But, at about 2am last night as I was heading to bed, a novel solution hit me.

What if I had the customer add in the data on a Google Spreadsheet and then pulled the data dynamically into the system using the Google Spreadsheets API. What would make this a real win is that I could require the spreadsheet self describing, which would allow the code that I write to remain completely generic (and reusable). The result: anytime I want a quick and dirty way for customers to maintain data, I just wire a database table up to a Google Spreadsheet.

The Implementation

After a couple hours this morning, I had my generic synchronization procedure implemented. Here are the assumptions I make that drives the process:

• The worksheet tab name is the same name as the database table to sync with
• The column heading of the spreadsheet correspond to the column names of the table
• The first column must be a unique identifier into the table (though, not the primary key - that will just be an auto incrementing number)
• The database table must contain a column named expired. This will be set to a timestamp when an item is removed from the spreadsheet, and is set back to null when it's added in. That way, the spreadsheet can appear to delete records, but can't really.

Using the above approach, I'm able to write a fairly simple series of functions (about 55 lines of code) that does the synchronization. I then gave the customer a page she can visit to resync the data. Down the line, I suppose I could wire this into a cron job.

Why It's So Simple

One of the features that Google provides that makes this especially simple to implement is their list based view the spreadsheet data. The first time I heard about this I was confused - why would anyone want such a simplistic view of a spreadsheet, when you can access rows, columns and ranges? But, it turns out, for applications where you just need to slurp down data, it's a huge win.

Specifically, using list view you can easily get an array of spreadsheet rows, where each row object has a map that contains column, value pairs. Or, to put this in code form you can say:

// See the <A href='http://framework.zend.com/manual/en/zend.gdata.spreadsheets.html'>Zend API</a>
$query = new Zend_Gdata_Spreadsheets_ListQuery();
$query->setSpreadsheetKey($spreadsheetKey);
$query->setWorksheetId($worksheetId);
$rows = $spreadsheetService->getListFeed($query);
foreach($rows as $row) {
  $rowData = $row->getCustom();
  foreach($rowData as $col) {
    echo $col->getColumnName() . " = " . $col->getText();
  }
}

This makes the synchronization process as easy as iterating through this list of rows and converting each entry to either an INSERT or UPDATE statement.

The Pros and Cons

The big problem with this approach is that it's fragile. This is especially true if data in on tab of the spreadsheet is intended to join up with another one. There's no concept of referential integrity here. But, having the ability to allow the customer to tweak their own data is absolutely invaluable. So for now, I think the risk is worth the reward.

Google Spreadsheets in general offers some interesting features that also make it a powerful choice:

• The client can invite others (think: intern, assistant) to edit the spreadsheet, and therefore manage the data
• Users of the spreadsheet can add alerts that lets them be notified anytime the spreadsheet data changes
• There's version history spreadsheets, which means clients can see a past snapshots of their data and optionally roll back to one
• Data can be exported for backup or printing, and imported if the client prefers to manage the data in another tool

I was considering posting the code for this solution - but alas, it has to many dependencies on code I'm not ready to make available to the public. If you have specific questions about how I implemented, please feel free to ask in the comments and I'll be glad to respond.