Google Spreadsheets and Ruby
Time to read: 3 minutes
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.
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:
- Go to the Google APIs Console.
- Create a new project.
- Click Enable API. Search for and enable the Google Drive API.
- Create credentials for a Web Server to access Application Data.
- Name the service account and grant it a Project Role of Editor.
- Download the JSON file.
- 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:
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:
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.
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.
To update a cell you can reference the cell by row and column number:
You can also use the cell name:
Deleting rows is like inserting rows, you need to provide the row number and how many rows you’d like to delete:
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.
Related Posts
Related Resources
Twilio Docs
From APIs to SDKs to sample apps
API reference documentation, SDKs, helper libraries, quickstarts, and tutorials for your language and platform.
Resource Center
The latest ebooks, industry reports, and webinars
Learn from customer engagement experts to improve your own communication.
Ahoy
Twilio's developer community hub
Best practices, code samples, and inspiration to build communications and digital engagement experiences.