Google Apps Script：如何用For循环在指定行范围批量写入数据？

• 现有可打印表格模板，行范围为19至49行，下方存在“Certification”文本，导致getLastRow()方法无法正确识别表格的最后有效行
• RC Disb (OpEx)与RC Disb (MBAP)模板样式一致，DV Logbook、CashDR各有对应样式（附对应模板截图：RC Disb模板、DV Logbook模板、CashDR模板）
• 原本习惯用getLastRow()定位表格起始位置，但当前模板位于表格中间且下方有文本，该方法失效，尝试改用For循环计数写入单元格，但无法正确实现
• 因需根据主表（DV Logbook）中的值选择对应模板，使用forEach遍历判断，编写了sortSCA函数，但存在多处问题，代码如下：

function sortSCA(){

const ws_lb = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DV Logbook");
const ws_opex = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("RCDisb (OpEx)");
const ws_mbap = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("RCDisb (MBAP)");
const ws_cashdr = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CashDR");

const columnB = ["B"]
const columnD = ["D"]
const columnF = ["F"]
const columnI = ["I"]

const timestamp_Range = ws_lb.getRange(columnB + lastrow_lb);
const payee_Range = ws_lb.getRange(columnD + lastrow_lb);
const particulars_Range = ws_lb.getRange(columnF + lastrow_lb);
const netAmount_Range = ws_lb.getRange(columnI + lastrow_lb);

const timestamp_value = timestamp_Range.getValue();
const payee_value = payee_Range.getValue();
const particulars_value = particulars_Range.getDisplayValue();
const netAmount_value = netAmount_Range.getDisplayValue();

const lastrow_lb = ws_lb.getLastRow();
const lastrow_opex = ws_opex.getLastRow();
const lastrow_mbap = ws_mbap.getLastRow();
const lastrow_cashdr = ws_cashdr.getLastRow();


  var range = ws_lb.getRange(1, 11, lastrow_lb, 1);
  var values = range.getValues();
  Logger.log(values);

  values.forEach(x => {
    if(x == "Operating Expenses"){
    for(i=19,49;i<1;i++){
      ws_opex.getRange(i, 2, 1, 1).setValue(timestamp_value);
      ws_opex.getRange(i, 6, 1, 1).setValue(payee_value);
      ws_opex.getRange(i, 8, 1, 1).setValue(particulars_value);
      ws_opex.getRange(i, 9, 1, 1).setValue(netAmount_value);
      //cashdr
      ws_cashdr.getRange(lastrow_cashdr + 1, 1, 1, 1).setValue(timestamp_value);
      ws_cashdr.getRange(lastrow_cashdr + 1, 2, 1, 1).setValue(dmsNumber_value);
      ws_cashdr.getRange(lastrow_cashdr + 1, 3, 1, 1).setValue(payee_value);
      ws_cashdr.getRange(lastrow_cashdr + 1, 6, 1, 1).setValue(particulars_value);
      var grossAmountCashDR = ws_cashdr.getRange(lastrow_cashdr + 1, 9, 1, 1)
      var grossAmountUse = grossAmountCashDR.getValue();
      grossAmountCashDR.setValue(grossAmount_value);
      var balanceCashDR = ws_cashdr.getRange(10, 10, 1, 1).getValue();
      ws_cashdr.getRange(lastrow_cashdr + 1, 10, 1, 1).setValue(balanceCashDR - grossAmountUse);
    }

    } else if(x == "Medical Expense"){
    //opex
      var dateOpex = ws_mbap.getRange(13 + lastrow_opex, 2, 1, 1).setValue(timestamp_value);
      var payeeOpex = ws_mbap.getRange(13 + lastrow_opex, 6, 1, 1).setValue(payee_value);
      var particularsOpex = ws_mbap.getRange(13 + lastrow_opex, 8, 1, 1).setValue(particulars_value);
      var amountOpex = ws_mbap.getRange(13 + lastrow_opex, 9, 1, 1).setValue(netAmount_value);
      //cashdr
      var dateCashDR = ws_cashdr.getRange(15 + lastrow_opex, 1, 1, 1).setValue(timestamp_value);
      var dvNumberCashDR = ws_cashdr.getRange(15 + lastrow_opex, 2, 1, 1).setValue(dmsNumber_value);
      var payeeCashDR = ws_cashdr.getRange(15 + lastrow_opex, 3, 1, 1).setValue(payee_value);
      var particularsCashDR = ws_cashdr.getRange(15 + lastrow_opex, 6, 1, 1).setValue(particulars_value);
      var grossAmountCashDR = ws_cashdr.getRange(15 + lastrow_opex, 9, 1, 1).setValue(grossAmount_value);
      var grossAmountUse = grossAmountCashDR.getValue();
      grossAmountCashDR.setValue(grossAmount_value);
      var balanceCashDR = ws_cashdr.getRange(10, 10, 1, 1).getValue();
      ws_cashdr.getRange(15 + lastrow_opex, 10, 1, 1).setValue(balanceCashDR - grossAmountUse);
     }
  });
}

代码问题分析 #

