Wrangling Google Services in Ruby

A Quickstart Guide to Getting Programmatic with Google

Published in

Building RigUp

7 min readJan 6, 2020

One of the more surprising things I’ve discovered while writing software for a living is how much of the business world still runs on spreadsheets.

Maybe this wasn’t shocking to you, but I can still taste the betrayal I first felt when I learned that the “scheduling algorithm” I’d been newly tasked to maintain was, in actuality, a vast network of admin assistants backed by an incredibly hacky and brittle Excel macro. Alas…

Things are predictably more correct at a unicorn tech company like RigUp, with its ever-doubling army of engineers; but I do, on occasion, still find myself asking questions like, “We’re using Google Forms for… that? Seriously?!”

C’est la vie. Sometimes you just have to make peace with what gets the job done and embrace the middle way — and by “middle way”, I mean getting programmatic with Google services.

Accessing Google APIs with Ruby

Google has provided excellent, well-documented Ruby APIs for pretty much any service with which you’d want to interact.

But authorizing your Ruby scripts to interact with Google can be a little tricky and hard to remember. Below is one workflow — and a sample Ruby module! — I’ve pieced together from two excellent posts on related topics, here and here. For this approach, we will:

Create/select a Gmail user who has access to the resources with which we want to interact.

2. Create an “application” to represent our script, acting on behalf of that user.

3. Authorize this “application” using Google’s OAuth Playground and keep this authorization alive using a long-lasting refresh token.

4. Create a Ruby module that tidily abstracts all this away (yay, Ruby!), and use it to interact with the Google sheets API.

But first, a caveat.

There are several ways to grant your scripts programmatic access to Google services. The approach I take, below, is suitable for giving a Ruby script the same level of access as your email account owner. This is the use case I most-often apply in our codebase.

However, it’s occasionally useful to use a service account for interacting with more strictly-permissioned resources to which your representative user won’t have (or want) access— like that incredibly high-stakes spreadsheet your organization is (regrettably) still using in place of a proper database… but I digress! We’ll save service-account-type access for a future post.

Let’s do this.

1. Set Up the User

Get yourself a Gmail account.

Really, any Gmail account will suffice for this how-to guide, though if you’re planning to interact with your organization’s G-Suite resources, you’ll want to use an official account. (Recall: the resulting access depends entirely on what the owner of this email account can access.)

At RigUp, for example, we tend to have dedicated email accounts specifically for these purposes.

2. Set Up A Web App To Represent Your Script

Get yourself an API console project.

Freshly logged into the Gmail account from above, navigate to Google’s API Console and create a new project (or just use your one of your organization’s existing projects, if appropriate).

You’ll create a web app within this project that will be interact with Google services on behalf of your Gmail user. This app will represent your Ruby scripts.

Create a web app and a new set of credentials.

Within your project, navigate to the Credentials tab and select Create Credentials. We’ll be using OAuth type credentials.

Depending on whether you’re using a personal (free) gmail or a G-Suite account with already fleshed-out APIs, you may may need to create a new web app to which to assign credentials. In either case, defaults will work fine here.

Just make sure to include https://developers.google.com/oauthplayground/ as a valid redirect URI. Download the newly created credentials as JSON to your local machine.

Really read that last paragraph again. It’s they key to all of this.

3. Use OAuth Playground to Authorize this Web App

OAuth Playground provides the magic to this whole approach. Basically, you’ll use it to (1) authorize your new web app to access resources on your behalf; and (2) get a refresh token you’ll need to keep this authorization alive.

Yet another caveat…

OAuth is really important in the big world of web development. For the sake of brevity, I’m going to gloss over the details. If you’re not already familiar with OAuth, should definitely read up on the whole cycle.

Briefly, OAuth is a way to authorize some application (your Ruby script) to access a resource (like Google sheets) on behalf of some user (your Gmail user). The flow goes something like this:

(1) the Ruby script tries to access Google sheets;
(2) Google sheets redirects it to login as a user with that permission (your gmail user);
(3) upon login, your script gets redirected (again) to your “valid redirect URI” with a newly obtained authorization code in hand.
Your script can then (4) exchange this code for a token from Google sheets that it can use to
(5) obtain a session from Google sheets that it can use to access the sheets on behalf of your user. (Exhausting, I know…)
And finally, if you’re lucky, your script can (6) get a refresh token that will allow you to skip steps (1) — (4) in the future and go straight to getting the session!

