Google Spreadsheets and Ruby
Tags: ruby, googlespreadsheets, database
Published: 2017-03-15 10:45:16

---
title: Google Spreadsheets and Ruby
published: true
description: Read, update and delete data from Google Spreadsheets with Ruby.
cover_image: https://www.twilio.com/blogwp-content/uploads/2017/03/devto_header.png
tags: ruby, google spreadsheets, database
canonical_url: https://www.twilio.com/blog/2017/03/google-spreadsheets-ruby.html
---

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 <a href="http://gimite.net/en/">Hiroshi Ichikawa's</a> <a href="https://github.com/gimite/google-drive-ruby">`google_drive` Ruby gem</a> 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 <em>copy</em> of this <a href="https://docs.google.com/spreadsheets/d/1KK432FFgQ18lP-juSq10ae1ZX1lfa7FTELQFY9bDC38/edit#gid=152787366">spreadsheet of contact information for US legislators</a>. (That's a useful set of data if you want to <a href="https://www.twilio.com/blog/2016/06/call-congress-using-one-phone-number.html">make it easy to call Congress like Ian Webster</a>.)

<img src="https://twilioinc.wpengine.com/wp-content/uploads/2017/02/Screen-Shot-2017-02-16-at-11.48.00-AM-300x247.png" alt="You can make a copy of a spreadsheet by selecting the file menu then choosing 'Make a copy...'" width="300" height="247" />

Create a directory to build this project in.

```bash
$ 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 <a href="https://console.developers.google.com/">Google APIs Console</a>.
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

<img class="aligncenter size-full wp-image-19613" src="https://twilioinc.wpengine.com/wp-content/uploads/2017/02/google-developer-console.gif" alt="" width="600" height="375" />

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:

```ruby
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:

```ruby
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.

<img class="aligncenter" src="https://twilioinc.wpengine.com/wp-content/uploads/2017/03/avtYQbIgC2F7TeRjE5_kyExulALckAcirCSp_dcNDoG2IX6hVHz520gft83vBxJGL3S_F35r5lWAv6_2ckV5vUt8yQ9NrLV1J5-QOn-hJtXcDWO2ifzwtc19dPYDAYRGBvMUNTc.png" alt="A terminal window showing the results of running the above script. The first six columns of each row are printed." />

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.

```ruby
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.

```ruby
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:

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

You can also use the cell name:

```ruby
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:

```ruby
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 <a href="http://www.rubydoc.info/gems/google_drive/2.1.2/GoogleDrive/Worksheet">`Worksheet` class documentation</a> 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](https://www.twilio.com/blog/2017/03/create-a-landing-page-with-sinatra-google-spreadsheets-and-ruby.html).

Let me know if you've built anything interesting with Google Spreadsheets and Ruby. You can reach me at <a href="mailto:philnash@twilio.com">philnash@twilio.com</a> or on Twitter at <a href="https://twitter.com/philnash">@philnash</a>.

___

_[Google Spreadsheets and Ruby](https://www.twilio.com/blog/2017/03/google-spreadsheets-ruby.html) was originally published on the [Twilio Blog](https://www.twilio.com/blog/) on March 14, 2017._