Class: SheetReaderWriter

Inherits:
Object
  • Object
show all
Defined in:
lib/sheet_reader_writer.rb,
lib/sheet_reader_writer/errors.rb,
lib/sheet_reader_writer/version.rb

Defined Under Namespace

Classes: BadSheetId, Error, MissingEnvVars, Unauthorized

Constant Summary collapse

REQUIRED_ENV_VARS = 
%w[GOOGLE_CLIENT_EMAIL
GOOGLE_ACCOUNT_TYPE
GOOGLE_PRIVATE_KEY]
VERSION = 
'1.1.4'

Instance Method Summary collapse

Constructor Details

#initialize(sheet_id, write_permission: true) ⇒ SheetReaderWriter

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.

Raises:



20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
 
# 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

Instance Method Details

#clear(sheet_name = "") ⇒ Object

Clears a google spreadsheet

Example:

>> sheet_reader_writer.clear



86
87
88
89
90
 
# 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 = "") ⇒ Object

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



50
51
52
53
54
55
 
# 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 = "") ⇒ Object

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



70
71
72
73
74
75
76
77
78
79
 
# 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