Class: Roo::Google

Inherits:
Base
  • Object
show all
Defined in:
lib/roo/google.rb

Constant Summary

Constants inherited from Base

Base::TEMP_PREFIX

Instance Attribute Summary collapse

Attributes inherited from Base

#default_sheet, #header_line, #headers

Instance Method Summary collapse

Methods inherited from Base

#column, #each, #each_with_pagename, #find, #first_column_as_letter, #info, #last_column_as_letter, #method_missing, #parse, #reload, #row, #row_with, #sheet, #to_csv, #to_matrix, #to_xml, #to_yaml

Constructor Details

#initialize(spreadsheet_key, options = {}) ⇒ Google

Creates a new Google Drive object.



11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
 
# File 'lib/roo/google.rb', line 11

def initialize(spreadsheet_key, options = {})
  @filename = spreadsheet_key
  @user = options[:user] || ENV['GOOGLE_MAIL']
  @password = options[:password] || ENV['GOOGLE_PASSWORD']
  @access_token = options[:access_token] || ENV['GOOGLE_TOKEN']

  @worksheets = session.spreadsheet_by_key(@filename).worksheets
  @sheets = @worksheets.map {|sheet| sheet.title }
  super
  @cell = Hash.new {|h,k| h[k]=Hash.new}
  @cell_type = Hash.new {|h,k| h[k]=Hash.new}
  @formula = Hash.new
  @date_format = '%d/%m/%Y'
  @datetime_format = '%d/%m/%Y %H:%M:%S'
  @time_format = '%H:%M:%S'
end

Dynamic Method Handling

This class handles dynamic methods through the method_missing method in the class Roo::Base

Instance Attribute Details

#date_format ⇒ Object

Returns the value of attribute date_format.



8
9
10
 
# File 'lib/roo/google.rb', line 8

def date_format
  @date_format
end

#datetime_format ⇒ Object

Returns the value of attribute datetime_format.



8
9
10
 
# File 'lib/roo/google.rb', line 8

def datetime_format
  @datetime_format
end

#sheets ⇒ Object (readonly)

returns an array of sheet names in the spreadsheet



29
30
31
 
# File 'lib/roo/google.rb', line 29

def sheets
  @sheets
end

Instance Method Details

#cell(row, col, sheet = nil) ⇒ Object

Returns the content of a spreadsheet-cell. (1,1) is the upper left corner. (1,1), (1,‘A’), (‘A’,1), (‘a’,1) all refers to the cell at the first line and first row.



66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
 
# File 'lib/roo/google.rb', line 66

def cell(row, col, sheet=nil)
  sheet ||= @default_sheet
  validate_sheet!(sheet) #TODO: 2007-12-16
  read_cells(sheet)
  row,col = normalize(row,col)
  value = @cell[sheet]["#{row},#{col}"]
  if celltype(row,col,sheet) == :date
    begin
      return  Date.strptime(value, @date_format)
    rescue ArgumentError
      raise "Invalid Date #{sheet}[#{row},#{col}] #{value} using format '{@date_format}'"
    end
  elsif celltype(row,col,sheet) == :datetime
    begin
      return  DateTime.strptime(value, @datetime_format)
    rescue ArgumentError
      raise "Invalid DateTime #{sheet}[#{row},#{col}] #{value} using format '{@datetime_format}'"
    end
  end
  return value
end

#celltype(row, col, sheet = nil) ⇒ Object

returns the type of a cell:

  • :float

  • :string

  • :date

  • :percentage

  • :formula

  • :time

  • :datetime



96
97
98
99
100
101
102
103
104
105
 
# File 'lib/roo/google.rb', line 96

def celltype(row, col, sheet=nil)
  sheet ||= @default_sheet
  read_cells(sheet)
  row,col = normalize(row,col)
  if @formula.size > 0 && @formula[sheet]["#{row},#{col}"]
    :formula
  else
    @cell_type[sheet]["#{row},#{col}"]
  end
end

#date?(string) ⇒ Boolean

Returns:

  • (Boolean) 


31
32
33
34
35
36
 
# File 'lib/roo/google.rb', line 31

def date?(string)
  Date.strptime(string, @date_format)
  true
rescue
  false
end

#datetime?(string) ⇒ Boolean

Returns:

  • (Boolean) 


46
47
48
49
50
51
 
# File 'lib/roo/google.rb', line 46

def datetime?(string)
  DateTime.strptime(string, @datetime_format)
  true
rescue
  false
end

#empty?(row, col, sheet = nil) ⇒ Boolean

true, if the cell is empty

Returns:

  • (Boolean) 


119
120
121
122
123
124
125
126
 
# File 'lib/roo/google.rb', line 119

