A simple gem for interacting with GSheets using its v4 API. Originally created due to google-drive-ruby lacking v4 support, which gimite has since fixed.

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
• Getting started
• GitHub
• API docs

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.

UPDATE 5-19-2019:

GoogleSheets.strip_all_cells

is now a thing - it will #strip all of the cells returned from the sheet if set to true.

You can set it in an initializer, eg in config/initializers/google_sheets.rb:

GoogleSheets.strip_all_cells = true