Class: GoogleSheets::Sheet

Inherits:
Object
  • Object
show all
Defined in:
lib/google_sheets/sheet.rb

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(service, sheet, spreadsheet) ⇒ Sheet

Returns a new instance of Sheet


13
14
15
16
17
18
19
# File 'lib/google_sheets/sheet.rb', line 13

def initialize service, sheet, spreadsheet
  @service = service
  @spreadsheet = spreadsheet
  @sheet = sheet
  @properties = sheet.properties.to_h
  @title = @properties[:title]
end

Instance Attribute Details

#propertiesHash (readonly)

Returns:

  • (Hash)

7
8
9
# File 'lib/google_sheets/sheet.rb', line 7

def properties
  @properties
end

#titleString (readonly)

title of the sheet

Returns:

  • (String)

10
11
12
# File 'lib/google_sheets/sheet.rb', line 10

def title
  @title
end

#valuesArray(String)

Returns an Array of string values, EG: [['one', 'two'], ['three', 'four']]

Returns:

  • (Array(String))

29
30
31
# File 'lib/google_sheets/sheet.rb', line 29

def values
  @values ||= @service.get_spreadsheet_values(@spreadsheet.key, @title).values
end

Class Method Details

.json_to_csv(json) ⇒ Array(Array)

Helper method for converting an array of hashes to csv-style values

Parameters:

  • an

    array of hashes to be converted to csv-style nested array format

Returns:

  • (Array(Array))

    csv style nested array


88
89
90
91
92
93
94
95
96
97
98
# File 'lib/google_sheets/sheet.rb', line 88

def self.json_to_csv json
  top_row = json.map(&:keys).flatten.uniq

  csv = json.map do |hash|
    top_row.map {|c| hash[c] }
  end

  csv.unshift top_row.map &:to_s

  csv
end

Instance Method Details

#delete!Sheet

Deletes a sheet from a spreadsheet

Returns:


35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
# File 'lib/google_sheets/sheet.rb', line 35

def delete!
  delete_sheet_request = Google::Apis::SheetsV4::DeleteSheetRequest.new
  delete_sheet_request.sheet_id = self.id

  batch_update_spreadsheet_request = Google::Apis::SheetsV4::BatchUpdateSpreadsheetRequest.new
  batch_update_spreadsheet_request.requests = Google::Apis::SheetsV4::Request.new

  batch_update_spreadsheet_request_object = [ delete_sheet: delete_sheet_request ]
  batch_update_spreadsheet_request.requests = batch_update_spreadsheet_request_object

  response = @service.batch_update_spreadsheet(@spreadsheet.key, batch_update_spreadsheet_request)

  @spreadsheet.sheets.delete(self)

  self
end

#idInteger

The internal ID of the sheet. From Google.

Returns:

  • (Integer)

23
24
25
# File 'lib/google_sheets/sheet.rb', line 23

def id
  @properties[:sheet_id]
end

#save!Object

Save the current values to the spreadsheet


76
77
78
79
80
81
82
83
# File 'lib/google_sheets/sheet.rb', line 76

def save!
  value_range_object = {
    majorDimension: 'ROWS',
    values: values
  }

  @service.update_spreadsheet_value(@spreadsheet.key, @title, value_range_object, value_input_option: 'RAW')
end

#set_values_from_json(json) ⇒ Object

Converts an array of hashes back to csv format. So the opposite of to_json

EG:

sheet.set_values_from_json([{name: 'john', age: '20'}])
sheet.values # => [['name', 'age'], ['john', '20']]

71
72
73
# File 'lib/google_sheets/sheet.rb', line 71

def set_values_from_json json
  self.values = Sheet.json_to_csv(json)
end

#to_jsonArray(Hash)

Converts the spreadsheet to an array of hashes, using the top row as the keys

EG [['name', 'age'], ['john', '20']] => [{name: 'john', age: '20'}]

Returns:

  • (Array(Hash))

56
57
58
59
# File 'lib/google_sheets/sheet.rb', line 56

def to_json
  top_row = values[0].map &:to_sym
  hashify_data(values[1..-1], top_row)
end