def empty?(row, col, sheet=nil)
  value = cell(row, col, sheet)
  return true unless value
  return false if value.class == Date # a date is never empty
  return false if value.class == Float
  return false if celltype(row,col,sheet) == :time
  value.empty?
end

#first_column(sheet = nil) ⇒ Object

returns the first non-empty column in a sheet



175
176
177
178
179
180
181
182
183
 
# File 'lib/roo/google.rb', line 175

def first_column(sheet=nil)
  sheet ||= @default_sheet
  unless @first_column[sheet]
    sheet_no = sheets.index(sheet) + 1
    @first_row[sheet], @last_row[sheet], @first_column[sheet], @last_column[sheet] =
      oben_unten_links_rechts(sheet_no)
  end
  return @first_column[sheet]
end

#first_row(sheet = nil) ⇒ Object

returns the first non-empty row in a sheet



153
154
155
156
157
158
159
160
161
 
# File 'lib/roo/google.rb', line 153

def first_row(sheet=nil)
  sheet ||= @default_sheet
  unless @first_row[sheet]
    sheet_no = sheets.index(sheet) + 1
    @first_row[sheet], @last_row[sheet], @first_column[sheet], @last_column[sheet] =
      oben_unten_links_rechts(sheet_no)
  end
  return @first_row[sheet]
end

#formula(row, col, sheet = nil) ⇒ Object Also known as: formula?

Returns the formula at (row,col). Returns nil if there is no formula. The method #formula? checks if there is a formula.



110
111
112
113
114
115
 
# File 'lib/roo/google.rb', line 110

def formula(row,col,sheet=nil)
  sheet ||= @default_sheet
  read_cells(sheet)
  row,col = normalize(row,col)
  @formula[sheet]["#{row},#{col}"] && @formula[sheet]["#{row},#{col}"]
end

#last_column(sheet = nil) ⇒ Object

returns the last non-empty column in a sheet



186
187
188
189
190
191
192
193
194
 
# File 'lib/roo/google.rb', line 186

def last_column(sheet=nil)
  sheet ||= @default_sheet
  unless @last_column[sheet]
    sheet_no = sheets.index(sheet) + 1
    @first_row[sheet], @last_row[sheet], @first_column[sheet], @last_column[sheet] =
      oben_unten_links_rechts(sheet_no)
  end
  return @last_column[sheet]
end

#last_row(sheet = nil) ⇒ Object

returns the last non-empty row in a sheet



164
165
166
167
168
169
170
171
172
 
# File 'lib/roo/google.rb', line 164

def last_row(sheet=nil)
  sheet ||= @default_sheet
  unless @last_row[sheet]
    sheet_no = sheets.index(sheet) + 1
    @first_row[sheet], @last_row[sheet], @first_column[sheet], @last_column[sheet] =
      oben_unten_links_rechts(sheet_no)
  end
  return @last_row[sheet]
end

#numeric?(string) ⇒ Boolean

Returns:

  • (Boolean) 


53
54
55
 
# File 'lib/roo/google.rb', line 53

def numeric?(string)
  string =~ /^[0-9]+[\.]*[0-9]*$/
end

#set(row, col, value, sheet = nil) ⇒ Object

sets the cell to the content of ‘value’ a formula can be set in the form of ‘=SUM(…)’



130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
 
# File 'lib/roo/google.rb', line 130

def set(row,col,value,sheet=nil)
  sheet ||= @default_sheet
  validate_sheet!(sheet)

  sheet_no = sheets.index(sheet)+1
  row,col = normalize(row,col)
  add_to_cell_roo(row,col,value,sheet_no)
  # re-read the portion of the document that has changed
  if @cells_read[sheet]
    value, value_type = determine_datatype(value.to_s)

    _set_value(col,row,value,sheet)
    set_type(col,row,value_type,sheet)
  end
end

#set_value(row, col, value, sheet = nil) ⇒ Object

DEPRECATED: Use Roo::Google#set instead



147
148
149
150
 
# File 'lib/roo/google.rb', line 147

def set_value(row,col,value,sheet=nil)
  warn "[DEPRECATION] `set_value` is deprecated.  Please use `set` instead."
  set(row,col,value,sheet)
end

#time?(string) ⇒ Boolean

is String a time with format HH:MM:SS?

Returns:

  • (Boolean) 


39
40
41
42
43
44
 
# File 'lib/roo/google.rb', line 39

def time?(string)
  DateTime.strptime(string, @time_format)
  true
rescue
  false
end

#timestring_to_seconds(value) ⇒ Object 



57
58
59
60
 
# File 'lib/roo/google.rb', line 57

def timestring_to_seconds(value)
  hms = value.split(':')
  hms[0].to_i*3600 + hms[1].to_i*60 + hms[2].to_i
end