如何修复Google Apps Script中行数不匹配的范围错误

问题原因 #

报错的核心是output数组的行数和目标写入单元格范围的行数不匹配。原代码仅在P列（x[10]）或AA列（x[21]）有值且匹配到价格时，才向output添加元素；若单元格为空，该行不会生成任何元素，导致output长度小于lr1 - 1（目标范围的行数），触发行列数不匹配的错误。

解决方案 #

确保遍历data1的每一行时，无论是否匹配到价格，都要向output数组添加一个元素（空值或计算后的价格），保证output长度与data1长度完全一致（即lr1 - 1）。

修改后的完整代码 #

function updateQuotationPrice() { 
  
  var ss1 = SpreadsheetApp.openById("1UMO4Fxaz1hqjnUx9rPpVM0Z03B7uyfynvV5enJz8ZmU"); //BLOCK ORDERS Update
  var ss2 = SpreadsheetApp.openById("10tb0zE_8i849T-hL6mU-Pw_4V-aMzNZJGNOFYG1F3qk"); //Quotation
  var ss3 = SpreadsheetApp.openById("1jbG_PLWU_eXfQTkwUn1krtYfzniw7HwnwnunhohzL60"); //PRODUCTS
  var svar = SpreadsheetApp.openById('1jbG_PLWU_eXfQTkwUn1krtYfzniw7HwnwnunhohzL60');
  var shvar = svar.getSheetByName("VARIABLES");
  // 获取附加费用变量
  const ster13N = shvar.getRange(3,18,1,1).getValue();
  const euro13N = shvar.getRange(3,19,1,1).getValue();

 // 提取BLOCK ORDERS数据（优化范围，避免空行）
  var lr1 = ss1.getSheetByName("BLOCK ORDERS").getLastRow();
  var data1 = ss1.getSheetByName("BLOCK ORDERS").getRange(2, 6, lr1 - 1, 61).getValues();
  
// 获取QUOTATIONS所有工作表名称
  var sheetNames2 = [];
  ss2.getSheets().forEach(x => sheetNames2.push(x.getName()));

  // 预加载PRODUCTS数据，避免循环内重复调用提升性能
  const productsData = ss3.getSheetByName("PRODUCTS").getDataRange().getValues();

  // 处理P列数据，写入AC列
  var output = [];
  data1.forEach((x,i) => {
    var zoneSurcharge = x[58];
    var price = ""; // 默认空值

    if (x[10] !== "") {
      if (sheetNames2.includes(x[0])) {
        const quoteSheetData = ss2.getSheetByName(x[0]).getDataRange().getValues();
        const matchedRow = quoteSheetData.find(y => y.includes(x[10]));
        if (matchedRow) {
          price = x[9] == "EURO" ? matchedRow[3] + zoneSurcharge : matchedRow[2] + zoneSurcharge;
        }
      } else {
        const matchedRow = productsData.find(y => y.includes(x[10]));
        if (matchedRow) {
          if (x[9] == "EURO") {
            price = x[12] == "13 Newton" ? matchedRow[3] + zoneSurcharge + euro13N : matchedRow[3] + zoneSurcharge;
          } else {
            price = x[12] == "13 Newton" ? matchedRow[2] + zoneSurcharge + ster13N : matchedRow[2] + zoneSurcharge;
          }
        }
      }
    }
    output.push([price]); // 每行必推元素，保证数组长度匹配
  });
  ss1.getSheetByName("BLOCK ORDERS").getRange(2, 26, output.length, 1).setValues(output);

  // 处理AA列数据，写入AF列
  var output = [];
  data1.forEach(x => {
    var zoneSurcharge = x[58];
    var price = ""; // 默认空值

    if (x[21] !== "") {
      if (sheetNames2.includes(x[0])) {
        const quoteSheetData = ss2.getSheetByName(x[0]).getDataRange().getValues();
        const matchedRow = quoteSheetData.find(y => y.includes(x[21]));
        if (matchedRow) {
          price = x[9] == "EURO" ? matchedRow[3] + zoneSurcharge : matchedRow[2] + zoneSurcharge;
        }
      } else {
        const matchedRow = productsData.find(y => y.includes(x[21]));
        if (matchedRow) {
          if (x[9] == "EURO") {
            price = x[12] == "13 Newton" ? matchedRow[3] + zoneSurcharge + euro13N : matchedRow[3] + zoneSurcharge;
          } else {
            price = x[12] == "13 Newton" ? matchedRow[2] + zoneSurcharge + ster13N : matchedRow[2] + zoneSurcharge;
          }
        }
      }
    }
    output.push([price]); // 每行必推元素，保证数组长度匹配
  });
  
  ss1.getSheetByName("BLOCK ORDERS").getRange(2, 29, output.length, 1).setValues(output);
 }

额外优化说明 #

1. 预加载productsData，避免循环内重复调用getDataRange()，减少API调用次数提升效率；
2. 使用find()替代filter()[0]，语义更清晰且性能更优；
3. 调整data1的获取范围为lr1 - 1行，避免原代码中从第2行取lr1行导致的多余空行；
4. 写入范围时使用output.length代替lr1 - 1，进一步规避行列数不匹配风险。

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