Simple Ruby gem for CRUD Google Sheets operations (v4 api)
Switch branches/tags
Nothing to show
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Failed to load latest commit information.
lib
spec
.gitignore
.rspec
.yardopts
Gemfile
Gemfile.lock
README.md
google_sheets.gemspec

README.md

Yard Docs

google-drive-ruby, a great gem, doesn't support Google's v4 Drive API. As a result, I seem to encounter rate limiting errors fairly quickly.

Since I only ever used that gem for creating/reading spreadsheets, I created this simple gem for just that, but using the v4 API.

If you'd like changes or a new feature, please create an issue or PR - features will be developed on an as-needed basis.

Installing

Add this line to your application's Gemfile & bundle install:

gem 'google_sheets'

Or install it yourself:

$ gem install google_sheets

Authorization

The authorization process is taken from Google's own tutorial. Take a look at session.rb - it closely resembles the authorization code in that tutorial.

You'll need to create a project and enable the GSheets API, as detailed in step 1 of that tutorial.

You'll download a client_secret.json that will contain a client_id and client_secret

I recommend using Rails 5.2's encrypted credentials to store the id & secret. So the final will result will look something like:

client_id = Rails.application.credentials[:client_id]
client_secret = Rails.application.credentials[:client_secret]

session = GoogleSheets::Session.start_session(
  client_id: client_id,
  client_secret: client_secret
)

Or store them in an environment variable, EG: ENV['client_id']

This will prompt you to authorize the app in the browser. Once completed, you'll notice a token.yaml in your cwd. If you'd like the file to be placed elsewhere, there's a token_path parameter that you can pass into start_session, EG:

session = GoogleSheets::Session.start_session(
  client_id: client_id,
  client_secret: client_secret,
  token_path: './tmp'
)

Getting Started

Once you're authorized, you can create, read, update and delete sheets within a spreadsheet.

session = GoogleSheets::Session.start_session(
  client_id: ENV['test_client_id'],
  client_secret: ENV['test_client_secret']
)

spreadsheet = session.spreadsheet_from_key '[your spreadsheet key]'

spreadsheet.sheets.map &:title
# => ['Sheet1', 'yoyo1']

sheet1 = spreadsheet.sheets[0]

sheet1.values
# => [['first, 'last', 'age'], ['bob', 'jones', '92'], ['steve', 'johnson', '22']]

sheet2 = spreadsheet.add_sheet('what', values: [[1,2],[3,4]])

spreadsheet.sheets.map &:title
# => ['Sheet1', 'yoyo1', 'what']

# this will delete the sheet!!!
sheet2.delete!

spreadsheet.sheets.map &:title
# => ['Sheet1', 'yoyo1']

# Sheet#to_json converts the csv to a json array
# it uses the top row as the keys
sheet1_json = sheet1.to_json
# =>
#  [
#     {
#       first: 'bob',
#       last: 'jones',
#       age: '92'
#     },
#     {
#       first: 'steve',
#       last: 'johnson',
#       age: '22'
#     }
#  ]

sheet1_json[0][:first] = 'bobby'

# Sheet#set_values_from_json is the inverse of to_json
# accepts an array of hashes, turns it back to csv format
# sets that as the sheet's values
sheet1.set_values_from_json(sheet1_json)

sheet1.values[1][0] # => 'bobby'

# save the spreadsheet's values
sheet1.save!

Or just look at the spec to see it in action.