Google Script技术问询：如何实现数据表第12列的运行总计计算

核心需求回顾 #

需要在目标账户数据表的新空白行第12列写入运行总计，计算规则：(TRANSACTIONS表E17收款 - E15付款) + 目标表最后一行第12列历史累计值

两种实现方案 #

方案1：写入静态计算值（值固定，不随后续数据修改变动）

直接计算出运行总计的数值后写入单元格，适合不需要动态更新的场景：

修改后的完整脚本：

function submitData(){
  var myGoogleSheet = SpreadsheetApp.getActiveSpreadsheet();
  var shUserForm = myGoogleSheet.getSheetByName("TRANSACTIONS");
  var shAccount = shUserForm.getRange("E5").getValue();
  var datasheet = myGoogleSheet.getSheetByName(shAccount);
  var blankRow = datasheet.getLastRow() + 1;

  // 写入基础数据
  datasheet.getRange(blankRow,2).setValue(shUserForm.getRange("E5").getValue()); 
  datasheet.getRange(blankRow,3).setValue(shUserForm.getRange("E7").getValue());
  datasheet.getRange(blankRow,4).setValue(shUserForm.getRange("E9").getValue());
  datasheet.getRange(blankRow,5).setValue(shUserForm.getRange("E11").getValue());
  datasheet.getRange(blankRow,6).setValue(shUserForm.getRange("E13").getValue());
  datasheet.getRange(blankRow,7).setValue(shUserForm.getRange("E19").getValue());

  // 计算并写入运行总计
  var received = shUserForm.getRange("E17").getValue();
  var paid = shUserForm.getRange("E15").getValue();
  var difference = received - paid;
  // 处理目标表为空的情况（首次写入）
  var historicalTotal = datasheet.getLastRow() === 0 ? 0 : datasheet.getRange(datasheet.getLastRow(), 12).getValue();
  var runningTotal = difference + historicalTotal;
  datasheet.getRange(blankRow, 12).setValue(runningTotal);
}

方案2：设置动态公式（值随上一行累计值变动而更新）

如果希望后续目标表历史累计值修改时，当前行的运行总计自动更新，可以设置单元格公式：

修改后的完整脚本：

function submitData(){
  var myGoogleSheet = SpreadsheetApp.getActiveSpreadsheet();
  var shUserForm = myGoogleSheet.getSheetByName("TRANSACTIONS");
  var shAccount = shUserForm.getRange("E5").getValue();
  var datasheet = myGoogleSheet.getSheetByName(shAccount);
  var blankRow = datasheet.getLastRow() + 1;

  // 写入基础数据
  datasheet.getRange(blankRow,2).setValue(shUserForm.getRange("E5").getValue()); 
  datasheet.getRange(blankRow,3).setValue(shUserForm.getRange("E7").getValue());
  datasheet.getRange(blankRow,4).setValue(shUserForm.getRange("E9").getValue());
  datasheet.getRange(blankRow,5).setValue(shUserForm.getRange("E11").getValue());
  datasheet.getRange(blankRow,6).setValue(shUserForm.getRange("E13").getValue());
  datasheet.getRange(blankRow,7).setValue(shUserForm.getRange("E19").getValue());

  // 设置运行总计公式
  var received = shUserForm.getRange("E17").getValue();
  var paid = shUserForm.getRange("E15").getValue();
  var difference = received - paid;
  var formula;
  // 处理目标表为空的情况（首次写入）
  if (blankRow === 1) {
    formula = `=${difference}`;
  } else {
    // L是第12列的列标，引用上一行的累计值
    formula = `=${difference}+L${blankRow - 1}`;
  }
  datasheet.getRange(blankRow, 12).setFormula(formula);
}

关键说明 #

• 两种方案都处理了目标账户数据表为空的边界情况（首次写入时历史累计值为0）
• 方案1的数值是静态的，提交后不受原表单或历史行修改影响；方案2的公式是动态的，会随上一行累计值变化自动更新

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