Google Spreadsheets for iPhone

Did you ever wanted to write a smart App with a cloud-based backend? - Recently I stumbled over the option simply to use a Google Spreadsheet as a data container - at least you have a reasonably stable server and an unbeatably easy maintenance interface. And as I discovered - an API that, despite some lack of documentation, is a fun to use!

Installation

Like all Google Data APIs, the spreadsheet API is a set of well-documented, REST-like, JSON-based services. Simple enough to use. But to be honest - I don't want to bother with JSON objects and REST calls when developing in Objective C for iOS (nor in Java for android).
So - as always, I must say - I was pleased to find a well-maintained iOS library for the Google Data Services called GData Objective C Client. Installation of the library is explained very well here, but does not differ much from other libraries:

• Checkout from the git repository
• Drag the framework or the project itself into your project
• Add the static library as a dependendy
• include the header files in the search path

Authentication

GData Objective C Client provides services for each Google service as the central point of interaction. In order to use them, include the header file:

#import "GData.h"

Then, for spreadsheets, instantiate the service:

_srv = [[GDataServiceGoogleSpreadsheet alloc] init];
[_srv setShouldCacheResponseData:YES];
[_srv setServiceShouldFollowNextLinks:YES];
[_srv setUserCredentialsWithUsername:username
                            password:password];

As you can see, here we send specific user credentials. This approach is not optimal since they have to be stored locally by your App. Google favours OAuth and even considers the explicit approach deprecated. Maybe in a later post, I will discuss how to use OAuth.

Where are my spreadsheets?

Having a service at hand, we can now start querying our document repository for spreadsheets:

NSString *serviceRootURL = [GDataServiceGoogleSpreadsheet serviceRootURLString];
NSString *sheetsQuery = @"spreadsheets/private/full";
NSString *query = [serviceRootURL stringByAppendingString:sheetsQuery];
NSURL *feedURL = [NSURL URLWithString:query];
SheetFeedDelegate *wrapper = [[SheetFeedDelegate alloc] initWithDelegate:delegate];
[_srv fetchFeedWithURL:feedURL
              delegate:wrapper
     didFinishSelector:@selector(ticket:finishedWithFeed:error:)];

As you can see, the google data services work feed-based: You request a specific URL and asynchroneously receive the feed as a result. Thus we first have to construct the URL from a service base URL an a query part, "spreadsheets/private/full". "spreadsheets" denotes what you are asking for, "private" means send me all spreadsheets available for the account  I'm authenticated for ("public" would show you only public sheets). "full", for the spreadsheet feed is the usual option (on cell feeds, "values" or "basic" would only give you values, not formulas and leave you in a readonly state).

Since the receipt of the result happens asynchroneously, you have to provide a delegate, on which a given selector is applied (you could also use Objective-C blocks if you prefer them):

- (void)ticket:(GDataServiceTicket *)ticket
  finishedWithFeed:(GDataFeedSpreadsheet *)feed
             error:(NSError *)error {
    if (!error) {
        NSMutableArray *result = [[NSMutableArray alloc] init];
        for (GDataEntrySpreadsheet *entry in [feed entries]) {
            GDataTextConstruct *title = [entry title];
            Sheet *sheet = [[[Sheet alloc] 
                            initWithTitle:[title stringValue]
                            worksheetsFeed:[entry worksheetsFeedURL]] 
                            autorelease];
            [result addObject:sheet];
        }
        [_delegate foundSheets:result];
    } else {
        NSLog(@"Error on load");
    }
}

Here, feed is the full result.  In this example, a list of Sheet objects is created to hold the result. Their definition is not interesting, but pay attention on how the title of the spreadsheet is returned via a GDataTextConstruct. Moreover, you see the worksheetsFeedURL being stored in the temporary structure. This is a general pattern: Items returned by the Google services often contain URLs to other feeds enabling further related queries and even modifications without further manual URL construction. It is often advisable to store these URLs locally for further use.

Get Worksheets

Having the worksheets feed available, querying the spreadsheed for its worksheets is already a well-known operation:

[_srv fetchFeedWithURL:sheet.worksheetsFeed
              delegate:wrapper
     didFinishSelector:@selector(ticket:finishedWithFeed:error:)];

With a delegate that does again:

