Payments.js

// Payments

function getPayments(){  
  // Create an array of the values in the data range.
  var values = getAllSheetValues('Payments')
  return values.map(rowToPayment)
}

function rowToPayment(row, index){
  // Create an object with the values for easier handling
  return {
    position: index+1,
    id: row[0],
    timestamp: row[1],
    payer_id: row[2],        
    amount: row[3],
    date: row[4],
    month: Utilities.formatDate(new Date(row[4]), Session.getScriptTimeZone(), "MMM"),
    status: row[5],
    category: row[9]
  }
}

function newPaymentCreated(event){    
  //if(event.changeType == 'EDIT' && event.source.getActiveSheet().getName() == 'Payments'){    
    // Get latest payment in Spreadsheet
    var sheet = getMaintenanceSpreadsheet('Payments')
    var lastRow = sheet.getLastRow()
    var paymentRange = sheet.getRange(lastRow,1,1,10)
    //Transform to Object with a -1 in last row cause it's adding it up later
    var newPayment = rowToPayment(paymentRange.getValues().flat(), lastRow-1)
    // Process payment to determine if it's fixed or variable
    processPayment(newPayment)
  //}
}


function setStatus(payment, status){  
  var paymentsSheet = getMaintenanceSpreadsheet('Payments')
  var paymentRange = paymentsSheet.getRange(payment.position,6)
  paymentRange.setValue(status)
}

function processPayment(payment){
  // Double check it's a new payment or just a manual edit
  if (payment.status != 'Conciliado'){
    console.log(payment)
    if(payment.category == 'Luz' || payment.category == 'Reparaciones'){
      // Creates debt with exact payment amount
      processVariablePayment(payment)
    }
    else{        
      // Match payment with open debts
      processFixedPayment(payment)
    }
  }
}

function processFixedPayment(p){  
  // Get debts by apartment
  var debts = getDebtsByApartment(p.payer_id)
  // Filter debts by the same category
  debts = debts.filter(debt => debt.category == p.category)
  // Sort debts by late fees first and then debts
  debts.sort(sortDebts)
  // Set initial qty to payment amount
  var quantity = p.amount
  var i = 0
  // While there is money settle debts from oldest to newest
  while(quantity > 0){
    quantity = settleDebt(debts[i],p)
    i++
  }
  setStatus(p,"Conciliado")
}
// Process payments with variable quantities and creates debts with equal amounts
function processVariablePayment(p){
  // Create matching debt for Building 999
  var fixedDebt = newDebt('999',p.amount,p.date,"Deuda",p.category)
  // Create conciliation
  createConciliation(p,fixedDebt,fixedDebt.amount)
  // Update debt status
  fixedDebt.amount = 0
  fixedDebt.status = 'Pagada'
  debtToRow(fixedDebt)
  setStatus(p,"Conciliado")
}

// Utility to test logic
function processAllPayments(){  
  var payments = getPayments()
  payments = payments.filter(payment => payment.status == "Nuevo")    
  payments.forEach(processPayment)
}