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