- (void)ticket:(GDataServiceTicket *)ticket
finishedWithFeed:(GDataFeedSpreadsheet *)feed
           error:(NSError *)error {
    if (!error) {
        NSMutableArray *result = [[NSMutableArray alloc] init];
        for (GDataEntryWorksheet *entry in [feed entries]) {
            GDataTextConstruct *title = [entry title];
            Worksheet *worksheet = [[[Worksheet alloc] 
                                    initWithSheet:_sheet 
                                    listFeed:[[entry cellsLink] URL]
                                    title:[title stringValue]]
                                    autorelease];
            [result addObject:worksheet];
        }
        [_delegate foundWorksheets:result];
    } else {
        NSLog(@"Error on load");
    }
}

In this code, you see only one single change: From the GDataEntryWorksheet, we do not store the listFeedURL as we did for the worksheet feed, but we use the cellsLink. See the next section for a discussion of the difference.

Get Cells

When it comes to query for actual worksheet cells, you have the choice: Either you use a list-based or a cell-based feed.

• The list-based feed works best when you worksheet can be seen just like a database table: With defined column and headers in the first row. For each subsequent row, you will get a set of column values. Sounds familiar.
• The cell-based feed is a more general approach and gives you the freedom to query any row-column-area of your worksheet.

Since we already coped with list feeds let's use a cell-based feed now. Therefore we stored the cellsLink above which contains the URL of a cell-based feed but by default is queried exactly like a list-based feed (though you could also use eg fetchFeedWithQuery to make more concrete requests):

[_srv fetchFeedWithURL:worksheet.listFeed
              delegate:wrapper
     didFinishSelector:@selector(ticket:finishedWithFeed:error:)];

Lets take a look at the delegate again:

- (void)ticket:(GDataServiceTicket *)ticket
finishedWithFeed:(GDataFeedSpreadsheetCell *)feed
         error:(NSError *)error {
    if (!error) {
        NSInteger rows = [feed rowCount];
        NSInteger cols = [feed columnCount];
        NSMutableArray *result = [[NSMutableArray alloc] init];
        for (int i=0; i<rows; i++) {
            Row *row = [[Row alloc] initWithWorksheet:_worksheet columnCount:cols];
            [result addObject:row];
        }
        for (GDataEntrySpreadsheetCell *entry in [feed entries]) {
            GDataSpreadsheetCell *cell = [entry cell];
            NSInteger rowNo = [cell row]-1;
            NSInteger colNo = [cell column]-1;
            [self setEntry:entry forRow:rowNo forColumn:colNo];
        }

        [_delegate foundList:result];
    } else {
        NSLog(@"Error on load");
    }
}

As you of course have noticed, we did not extract actual cell values until now. We only build an a row/column-based index of objects of type GDataEntrySpreadsheetCell. What if you want to know the actual value of a cell?

-(NSString*) getValueForRow:(int)row forColumn:(int)column {
    GDataEntrySpreadsheetCell *entry = [self getEntryForRow:row forColumn:column];
    GDataSpreadsheetCell *cell = [entry cell];
    return [cell resultString];
}

So... why this indirection? The GDataEntrySpreadsheetCell is quite a huge structure so the ideal solution would be stored the string value only. But then, updating cells would get a little trickier, as I'll explain in the next section:

Update Cells

Updating a cell is more or less trivial when you have the entry at hand:

[_srv fetchEntryByUpdatingEntry:entry
                           delegate:delegate
                  didFinishSelector:@selector(ticket:finishedWithFeed:error:)];

where the delegate is simply implemented like:

- (void)ticket:(GDataServiceTicket *)ticket
finishedWithFeed:(GDataEntrySpreadsheetCell *)entry
         error:(NSError *)error {
    [self setEntry:entry forColumn:_column];
}

Huh? - Yes, you have seen this line of code earlier, when we retrieved cell values from the server. It is important to store the new entry object sent as a response to your update request. If you don't, upon sending the next update request, Google will detect an intermediate change and send you a corresponding error.

So updating a cell value is easy if you store the corresponding entry object. For less memory footprint though, you can store only the value and re-create the entry from scratch. The only thing you have to do is also to store the so-called etag from the entry and assign it to the new entry so that google again can check for intermediate changes.

These are the simpliest operations available via the Google Data API for Spreadsheets. From this starting point, the design of advanced apps using spreadsheets should be possible.