Mark Needham

Thoughts on Software Development

Ruby: Create and share Google Drive Spreadsheet

without comments

Over the weekend I’ve been trying to write some code to help me create and share a Google Drive spreadsheet and for the first bit I started out with the Google Drive gem.

This worked reasonably well but that gem doesn’t have an API for changing the permissions on a document so I ended up using the google-api-client gem for that bit.

This tutorial provides a good quick start for getting up and running but it still has a manual step to copy/paste the ‘OAuth token’ which I wanted to get rid of.

The first step is to create a project via the Google Developers Console. Once the project is created, click through to it and then click on ‘credentials’ on the left menu. Click on the “Create new Client ID” button to create the project credentials.

You should see something like this on the right hand side of the screen:

2014 08 17 16 29 39

These are the credentials that we’ll use in our code.

Since I now have two libraries I need to satisfy the OAuth credentials for both, preferably without getting the user to go through the process twice.

After a bit of trial and error I realised that it was easier to get the google-api-client to handle authentication and just pass in the token to the google-drive code.

I wrote the following code using Sinatra to handle the OAuth authorisation with Google:

require 'sinatra'
require 'json'
require "google_drive"
require 'google/api_client'
CLIENT_ID = 'my client id'
CLIENT_SECRET = 'my client secret'
REDIRECT_URI = 'http://localhost:9393/oauth2callback'
helpers do
  def partial (template, locals = {})
    haml(template, :layout => false, :locals => locals)
enable :sessions
get '/' do
  haml :index
configure do
  google_client =
  google_client.authorization.client_id = CLIENT_ID
  google_client.authorization.client_secret = CLIENT_SECRET
  google_client.authorization.scope = OAUTH_SCOPE
  google_client.authorization.redirect_uri = REDIRECT_URI
  set :google_client, google_client
  set :google_client_driver, google_client.discovered_api('drive', 'v2')
post '/login/' do
  client = settings.google_client
  redirect client.authorization.authorization_uri
get '/oauth2callback' do
  authorization_code = params['code']
  client = settings.google_client
  client.authorization.code = authorization_code
  oauth_token = client.authorization.access_token
  session[:oauth_token] = oauth_token
  redirect '/'

And this is the code for the index page:

    %title Google Docs Spreadsheet
        Create Google Docs Spreadsheet
        - unless session['oauth_token']
          %form{:name => "spreadsheet", :id => "spreadsheet", :action => "/login/", :method => "post", :enctype => "text/plain"}
            %input{:type => "submit", :value => "Authorise Google Account", :class => "button"}
        - else
          %form{:name => "spreadsheet", :id => "spreadsheet", :action => "/spreadsheet/", :method => "post", :enctype => "text/plain"}
            %input{:type => "submit", :value => "Create Spreadsheet", :class => "button"}

We initialise the Google API client inside the ‘configure’ block before each request gets handled and then from ‘/’ the user can click a button which does a POST request to ‘/login/’.

‘/login/’ redirects us to the OAuth authorisation URI where we select the Google account we want to use and login if necessary. We’ll then get redirected back to ‘/oauth2callback’ where we extract the authorisation code and then get an authorisation token.

We’ll store that token in the session so that we can use it later on.

Now we need to create the spreadsheet and share that document with someone else:

post '/spreadsheet/' do
  client = settings.google_client
  if session[:oauth_token]
    client.authorization.access_token = session[:oauth_token]
  google_drive_session = GoogleDrive.login_with_oauth(session[:oauth_token])
  spreadsheet = google_drive_session.create_spreadsheet(title = "foobar")
  ws = spreadsheet.worksheets[0]
  ws[2, 1] = "foo"
  ws[2, 2] = "bar"
  file_id = ws.worksheet_feed_url.split("/")[-4]
  drive = settings.google_client_driver
  new_permission ={
      'value' => "",
      'type' => "user",
      'role' => "reader"
  result = client.execute(
    :api_method => drive.permissions.insert,
    :body_object => new_permission,
    :parameters => { 'fileId' => file_id })
  if result.status == 200
    puts "An error occurred: #{['error']['message']}"
  "spreadsheet created and shared"

Here we create a spreadsheet with some arbitrary values using the google-drive gem before granting permission to a different email address than the one which owns it. I’ve given that other user read permission on the document.

One other thing to keep in mind is which ‘scopes’ the OAuth authentication is for. If you authenticate for one URI and then try to do something against another one you’ll get a ‘Token invalid – AuthSub token has wrong scope‘ error.

Be Sociable, Share!

Written by Mark Needham

August 17th, 2014 at 9:42 pm

Posted in Ruby

Tagged with