Google Spreadsheets and Ruby

Sometimes you don't need a database. Maybe you're building a prototype or a simple internal application. Using a Google Spreadsheet can be quicker and easier to set up and get going with.

In this tutorial we'll use Hiroshi Ichikawa's google_drive Ruby gem to read, write and delete data from a Google Spreadsheet with just a few lines of code.

Accessing your Spreadsheet

First you'll need a spreadsheet to read from and write to. If you don't have your own spreadsheet, make a copy of this spreadsheet of contact information for US legislators. (That's a useful set of data if you want to make it easy to call Congress like Ian Webster.)

Create a directory to build this project in.

$ mkdir spreadsheet_fun
$ cd spreadsheet_fun

You will also need to get OAuth2 credentials to access the spreadsheet from Ruby. You can create a service account to do this with the following steps:

1. Go to the Google APIs Console.
2. Create a new project.
3. Click Enable API. Search for and enable the Google Drive 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. Copy the JSON file to your spreadsheet_fun directory and rename it to client_secret.json

Those are the credentials your application will need. They represent a user that can update spreadsheets on your behalf. We still need to give this user access to the spreadsheet we want to use though. Open client_secret.json and find and copy the client_email. In your spreadsheet click the "Share" button in the top right and paste the email, giving your service account edit rights.

That's all you need to do to authorise access to your spreadsheet. Let's get on with reading the data in Ruby.

Read data from a Google Spreadsheet with Ruby

Create a Gemfile in your spreadsheet_fun directory and paste the following into it:

source "https://rubygems.org"

gem "google_drive"

In the directory, run bundle install. Now we're ready to start interacting with our spreadsheet. Create a new file called spreadsheet.rb and copy in the following code:

require 'bundler'
Bundler.require

# Authenticate a session with your Service Account
session = GoogleDrive::Session.from_service_account_key("client_secret.json")

# Get the spreadsheet by its title
spreadsheet = session.spreadsheet_by_title("Copy of Legislators 2017")
# Get the first worksheet
worksheet = spreadsheet.worksheets.first
# Print out the first 6 columns of each row
worksheet.rows.each { |row| puts row.first(6).join(" | ") }

Run this with bundle exec ruby spreadsheet.rb and you'll see the data from the spreadsheet.

Now we've read data from it, let's see about editing the spreadsheet.

Insert, Update, and Delete from a Spreadsheet with Ruby

Now we have a reference to the worksheet, inserting a row of data is straightforward. The insert_rows method inserts new rows above the row number you supply. This will insert a single row above the first row of data.

worksheet.insert_rows(2, [["Hello!", "This", "was", "inserted", "via", "Ruby"]])
worksheet.save

You always need to save the worksheet to persist changes back to the spreadsheet.

If you want to insert a row at the bottom of all existing rows, use the num_rows method.

worksheet.insert_rows(worksheet.num_rows + 1, [["Hello!", "This", "was", "inserted", "at", "the", "bottom"]])
worksheet.save

To update a cell you can reference the cell by row and column number:

worksheet[2, 1] = "Updated!"
worksheet.save

You can also use the cell name:

worksheet["A2"] = "Updated again!"
worksheet.save

Deleting rows is like inserting rows, you need to provide the row number and how many rows you'd like to delete:

worksheet.delete_rows(2, 1)
worksheet.save

Create, read, update, delete, done

Now you've seen how to access and change data in a Google Spreadsheet using the google_drive gem. For more information on the methods available, do check out the Worksheet class documentation for the gem.

Next time you create a Sinatra application that needs some storage or just need to load and work with some data, a Google Spreadsheet might be a good fit. Don't forget, it's a great interface for non-technical users to view the data too. Here's an example of how to build a landing page with Google Spreadsheets and Sinatra.

Let me know if you've built anything interesting with Google Spreadsheets and Ruby. You can reach me at philnash@twilio.com or on Twitter at @philnash.

Google Spreadsheets and Ruby was originally published on the Twilio Blog on March 14, 2017.