Apps Script替代appendRow方案：指定列找空行且不覆盖公式列

问题背景 #

开发的定价计算器包含「Calculator」（管理员输入）和「DataLog」（存储结果）两张工作表：

• DataLog的A-X列存储输入结果，Y-AO列是自动计算价格的公式，AP-AS列是工作流相关内容
• 当前使用appendRow()写入数据，但因Y-AS列非空，appendRow()会自动将数据追加到表格最底部，无法利用已有空行
• 核心需求：
  1. 仅检查指定列（如A列）查找第一个空行并写入数据
  2. 绝对不能覆盖Y-AS列的公式和工作流内容
  3. 无法提前计算价格写入静态值，需保留公式以便后续修改行数据时自动更新价格

解决思路 #

1. 遍历DataLog的目标检查列（如A列），定位第一个空行的行号
2. 仅将输入数据写入该行的A-X列，完全避开后续公式/工作流列
3. 保留原有输入校验、清空逻辑，只替换数据写入的核心逻辑

修改后的完整代码 #

function ClearCells() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('CALCULATOR');
  sheet.getRange('G9:H9').clearContent();
  sheet.getRange('G11').clearContent();
  sheet.getRange('G14:H14').clearContent();
  sheet.getRange('G6').clearContent();
  sheet.getRange('I6').clearContent();
  sheet.getRange('I17:I21').clearContent();
  sheet.getRange('I24:I29').clearContent();
  sheet.getRange('I32').clearContent();
  sheet.getRange('K5').clearContent();
  sheet.getRange('K15').clearContent();
}

function FinalizePrice() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  // 读取各命名区域数据
  const sourceRangeFL = ss.getRangeByName('FirstLast');
  const sourceValsFL = sourceRangeFL.getValues().flat();
  const sourceRangeEN = ss.getRangeByName('EntityName');
  const sourceValsEN = sourceRangeEN.getValues().flat();
  const sourceRangeEP = ss.getRangeByName('EmailPhone');
  const sourceValsEP = sourceRangeEP.getValues().flat();
  const sourceRangeRT = ss.getRangeByName('ReturnType');
  const sourceValsRT = sourceRangeRT.getValues().flat();
  const sourceRangeRE = ss.getRangeByName('Returning');
  const sourceValsRE = sourceRangeRE.getValues().flat();
  const sourceRangeBQ = ss.getRangeByName('BasicQuestions');
  const sourceValsBQ = sourceRangeBQ.getValues().flat();
  const sourceRangeSEQ = ss.getRangeByName('SchEQuestions');
  const sourceValsSEQ = sourceRangeSEQ.getValues().flat();
  const sourceRangeEQ = ss.getRangeByName('EntityQuestions');
  const sourceValsEQ = sourceRangeEQ.getValues().flat();
  const sourceRangePYP = ss.getRangeByName('PYP');
  const sourceValsPYP = sourceRangePYP.getValues().flat();
  const sourceRangeADJ = ss.getRangeByName('Adjustment')
  const sourceValsADJ = sourceRangeADJ.getValues().flat();
  const sourceRangeAN = ss.getRangeByName('AdjustmentNote')
  const sourceValsAN = sourceRangeAN.getValues().flat();

  // 合并所有输入值
  const sourceVals = [...sourceValsFL, ...sourceValsEN, ...sourceValsEP, ...sourceValsRT, ...sourceValsRE, ...sourceValsBQ, ...sourceValsSEQ, ...sourceValsEQ, ...sourceValsPYP, ...sourceValsADJ, ...sourceValsAN]

  // 校验输入是否完整
  const anyEmptyCell = sourceVals.findIndex(cell => cell === "");
  if(anyEmptyCell !== -1){
    const ui = SpreadsheetApp.getUi();
    ui.alert(
      "Input Incomplete",
      "Please enter a value in ALL input cells before submitting",
      ui.ButtonSet.OK
    );
    return;
  }

  // 准备要写入的数据（日期+邮箱+输入值）
  const date = new Date();
  const email = Session.getActiveUser().getEmail();
  const data = [date, email, ...sourceVals];

  const destinationSheet = ss.getSheetByName("DataLog");
  // 关键逻辑：查找A列第一个空行
  const checkColumn = destinationSheet.getRange("A:A");
  const checkValues = checkColumn.getValues().flat();
  // 数组索引从0开始，行号从1开始，所以+1转换为行号
  const firstEmptyRow = checkValues.findIndex(value => value === "") + 1;

  // 如果A列所有行都有数据，就追加到最后一行
  const targetRow = firstEmptyRow === 0 ? destinationSheet.getLastRow() + 1 : firstEmptyRow;

  // 仅写入A到X列（共24列，A=1，X=24）
  const targetRange = destinationSheet.getRange(targetRow, 1, 1, 24);
  targetRange.setValues([data]);

  // 清空输入区域
  sourceRangeFL.clearContent();
  sourceRangeEN.clearContent();
  sourceRangeEP.clearContent();
  sourceRangeRT.clearContent();
  sourceRangeRE.clearContent();
  sourceRangeBQ.clearContent();
  sourceRangeSEQ.clearContent();
  sourceRangeEQ.clearContent();
  sourceRangePYP.clearContent();
  sourceRangeADJ.clearContent();
  sourceRangeAN.clearContent();

  ss.toast("Success: Item added to the Data Log!");
}

关键说明 #

• 空行定位：通过遍历A列所有值找到第一个空行；若A列无空行，则自动追加到表格末尾
• 范围控制：用getRange(targetRow, 1, 1, 24)严格限定写入范围为目标行的A到X列，完全不触碰Y-AS列的公式和工作流
• 动态更新支持：未修改Y-AS列的公式，后续修改A-X列的输入数据时，Y-AO列的价格会自动重新计算

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