Fortunately for us, OAuth Playground lets us blaze through all the steps above to get our refresh token. (Hooray!)

Configure OAuth Playground to authorize your web app.

Navigate to OAuth Playground and set the following presets (using the Wgear icon):

OAuth flow: server-side
Access type: offline
Use your own OAuth credentials: check
Enter the Client Id and Client Secret from step you obtained earlier.

Here, you’re telling OAuth Playground that you want to give your web app the same permissions as your Gmail user.

Authorize your web app to access the desired services (scopes).

Now, in Step 1 of OAuth playground’s flow, select the service for which you want to authorize your web app— Google refers to this as the “scopes” of the authorization. You can select as many as you like (go nuts!). For this how-to, I’ll select Google Sheets, or https://www.googleapis.com/auth/spreadsheets.

Pro-tip: You can use CMD-F on Mac (CTRL-F on Windows) to find scopes instead of scrolling forever.

Still in Step 1, click Authorize APIs and you’ll be prompted to login as your Gmail user. This is you saying, “I’m about to grant all my user’s permissions to….”. This takes care of (1) and (2) in the OAuth flow.

Exchange your authorization code for a refresh token.

You’ll proceed to Step 2 in the playground, where you’ll get an authorization code that you’ll want to exchange for a token — conveniently, by clicking Exchange authorization code for tokens, or (3) and (4) of the flow, above.

Copy the refresh token (hooray!) which we’ll use to create sessions for interacting with Google sheets on behalf of our Gmail.

4. Our Hard-Won Google Credentials Module

The above is all you’ll need to give a Ruby script programmatic access to Google sheets (on behalf of your Gmail user).

Write you a GoogleCredentials module.

Here’s a module that makes newly authorized credentials available through the credentials method.

require 'googleauth'

module GoogleCredentials
  CLIENT_ID = ENV['GOOGLE_CLIENT_ID']
  CLIENT_SECRET = ENV['GOOGLE_CLIENT_SECRET']
  REFRESH_TOKEN = ENV['GOOGLE_REFRESH_TOKEN']
  SCOPES = [
    'https://www.googleapis.com/auth/spreadsheets',
  ].freeze

  def make_credentials!
    creds = Google::Auth::UserRefreshCredentials.new(credentials_config)
    creds.refresh_token = REFRESH_TOKEN
    creds.fetch_access_token!
    creds
  end  def credentials_config
    {
      client_id: CLIENT_ID,
      client_secret: CLIENT_SECRET,
      scope: SCOPES,
      additional_parameters: { 'access_type' => 'offline' },
    }
  end
end

For the above module to function you need to install the googleauth gem; and stash your GOOGLE_CLIENT_ID, GOOGLE_CLIENT_SECRET, and GOOGLE_REFRESH_TOKEN in thusly-named environment variables.

Use your GoogleCredentials module to interact with Google Sheets!

Now use the above to create a(n admittedly very basic) GoogleSheetsService, which depends on the google-api-client gem.

require "google/apis/sheets_v4"
require "google_credentials" # We made this.class GoogleSheetsService
  include GoogleCredentials
  attr_reader :service  def self.service
    new.service
  end  private  def initialize
    @service = freshly_authorized_service
  end  def freshly_authorized_service
    service = Google::Apis::SheetsV4::SheetsService.new
    service.authorization = make_credentials!
    service
  endend

And, finally, here’s some example code (from Google Sheets API Ruby docs) that utilizes our new module. (Note how much cleaner it is than the official example….)

require "google_sheets_service" # We wrote this# Prints the names and majors of students in a sample spreadsheet: https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/editSPREADSHEET_ID = '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms'
RANGE = 'Class Data!A2:E'service = GoogleSheetsService.service # SO EASY!
response = service.get_spreadsheet_values spreadsheet_id, range
puts "Name, Major:"
puts "No data found." if response.values.empty?
response.values.each do |row|
  # Print columns A and E, which correspond to indices 0 and 4.
  puts "#{row[0]}, #{row[4]}"
end

Getting Back to Business

At RigUp, we’ve used code like the above to track and process thousands of service professionals in Google spreadsheets or to parse hundreds of uploaded resumes in Google Drive. The opportunities for new hacky integrations are are quite literally endless.

Congratulations, adventurer! You’re now equipped to tie your secure, version-controlled application code into the thorny world of Google Sheets, Docs, Forms, etc.

May you migrate your stakeholders off of Google Sheets gently but swiftly.