Class: SpreadsheetReport

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

Instance Method Summary collapse

Constructor Details

#initialize(username, password, spreadsheet_key) {|_self| ... } ⇒ SpreadsheetReport

Returns a new instance of SpreadsheetReport.

Yields:

  • (_self)

Yield Parameters:



5
6
7
8
9
10
 
# File 'lib/spreadsheet_report.rb', line 5

def initialize(username, password, spreadsheet_key, &block)
  @session = GoogleSpreadsheet.(username, password)
  @spreadsheet = @session.spreadsheet_by_key(spreadsheet_key)
  
  yield(self)
end

Instance Method Details

#worksheet(name, query, cols = nil) ⇒ Object

Writes a report to a worksheet. By default, if cols is left nil, the report will base cols on the results of the query (but it’s a hash, so no ordering is guaranteed). If cols is given, only those columns are reported, and the spreadsheet columns are populated in the order that they are given.



16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
 
# File 'lib/spreadsheet_report.rb', line 16

def worksheet(name, query, cols = nil)
  worksheet = @spreadsheet.worksheets.find { |ws| ws.title == name }
  
  # create the worksheet if we didn't find it
  if worksheet != nil
    worksheet.delete
    worksheet = nil
  end

  # run the query
  result = ActiveRecord::Base.connection.select_all(query)

  # figure out the column names being used
  if cols.nil? && result.size > 0
    cols = result[0].keys      
  end
  
  # resize the worksheet
  worksheet = @spreadsheet.add_worksheet(name, result.size + 1, cols.size)
  
  # add the title cells
  cols.each_with_index do |key, index|
    worksheet[1, index + 1] = key
  end
  
  current_row = 2
  result.each do |row_data|
    cols.each_with_index do |key, index|
      worksheet[current_row, index + 1] = row_data[key]
    end
    current_row += 1
    
    if current_row % 250 == 0
      worksheet.save
    end
  end
  
  worksheet.save
  worksheet.synchronize
end