Using OAuth with Spreadsheets API on Django/AppEngine

In a previous blog post, I showed how to import a published spreadsheet feed into an App Engine datastore by just grabbing the JSON. For another project I'm working on, I need to be able to import a *private* spreadsheet into an App Engine datastore. Because of the need to authenticate the user (via the multiple steps of the ever-so-elegant OAuth dance), this importing requires much more finagling.

With the help of my trusty colleague Vic Fryzel, I've put together a set of Django views that use the Python GData Client Library and should run on both App Engine Django, and with some modification for token storage, other Django stacks. I'll walkthrough the views here.

There are four URL handlers required, two for token requests, one for actually importing the spreadsheet, and one to manage the flow:

urlpatterns = patterns('',  
  (r'^get_oauth_token', 'importer.views.get_oauth_token'),
  (r'^get_access_token', 'importer.views.get_access_token'),
  (r'^import_spreadsheet', 'importer.views.import_spreadsheet'),
  (r'^$', 'importer.views.main_page'),
) 

When the user visits the main page, they are asked to login so that the app can remember their auth tokens, and if they are already logged in, they are redirected to the first token handler:

def main_page(request):  
  if not users.get_current_user():
    return HttpResponseRedirect(users.create_login_url(request.build_absolute_uri()))

  access_token = gdata.gauth.AeLoad(ACCESS_TOKEN)
  if not isinstance(access_token, gdata.gauth.OAuthHmacToken):
    return HttpResponseRedirect('/importer/get_oauth_token') 

In this first step of the OAuth dance, the app requests an oauth token for the specified scope (Spreadsheets), key/secret (anonymous, as I haven't registered my app), and a callback URL to my app. It saves that token to the App Engine datastore using a convenience function in the client library. Then it redirects the user to the authorization URL for that token, and the user is presented with the "Grant access" screen.

def get_oauth_token(request):
    oauth_callback_url = 'http://%s:%s/importer/get_access_token' %
        (request.META.get('SERVER_NAME'), request.META.get('SERVER_PORT'))
    request_token = client.GetOAuthToken(SCOPES, oauth_callback_url,
        CONSUMER_KEY, consumer_secret=CONSUMER_SECRET)
    gdata.gauth.AeSave(request_token, REQUEST_TOKEN)

    authorization_url = request_token.generate_authorization_url()
   return HttpResponseRedirect(authorization_url) 

When the user returns from the authorization screen to the callback handler, the app retrieves the original token, asks Google to upgrade that to an access token, and saves the access token to the App Engine datastore again.

def get_access_token(request):  
  saved_request_token = gdata.gauth.AeLoad(REQUEST_TOKEN)
  request_token = gdata.gauth.AuthorizeRequestToken(saved_request_token,
      request.build_absolute_uri())
  access_token = client.GetAccessToken(request_token)
  gdata.gauth.AeSave(access_token, ACCESS_TOKEN)
  return HttpResponseRedirect('/importer/') 

The user is then redirected to the main page, and since it sees that there is now an access token for the user, it shows the user an input box for providing a spreadsheets URL. When it knows the spreadsheets URL, it retrieves the list feed for that spreadsheet and saves each row as an entity in the datastore.

def import_spreadsheet(request): 
  import re
  import models

  client.auth_token = gdata.gauth.AeLoad(ACCESS_TOKEN)

  spreadsheet = request.GET.get('spreadsheet')
  if spreadsheet.find('google.com') > -1:
    spreadsheet_key = re.search('key=([^(?|&)]*)', spreadsheet).group(1)
  else:
    spreadsheet_key = spreadsheet
  worksheet_id = 'od6'
  list_feed = 'https://spreadsheets.google.com/feeds/list/%s/%s/private/values' %
      (spreadsheet_key, worksheet_id)
  feed = client.get_feed(list_feed,
                         desired_class=gdata.spreadsheets.data.ListsFeed)
  for row in feed.entry:
    firstname = row.get_value('firstname')
    lastname = row.get_value('lastname')
    email = row.get_value('email')
    person = models.Person(firstname=firstname, lastname=lastname, email=email)
    person.save()

  return HttpResponse('Saved %s rows' % str(len(feed.entry))) 

Using that code, the end result is going from this spreadsheet...

... to these datastore entities:

To see the full code (with inline comments), check it out from my repository or download the zip.

For simplicity's sake, this sample shows the simplest possible import. In my actual project, I am also creating an entity that represents the entire spreadsheet, and the entity for each row refer to that entity. In addition, I have code to convert from the spreadsheets strings to other model types like dates.

Hopefully this project can serve as a basis for other developers using spreadsheets as an import source for their apps. Enjoy!