· ruby

Ruby: Create and share Google Drive Spreadsheet

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 Ihttp://www.markhneedham.com/blog/2014/08/17/ruby-google-drive-errorbadauthentication-googledriveauthenticationerror-infoinvalidsecondfactor/[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'
OAUTH_SCOPE = 'https://www.googleapis.com/auth/drive https://docs.google.com/feeds/ https://docs.googleusercontent.com/ https://spreadsheets.google.com/feeds/'
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::APIClient.new
  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 = drive.permissions.insert.request_schema.new({
      'value' => "some_other_email@gmail.com",
      'type' => "user",
      'role' => "reader"

  result = client.execute(
    :api_method => drive.permissions.insert,
    :body_object => new_permission,
    :parameters => { 'fileId' => file_id })

  if result.status == 200
    p result.data
    puts "An error occurred: #{result.data['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.

  • LinkedIn
  • Tumblr
  • Reddit
  • Google+
  • Pinterest
  • Pocket