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?
formula?(row, col, sheet = default_sheet)
Alias for: 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