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
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