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


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)


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

def properties
  @properties
end

#titleString (readonly)

title of the sheet


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']]


29
30
31
32
33
34
35
36
37
38
39
# File 'lib/google_sheets/sheet.rb', line 29

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

    if GoogleSheets.strip_all_cells
      vals.flatten.each &:strip!
    end

    vals
  end
end

Class Method Details

.json_to_csv(json) ⇒ Array(Array)

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


96
97
98
99
100
101
102
103
104
105
106
# File 'lib/google_sheets/sheet.rb', line 96

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


43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
# File 'lib/google_sheets/sheet.rb', line 43

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.


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


84
85
86
87
88
89
90
91
# File 'lib/google_sheets/sheet.rb', line 84

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) ⇒ Array(Array)

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']]

79
80
81
# File 'lib/google_sheets/sheet.rb', line 79

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'}]


64
65
66
67
# File 'lib/google_sheets/sheet.rb', line 64

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