Google Sheets AppScript现金与银行账户逐行余额计算异常求助

Hey there! Let's work through your problem step by step. I spotted two key issues with your current setup, and we'll fix them to get your bank balance updating correctly after every transaction:

1. Duplicate onEdit Functions Are Causing Conflicts #

In Google Apps Script, you can't have two functions with the same name onEdit—only the last one you defined will actually run. That means your cash balance logic isn't even executing right now! We'll merge both logic blocks into a single onEdit function to fix this.

2. Bank Balance Formula & Range Calculation Issues #

While your =sum($X$3:X)-sum($Y$3:Y) formula is technically meant to calculate cumulative balance, the range math in your script was off. Plus, using full-column cumulative sums can slow down your sheet as transactions pile up. A better approach is to use a recursive formula (just like your cash balance) where each row's balance builds on the previous row's value—it's faster and more efficient.

Solution 1: Cumulative Sum Approach (Fixed Script) #

This keeps your original formula but fixes the range logic and merges the duplicate onEdit functions:

function onEdit(e) {
  const masterSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Master");
  if (!masterSheet) return; // Exit if the target sheet isn't found

  const lastRow = masterSheet.getLastRow();
  if (lastRow < 3) return; // Need at least row 3 to start calculations

  // Update Cash Balance
  masterSheet.getRange("T3").setFormula("=sum($R$3:R3)-sum($S$3:S3)");
  const cashFillRange = masterSheet.getRange(4, 20, lastRow - 3);
  masterSheet.getRange("T3").copyTo(cashFillRange, SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);

  // Update Bank Balance
  masterSheet.getRange("Z3").setFormula("=sum($X$3:X3)-sum($Y$3:Y3)");
  const bankFillRange = masterSheet.getRange(4, 26, lastRow - 3);
  masterSheet.getRange("Z3").copyTo(bankFillRange, SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
}

Solution 2: Recursive Balance Approach (More Efficient) #

This uses the same logic as your cash balance (previous balance + current income - current expense) which is way faster for large datasets:

function onEdit(e) {
  const masterSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Master");
  if (!masterSheet) return;

  const lastRow = masterSheet.getLastRow();
  if (lastRow < 3) return;

  // Cash Balance (recursive version for consistency)
  masterSheet.getRange("T3").setFormula("=R3-S3");
  if (lastRow >= 4) {
    masterSheet.getRange("T4").setFormula("=T3+R4-S4");
    const cashFillRange = masterSheet.getRange(5, 20, lastRow - 4);
    masterSheet.getRange("T4").copyTo(cashFillRange, SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
  }

  // Bank Balance (recursive logic)
  masterSheet.getRange("Z3").setFormula("=X3-Y3");
  if (lastRow >= 4) {
    masterSheet.getRange("Z4").setFormula("=Z3+X4-Y4");
    const bankFillRange = masterSheet.getRange(5, 26, lastRow - 4);
    masterSheet.getRange("Z4").copyTo(bankFillRange, SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
  }
}

Key Fixes Explained #

• Merged onEdit Function: Now both cash and bank balance logic runs every time you edit the sheet, no more missed calculations.
• Corrected Range Calculation: lastRow - 3 ensures we only fill formulas from row 4 to the last populated row, avoiding unnecessary empty rows.
• Recursive Formula Option: Avoids recalculating the entire column sum for every row, which keeps your sheet snappy even as your transaction list grows.

内容的提问来源于stack exchange，提问作者Dilla