class Roo::Google
Constants
- DATETIME_FORMAT
- DATE_FORMAT
- TIME_FORMAT
Attributes
date_format[RW]
datetime_format[RW]
sheets[R]
returns an array of sheet names in the spreadsheet
time_format[RW]
Public Class Methods
new(spreadsheet_key, options = {})
click to toggle source
Creates a new Google
Drive object.
Calls superclass method
# 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
Public Instance Methods
cell(row, col, sheet = default_sheet)
click to toggle source
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.
# 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)
click to toggle source
returns the type of a cell:
-
:float
-
:string
-
:date
-
:percentage
-
:formula
-
:time
-
:datetime
# 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)
click to toggle source
true, if the cell is empty
# 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)
click to toggle source
Returns the formula at (row,col). Returns nil if there is no formula. The method formula?
checks if there is a formula.
# 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
Also aliased as: formula?
numeric?(string)
click to toggle source
# 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)
click to toggle source
sets the cell to the content of 'value' a formula can be set in the form of '=SUM(…)'
# 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)
click to toggle source
# 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()
click to toggle source
# File lib/roo/google.rb, line 25 def worksheets @worksheets ||= session.spreadsheet_by_key(@filename).worksheets end
Private Instance Methods
_set_value(row, col, value, sheet = default_sheet)
click to toggle source
# File lib/roo/google.rb, line 151 def _set_value(row, col, value, sheet = default_sheet) @cell[sheet]["#{row},#{col}"] = value end
add_to_cell_roo(row, col, value, sheet_no = 1)
click to toggle source
# File lib/roo/google.rb, line 207 def add_to_cell_roo(row, col, value, sheet_no = 1) sheet_no -= 1 worksheets[sheet_no][row, col] = value worksheets[sheet_no].save end
determine_datatype(val, numval = nil)
click to toggle source
# File lib/roo/google.rb, line 184 def determine_datatype(val, numval = nil) if val.nil? || val[0, 1] == '=' ty = :formula val = numeric?(numval) ? numval.to_f : numval else case when datetime?(val) ty = :datetime when date?(val) ty = :date when numeric?(val) ty = :float val = val.to_f when time?(val) ty = :time val = timestring_to_seconds(val) else ty = :string end end [val, ty] end
entry_roo(value, row, col)
click to toggle source
# File lib/roo/google.rb, line 213 def entry_roo(value, row, col) [value, row, col] end
read_cell_row(sheet, ws, row, col)
click to toggle source
# File lib/roo/google.rb, line 173 def read_cell_row(sheet, ws, row, col) key = "#{row},#{col}" string_value = ws.input_value(row, col) # item['inputvalue'] || item['inputValue'] numeric_value = ws[row, col] # item['numericvalue'] || item['numericValue'] (value, value_type) = determine_datatype(string_value, numeric_value) @cell[sheet][key] = value unless value == '' || value.nil? @cell_type[sheet][key] = value_type @formula[sheet] = {} unless @formula[sheet] @formula[sheet][key] = string_value if value_type == :formula end
read_cells(sheet = default_sheet)
click to toggle source
read all cells in a sheet.
# File lib/roo/google.rb, line 160 def read_cells(sheet = default_sheet) validate_sheet!(sheet) return if @cells_read[sheet] ws = worksheets[sheets.index(sheet)] for row in 1..ws.num_rows for col in 1..ws.num_cols read_cell_row(sheet, ws, row, col) end end @cells_read[sheet] = true end
reinitialize()
click to toggle source
# File lib/roo/google.rb, line 232 def reinitialize @session = nil initialize(@filename, access_token: @access_token) end
rows_and_cols_min_max(sheet_no)
click to toggle source
# File lib/roo/google.rb, line 217 def rows_and_cols_min_max(sheet_no) ws = worksheets[sheet_no - 1] rows = [] cols = [] for row in 1..ws.num_rows for col in 1..ws.num_cols if ws[row, col] && !ws[row, col].empty? rows << row cols << col end end end [rows.min, rows.max, cols.min, cols.max] end
session()
click to toggle source
# File lib/roo/google.rb, line 237 def session @session ||= if @access_token ::GoogleDrive.login_with_oauth(@access_token) else warn 'set access token' end end
set_first_last_row_column(sheet)
click to toggle source
# File lib/roo/google.rb, line 145 def set_first_last_row_column(sheet) sheet_no = sheets.index(sheet) + 1 @first_row[sheet], @last_row[sheet], @first_column[sheet], @last_column[sheet] = rows_and_cols_min_max(sheet_no) end
set_type(row, col, type, sheet = default_sheet)
click to toggle source
# File lib/roo/google.rb, line 155 def set_type(row, col, type, sheet = default_sheet) @cell_type[sheet]["#{row},#{col}"] = type end