Skip to content

Instantly share code, notes, and snippets.

@johnallen3d
Last active November 7, 2019 00:28
Show Gist options
  • Save johnallen3d/793cfa84d9db7d04f4c44b07202ee8e6 to your computer and use it in GitHub Desktop.
Save johnallen3d/793cfa84d9db7d04f4c44b07202ee8e6 to your computer and use it in GitHub Desktop.
Convert a Google Sheet into a Ruby Array of Hashes

Convert a Google Sheet into a Ruby Array of Hashes

Example

id first_name last_name
1 Trey Anastasio
2 Mike Gordon

becomes

[
  {
    'id': 1,
    'first_name': 'Trey',
    'last_name': 'Anastasio'
  },
  {
    'id': 2,
    'first_name': 'Mike',
    'last_name': 'Gordon'
  }
]

Authentication

Set the following environment variables:

export GOOGLE_ACCOUNT_TYPE=service_account
export GOOGLE_CLIENT_EMAIL=...
export GOOGLE_PRIVATE_KEY=...

See google-auth-library-ruby for additional detail.

source 'https://rubygems.org'
gem 'google-api-client'
require 'google/apis/sheets_v4'
require 'googleauth'
authorization = Google::Auth::ServiceAccountCredentials.make_creds(
scope: ['https://www.googleapis.com/auth/drive']
)
service = Google::Apis::SheetsV4::SheetsService.new.tap do |service|
service.authorization = authorization
end
keys, *values = service.get_spreadsheet_values(
'spreadsheet_id',
'A:ZZ'
).values
rows = values.map { |row| Hash[keys.zip(row)] }
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment