class SheetsAPI::Sheet

Public Class Methods

new(document, sheetName) click to toggle source
# File lib/sheetsAPI.rb, line 47
def initialize(document, sheetName)
  @document = document
  @sheetName = sheetName
end

Public Instance Methods

insert(rows:[], index:[], upsert: false, sort: false) click to toggle source
# File lib/sheetsAPI.rb, line 52
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