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

#properties ⇒ Hash (readonly)

[Google::Apis::SheetsV4::SheetProperties](github.com/googleapis/google-api-ruby-client/blob/master/generated/google/apis/sheets_v4/classes.rb#L8756) in hash form

Returns:

  • (Hash) 


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

def properties
  @properties
end

#title ⇒ String (readonly)

title of the sheet

Returns:

  • (String) 


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

def title
  @title
end

#values ⇒ Array(String)

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

Returns:

  • (Array(String)) 


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

Parameters:

  • json (Array(Hash)) —

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

Returns:

  • (Array(Array)) —

    csv style nested array



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

Returns:



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

#id ⇒ Integer

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



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([’john’, age: ‘20’]) sheet.values # => [[‘name’, ‘age’], [‘john’, ‘20’]] “‘

Returns:

  • (Array(Array)) 


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

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

EG ‘[[’name’, ‘age’], [‘john’, ‘20’]] => [‘john’, age: ‘20’]‘

Returns:

  • (Array(Hash)) 


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