class SheetReaderWriter

Constants

REQUIRED_ENV_VARS
VERSION

Public Class Methods

new(sheet_id, write_permission: true) click to toggle source

Creates a new instance to interact with Google Sheets

Example:

>> SheetReaderWriter.new("1ukhJwquqRTgfX-G-nxV6AsAH726TOsKQpPJfpqNjWGg").read("Sheet 1")
=> [{"foo"=>"hey", "bar"=>"ho"},
    {"foo"=>"let's ", "bar"=>"go"}]

Arguments:

sheet_id: (String) The google sheet identifier.

# File lib/sheet_reader_writer.rb, line 20
def initialize(sheet_id, write_permission: true)
  raise MissingEnvVars unless required_env_vars?
  ensure_valid_key_format

  with_exceptions do
    sheets = Google::Apis::SheetsV4::SheetsService.new

    scopes = if write_permission
      ['https://www.googleapis.com/auth/spreadsheets']
    else
      ['https://www.googleapis.com/auth/spreadsheets.readonly']
    end

    sheets.authorization = Google::Auth.get_application_default(scopes)

    @sheet_service = sheets
    @sheet_id = sheet_id
  end
end

Public Instance Methods

clear(sheet_name = "") click to toggle source

Clears a google spreadsheet

Example:

>> sheet_reader_writer.clear

# File lib/sheet_reader_writer.rb, line 86
def clear(sheet_name = "")
  with_exceptions do
    @sheet_service.clear_values(@sheet_id, "#{sheet_name}!A:ZZ")
  end
end

read(sheet_name = "") click to toggle source

Fetches the content of a google spreadsheet

Example:

>> sheet_reader_writer.read("Sheet 1")
=> [{"foo"=>"hey", "bar"=>"ho"},
    {"foo"=>"let's ", "bar"=>"go"}]

Arguments:

sheet_name: (String) The sheet name, by default it's the first one

# File lib/sheet_reader_writer.rb, line 50
def read(sheet_name = "")
  with_exceptions do
    raw_values = @sheet_service.get_spreadsheet_value(@sheet_id, "#{sheet_name}!A:ZZ").values
    rows_as_hashes(raw_values)
  end
end

write(row_hashes, sheet_name = "") click to toggle source

Writes the specified content to a google spreadsheet

Example:

screen_reader_writer.write [

{"foo"=>"hey", "bar"=>"ho"},
{"foo"=>"let's"},
{"bar"=>"go"}

]

Arguments:

row_hashes: (Array of hashes) The values to update in the same format as returned by the read method

# File lib/sheet_reader_writer.rb, line 70
def write(row_hashes, sheet_name = "")
  value_range_object = {
    major_dimension: "ROWS",
    values: to_values_array(row_hashes)
  }

  with_exceptions do
    @sheet_service.update_spreadsheet_value(@sheet_id, "#{sheet_name}!A:ZZ", value_range_object, value_input_option: 'USER_ENTERED')
  end
end

Private Instance Methods

convery_empty_cells_to_nil(row) click to toggle source

# File lib/sheet_reader_writer.rb, line 142
def 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_writer.rb, line 130
def 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_writer.rb, line 152
def 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_writer.rb, line 134
def rows_as_hashes(rows)
  keys, *rest = rows

  rest.map do |row|
    Hash[keys.zip(convery_empty_cells_to_nil(row))]
  end
end

stringify_keys(hash) click to toggle source

# File lib/sheet_reader_writer.rb, line 94
def stringify_keys(hash)
  hash.reduce({}) do |hash, (k,v)|
    hash[k.to_s] = v
    hash
  end
end

to_values_array(row_hashes) click to toggle source

# File lib/sheet_reader_writer.rb, line 101
def to_values_array(row_hashes)
  row_hashes = row_hashes.map do |row_hash|
    stringify_keys(row_hash)
  end

  keys = row_hashes.map do |row_hash|
    row_hash.keys
  end.flatten.uniq

  values_array = row_hashes.map do |row_hash|
    keys.map do |key|
      row_hash[key] || ""
    end
  end

  values_array.unshift(keys)
end

with_exceptions() { || ... } click to toggle source

# File lib/sheet_reader_writer.rb, line 119
def with_exceptions
  begin
    yield
  rescue Google::Apis::ClientError => e
    raise BadSheetId if e.message =~ /notFound/
    raise Unauthorized if e.message =~ /forbidden/
  rescue StandardError => e
    raise e
  end
end