Connecting Ruby to a Google Spreadsheet With a Service Account

Jon Fuller
May 9, 2017

Ever wanted to connect your ruby app to a Google Spreadsheet? Yeah. Me too.

I find myself writing ruby scripts and console apps all the time. Many times needing a simple way to store or pull data. Databases are good at that (storing data). But, sometimes a spreadsheet is a more natural place to put that data. AND that helps me with writing less software.

I know this might sound weird. A programmer friend asked me why not keep everything in code/database. A manager friend asked me why I can’t do everything in the spreadsheet. In this case, I want the spreadsheet to be the source of truth. It’s easy to do CRUD there. I want to expose that data to other folks in interesting ways (not a spreadsheet).

Getting started – Opening access to the spreadsheet

I have wanted to do this for ages, but have always shied away from it. I knew the authentication and authorization bits were going to be a real pain. They were. OAuth 2 usually is. This post from Twilio, did a great job getting me going. The first section shows how to create credentials that can used by a console app. Perfect.

THIS SECTION IS LIFTED DIRECTLY FROM THE ABOVE TWILIO LINK, TO MAKE IT EASY FOR YOU. FULL ATTRIBUTION GOES TO THIS POST AT TWILIO.

  1. Go to the Google APIs Console.
  2. Create a new project.
  3. Click Enable API. Search for and enable the Google Drive API and the Google Sheets API.
  4. Create credentials for a Web Server to access Application Data.
  5. Name the service account and grant it a Project Role of Editor.
  6. Download the JSON file.
  7. Back to your spreadsheet. “Share” it with the email address corresponding to “client_email” in the JSON file.

Connecting with Ruby

So we’re clear, here’s what my environment looks like:

macos 10.12.4  ruby 2.4.0rc1  rubygems 2.6.8  bundler 1.14.6

Add ‘google-api-client’ to your Gemfile and run bundle install.

The rest sort of speaks for itself:

require 'google/apis/sheets_v4'require 'googleauth' # Connect to Googleservice = Google::Apis::SheetsV4::SheetsService.newservice.client_options.application_name = 'Some application name'service.authorization = Google::Auth::ServiceAccountCredentials.make_creds(    json_key_io: File.open('client_secret.json'),    scope: Google::Apis::SheetsV4::AUTH_SPREADSHEETS_READONLY) # Query the spreadsheet spreadsheet_id = 'biglongidlookingthingfromyourgooglesheeturl'range = 'characters!A1:E'response = service.get_spreadsheet_values(spreadsheet_id, range) # Use the first row as a header row, then turn each subsequent row into a hash using the header row values as keys header = response.values.first.map(&:to_s)response.values    .drop(1)    .map{|row| Hash[header.zip(row)]}

A couple things to note:

  • The client_secret.json is the one you downloaded from the Google Developer Console.
  • The spreadsheet_id is the big long id looking thing in the URL when you have a Google Sheet open in your browser. I’d recommend putting this in an environment variable.
  • The range is typical range syntax… Sheet Name first, then Column-Row information.

Caveats

I’m tempted to drop the client_secret.json into source control. But, I know better than to keep secrets in source control. After poking at the source, I found it’s possible to use environment variables instead. This allows us to avoid the secrets in source control altogether.

That changes the above make_creds call to be:

# Add these variables to your .env file (or actual environment variable)... the corresponding values are inside the client_secret.json fileGOOGLE_PRIVATE_KEYGOOGLE_CLIENT_EMAILservice.authorization = Google::Auth::ServiceAccountCredentials.make_creds(    scope: Google::Apis::SheetsV4::AUTH_SPREADSHEETS_READONLY)

Here are a few resources in case you want some more:

Build awesome things for fun.

Check out our current openings for your chance to make awesome things with creative, curious people.

Explore SEP Careers »

You Might Also Like

Headshot of Jon Fuller

Jon Fuller

Tags

OAuth Ruby Security