module SheetReader
Constants
- REQUIRED_ENV_VARS
- VERSION
Public Class Methods
read(sheet_id, sheet_name = "")
click to toggle source
Fetches the content of a google spreadsheet
Example:
>> SheetReader.read("1ukhJwquqRTgfX-G-nxV6AsAH726TOsKQpPJfpqNjWGg") => [{"foo"=>"hey", "bar"=>"ho"}, {"foo"=>"let's ", "bar"=>"go"}]
Arguments:
sheet_id: (String) The google sheet identifier. sheet_name: (String) The sheet name, by default it's the first one
# File lib/sheet_reader.rb, line 21 def self.read(sheet_id, sheet_name = "") raise MissingEnvVars unless required_env_vars? ensure_valid_key_format begin sheets = Google::Apis::SheetsV4::SheetsService.new scopes = ['https://www.googleapis.com/auth/spreadsheets.readonly'] sheets.authorization = Google::Auth.get_application_default(scopes) raw_values = sheets.get_spreadsheet_values(sheet_id, "#{sheet_name}!A:ZZ").values rescue Google::Apis::ClientError => e raise BadSheetId if e.message =~ /notFound/ raise Unauthorized if e.message =~ /forbidden/ rescue raise Error end rows_as_hashes(raw_values) end
Private Class Methods
convery_empty_cells_to_nil(row)
click to toggle source
# File lib/sheet_reader.rb, line 54 def self.convery_empty_cells_to_nil(row) row.map do |cell| if cell.strip == "" nil else cell end end end
ensure_valid_key_format()
click to toggle source
# File lib/sheet_reader.rb, line 42 def self.ensure_valid_key_format ENV['GOOGLE_PRIVATE_KEY'] = ENV['GOOGLE_PRIVATE_KEY'].gsub(/\\n/, "\n") end
required_env_vars?()
click to toggle source
# File lib/sheet_reader.rb, line 64 def self.required_env_vars? REQUIRED_ENV_VARS.all? do |e| ENV.has_key?(e) && ENV.fetch(e) && ENV.fetch(e).strip != "" end end
rows_as_hashes(rows)
click to toggle source
# File lib/sheet_reader.rb, line 46 def self.rows_as_hashes(rows) keys, *rest = rows rest.map do |row| Hash[keys.zip(convery_empty_cells_to_nil(row))] end end