Class: Roo::Google

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

Constant Summary collapse

DATE_FORMAT = 
'%d/%m/%Y'.freeze
DATETIME_FORMAT = 
'%d/%m/%Y %H:%M:%S'.freeze
TIME_FORMAT = 
'%H:%M:%S'.freeze

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

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

Creates a new Google Drive object.



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

def initialize(spreadsheet_key, options = {})
  @filename     = spreadsheet_key
  @access_token = options[:access_token] || ENV['GOOGLE_TOKEN']
  super
  @cell      = Hash.new { |h, k| h[k] = Hash.new }
  @cell_type = Hash.new { |h, k| h[k] = Hash.new }
  @formula   = {}
  %w(date time datetime).each do |key|
    __send__("#{key}_format=", self.class.const_get("#{key.upcase}_FORMAT"))
  end
end

Instance Attribute Details

#date_format ⇒ Object

Returns the value of attribute date_format.



5
6
7
 
# File 'lib/roo/google.rb', line 5

def date_format
  @date_format
end

#datetime_format ⇒ Object

Returns the value of attribute datetime_format.



5
6
7
 
# File 'lib/roo/google.rb', line 5

def datetime_format
  @datetime_format
end

#sheets ⇒ Object (readonly)

returns an array of sheet names in the spreadsheet



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

def sheets
  @sheets
end

#time_format ⇒ Object

Returns the value of attribute time_format.



5
6
7
 
# File 'lib/roo/google.rb', line 5

def time_format
  @time_format
end

Instance Method Details

#cell(row, col, sheet = default_sheet) ⇒ 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.



57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
 
# File 'lib/roo/google.rb', line 57

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

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

returns the type of a cell:

  • :float

  • :string

  • :date

  • :percentage

  • :formula

  • :time

  • :datetime



84
85
86
87
88
89
90
91
92
 
# File 'lib/roo/google.rb', line 84

def celltype(row, col, 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

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

true, if the cell is empty

Returns:

  • (Boolean) 


106
107
108
109
110
111
112
113
 
# File 'lib/roo/google.rb', line 106

def empty?(row, col, sheet = default_sheet)
  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

#formula(row, col, sheet = default_sheet) ⇒ 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.



97
98
99
100
101
 
# File 'lib/roo/google.rb', line 97

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

#numeric?(string) ⇒ Boolean

Returns:

  • (Boolean) 


44
45
46
 
# File 'lib/roo/google.rb', line 44

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

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

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



117
118
119
120
121
122
123
124
125
126
127
128
129
130
 
# File 'lib/roo/google.rb', line 117

def set(row, col, value, 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(row, col, value, sheet)
    set_type(row, col, value_type, sheet)
  end
end

#timestring_to_seconds(value) ⇒ Object 



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

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

#worksheets ⇒ Object 



25
26
27
 
# File 'lib/roo/google.rb', line 25

def worksheets
  @worksheets ||= session.spreadsheet_by_key(@filename).worksheets
end