1. 变量声明顺序错误：lastrow_lb在被用于获取单元格范围后才定义，会直接导致运行报错
2. For循环语法完全错误：for(i=19,49;i<1;i++)的写法不符合JavaScript循环规则，且i<1的条件永远不成立，循环不会执行
3. 未定义变量：dmsNumber_value、grossAmount_value没有声明就直接使用，会抛出引用错误
4. 行号判断错误：lastrow_opex等通过getLastRow()获取的行号包含了下方的“Certification”文本行，不是模板内的有效空行
5. 数组遍历错误：values是二维数组，forEach中的x是单个单元格的数组（如["Operating Expenses"]），直接用x == "Operating Expenses"判断永远不成立

修正后的代码 #

function sortSCA() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ws_lb = ss.getSheetByName("DV Logbook");
  const ws_opex = ss.getSheetByName("RCDisb (OpEx)");
  const ws_mbap = ss.getSheetByName("RCDisb (MBAP)");
  const ws_cashdr = ss.getSheetByName("CashDR");

  // 先获取主表最后行，再取对应单元格值
  const lastrow_lb = ws_lb.getLastRow();
  const timestamp_value = ws_lb.getRange(`B${lastrow_lb}`).getValue();
  const payee_value = ws_lb.getRange(`D${lastrow_lb}`).getValue();
  const particulars_value = ws_lb.getRange(`F${lastrow_lb}`).getDisplayValue();
  const netAmount_value = ws_lb.getRange(`I${lastrow_lb}`).getDisplayValue();
  // 补充获取缺失的变量（替换为实际列）
  const dmsNumber_value = ws_lb.getRange(`K${lastrow_lb}`).getValue();
  const grossAmount_value = ws_lb.getRange(`J${lastrow_lb}`).getValue();

  // 获取主表第11列（K列）的所有值
  const values = ws_lb.getRange(1, 11, lastrow_lb, 1).getValues();

  // 遍历每行数据
  values.forEach((row, index) => {
    const category = row[0]; // 二维数组取第一个元素
    if (category === "Operating Expenses") {
      // 找OpEx模板19-49行的第一个空行（以B列为判断依据）
      let targetRow = null;
      for (let i = 19; i <= 49; i++) {
        if (ws_opex.getRange(i, 2).isBlank()) {
          targetRow = i;
          break;
        }
      }
      if (targetRow) {
        // 写入OpEx模板
        ws_opex.getRange(targetRow, 2).setValue(timestamp_value);
        ws_opex.getRange(targetRow, 6).setValue(payee_value);
        ws_opex.getRange(targetRow, 8).setValue(particulars_value);
        ws_opex.getRange(targetRow, 9).setValue(netAmount_value);
      }

      // 写入CashDR
      const cashdrLastRow = ws_cashdr.getLastRow();
      const newCashdrRow = cashdrLastRow + 1;
      ws_cashdr.getRange(newCashdrRow, 1).setValue(timestamp_value);
      ws_cashdr.getRange(newCashdrRow, 2).setValue(dmsNumber_value);
      ws_cashdr.getRange(newCashdrRow, 3).setValue(payee_value);
      ws_cashdr.getRange(newCashdrRow, 6).setValue(particulars_value);
      ws_cashdr.getRange(newCashdrRow, 9).setValue(grossAmount_value);
      // 计算余额
      const balanceCashDR = ws_cashdr.getRange(10, 10).getValue();
      ws_cashdr.getRange(newCashdrRow, 10).setValue(balanceCashDR - grossAmount_value);
    } else if (category === "Medical Expense") {
      // 找MBAP模板的第一个空行（假设模板行范围为13-49）
      let targetRow = null;
      for (let i = 13; i <= 49; i++) {
        if (ws_mbap.getRange(i, 2).isBlank()) {
          targetRow = i;
          break;
        }
      }
      if (targetRow) {
        // 写入MBAP模板
        ws_mbap.getRange(targetRow, 2).setValue(timestamp_value);
        ws_mbap.getRange(targetRow, 6).setValue(payee_value);
        ws_mbap.getRange(targetRow, 8).setValue(particulars_value);
        ws_mbap.getRange(targetRow, 9).setValue(netAmount_value);
      }

      // 写入CashDR
      const cashdrLastRow = ws_cashdr.getLastRow();
      const newCashdrRow = cashdrLastRow + 1;
      ws_cashdr.getRange(newCashdrRow, 1).setValue(timestamp_value);
      ws_cashdr.getRange(newCashdrRow, 2).setValue(dmsNumber_value);
      ws_cashdr.getRange(newCashdrRow, 3).setValue(payee_value);
      ws_cashdr.getRange(newCashdrRow, 6).setValue(particulars_value);
      ws_cashdr.getRange(newCashdrRow, 9).setValue(grossAmount_value);
      const balanceCashDR = ws_cashdr.getRange(10, 10).getValue();
      ws_cashdr.getRange(newCashdrRow, 10).setValue(balanceCashDR - grossAmount_value);
    }
  });
}

关键修正说明 #

1. 调整变量顺序：先获取主表最后行号，再读取对应单元格值，避免未定义错误
2. 修复循环逻辑：遍历模板指定行范围，判断空行找到写入目标位置，避免覆盖已有数据
3. 补充缺失变量：添加dmsNumber_value、grossAmount_value的获取（需根据实际列调整）
4. 修正数组遍历：通过row[0]获取二维数组中的单元格值，正确判断分类
5. 优化写入逻辑：CashDR部分若下方无干扰文本可直接用getLastRow()，否则可复用模板空行查找逻辑

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