Untitled

function summarizeDebts() {
  try {
    // Open the active spreadsheet and select the "Expense Calculator" sheet
    var expenseSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Expense Calculator");
    
    if (!expenseSheet) {
      throw new Error('Sheet "Expense Calculator" not found');
    }
    
    // Get the values in Column M and remove empty cells
    var data = expenseSheet.getRange("M:M").getValues().flat().filter(String);
    
    // Sort the data and remove duplicates
    var uniqueData = Array.from(new Set(data)).sort();
    
    // Create an object to store the debts
    var debts = {};
    
    // Process each row in the unique, sorted data
    for (var i = 0; i < uniqueData.length; i++) {
      var row = uniqueData[i];
      
      if (row) {
        // Extract the names and amount from the string
        var match = row.match(/(\w+)\sowes\s(\w+)\s\$(\d+\.\d{2})/);
        
        if (match) {
          var debtor = match[1];
          var creditor = match[2];
          var amount = parseFloat(match[3]);
          
          // Initialize the debts object if necessary
          if (!debts[debtor]) {
            debts[debtor] = {};
          }
          if (!debts[debtor][creditor]) {
            debts[debtor][creditor] = 0;
          }
          
          // Add the amount to the debts object
          debts[debtor][creditor] += amount;
        }
      }
    }
    
    // Open the "Expense" sheet and find the cell containing "Summary of Debts as of:"
    var summarySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Expense");
    
    if (!summarySheet) {
      throw new Error('Sheet "Expense" not found');
    }
    
    var range = summarySheet.createTextFinder("Summary of Debts as of:").findNext();
    if (range) {
      var row = range.getRow();
      var column = range.getColumn();
      
      // Find the first row below "Summary of Debts as of:"
      var targetRow = row + 1;

      // Clear existing content below "Summary of Debts as of:"
      while (summarySheet.getRange(targetRow, column).getValue() !== "") {
        summarySheet.getRange(targetRow, column, 1, 1).clearContent();
        targetRow++;
      }

      // Reset targetRow back to the first row below "Summary of Debts as of:"
      targetRow = row + 1;

      // Combine the summarized debts into strings
      var summaryStrings = [];
      for (var debtor in debts) {
        for (var creditor in debts[debtor]) {
          var debtString = debtor + " owes " + creditor + " $" + debts[debtor][creditor].toFixed(2);
          summaryStrings.push([debtString]);
        }
      }
      
      if (summaryStrings.length > 0) {
        var targetRange = summarySheet.getRange(targetRow, column, summaryStrings.length, 1);
        targetRange.setValues(summaryStrings);
        targetRange.setFontFamily("Montserrat").setFontSize(10).setHorizontalAlignment("left");
      }
    } else {
      Logger.log('Cell containing "Summary of Debts as of:" not found.');
    }
  } catch (error) {
    Logger.log(error.message);
  }
}