Class: SheetsAPI::Sheet

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

Instance Method Summary collapse

Constructor Details

#initialize(document, sheetName) ⇒ Sheet

Returns a new instance of Sheet.



45
46
47
48
 
# File 'lib/sheetsAPI.rb', line 45

def initialize(document, sheetName)
  @document = document
  @sheetName = sheetName
end

Instance Method Details

#insert(rows: [], index: [], upsert: false, sort: false) ⇒ Object 



50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
 
# File 'lib/sheetsAPI.rb', line 50

def insert(rows:[], index:[], upsert: false, sort: false)
  # Get current content of sheet
  sheetContent = SheetService.get_spreadsheet_values(@document.id, "'#{@sheetName}'")&.values || []

  # Get current headers in the sheet
  sheetHeaders = sheetContent.shift || []

  # Add any missing headers
  missingHeaders = ([Set.new(index)] + rows.map(&:keys)).reduce(&:merge).to_a.map(&:to_s) - sheetHeaders
  newHeaders = sheetHeaders + missingHeaders
  symbolHeaders = newHeaders.map(&:to_sym)

  # If we need to upsert and an index is provided
  if upsert && index.length > 0
    # If we upsert a row it no longer needs further processing
    rows.delete_if do |row|
      rowIndex = index.map{ |i| row[i].to_s }
      lineIndex = index.map{ |i| symbolHeaders.index(i) }
      # Attempt to match the index of the row with the corresponding index of each line in the current content
      match = sheetContent.index{ |line| lineIndex.map{ |i| line[i] } == rowIndex }
      # If we match, replace the values in that line with the contents of our row, and return true to remove this row
      if match
        sheetContent[match] = symbolHeaders.each_with_index.map { |header, index| row[header] || sheetContent[match][index] }
        true
      else
        false
      end
    end
  end

  # Append any remaining rows
  sheetContent += rows.map{ |row| symbolHeaders.map{ |header| row[header] || "" }}

  # Sort the output
  if sort && index.length > 0
    sortIndex = index.map{ |s| symbolHeaders.index(s) }
    sheetContent.sort!{ |row1, row2| sortIndex.map{ |i| row1[i].to_s } <=> sortIndex.map{ |i| row2[i].to_s }}
  end

  # Write the updated content to the spreadsheet
  SheetService.update_spreadsheet_value(@document.id, "'#{@sheetName}'", {major_dimension: "ROWS", values: sheetContent.unshift(newHeaders)}, value_input_option: 'USER_ENTERED')

end