Connecting Ruby to a Google Spreadsheet With a Service Account

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.


  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.

Creating a Service Account in the Google Developer Console

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:'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 =    .drop(1)    .map{|row| Hash[]}

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.


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: