Google Apps Script：动态匹配单元格范围实现数据追加需求

需求概述 #

从pricingWS工作表提取数据追加至takeoffWS工作表，仅当D列1-45行单元格为有效正数时执行追加。当前代码中首尾追加的A7、A8为硬编码，导致所有符合条件的行重复使用该值；需实现：

• 处理D10-D15范围的行时，追加A7和A8的值
• 处理D40-D45范围的行时，追加A36和A38的值

当前代码 #

function saveData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const sheets = ss.getSheets();
  const pricingWS = sheets[1]
  const takeoffWS = sheets[2]

  var dataRange = pricingWS.getRange(1,4,45,1).getValues()  

  var dataArray = []

  debugger

  for (var i = 1; i <= dataRange.length; i++)
  {
    if(dataRange[i - 1]<=0|| /^[a-zA-Z]+$/.test(dataRange[i - 1])){
      continue
    }
    else{ 
      dataArray.push(pricingWS.getRange("A7").getValue())
      dataArray.push(pricingWS.getRange(i, 1).getValue())
      dataArray.push(pricingWS.getRange(i, 2).getValue())
      dataArray.push(pricingWS.getRange(i, 4).getValue())
      dataArray.push(pricingWS.getRange("A8").getValue())
      takeoffWS.appendRow(dataArray)
      dataArray = []
    }   
    
  }
  
}

解决方案代码 #

function saveData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  const pricingWS = sheets[1];
  const takeoffWS = sheets[2];

  // 批量读取所需数据，减少重复的getRange调用，提升性能
  const dValues = pricingWS.getRange(1, 4, 45, 1).getValues().flat();
  const a7 = pricingWS.getRange("A7").getValue();
  const a8 = pricingWS.getRange("A8").getValue();
  const a36 = pricingWS.getRange("A36").getValue();
  const a38 = pricingWS.getRange("A38").getValue();
  const aValues = pricingWS.getRange(1, 1, 45, 1).getValues().flat();
  const bValues = pricingWS.getRange(1, 2, 45, 1).getValues().flat();

  // 定义行区间与对应首尾值的映射，方便后续扩展
  const rangeMappings = [
    { start: 10, end: 15, firstVal: a7, lastVal: a8 },
    { start: 40, end: 45, firstVal: a36, lastVal: a38 }
  ];

  for (let i = 1; i <= dValues.length; i++) {
    const dValue = dValues[i - 1];
    
    // 验证是否为有效正数：排除非数字、≤0、纯字母字符串
    if (isNaN(dValue) || dValue <= 0 || (typeof dValue === 'string' && /^[a-zA-Z]+$/.test(dValue))) {
      continue;
    }

    // 匹配当前行对应的首尾值
    let firstVal = "";
    let lastVal = "";
    const matchedRule = rangeMappings.find(rule => i >= rule.start && i <= rule.end);
    if (matchedRule) {
      firstVal = matchedRule.firstVal;
      lastVal = matchedRule.lastVal;
    }
    // 若需跳过未匹配区间的行，可取消注释下面的continue
    // else continue;

    // 构造数据并追加到目标工作表
    takeoffWS.appendRow([
      firstVal,
      aValues[i - 1],
      bValues[i - 1],
      dValue,
      lastVal
    ]);
  }
}

关键修改说明 #

1. 批量读取数据：一次性读取所有需要的列数据和固定单元格值，避免循环中反复调用getRange，大幅提升脚本执行效率。
2. 区间映射规则：用数组存储行区间与对应首尾值的关联关系，后续新增区间只需添加新的规则对象，代码维护更便捷。
3. 动态匹配逻辑：通过find方法定位当前行所属的区间，自动获取对应的首尾值；未匹配到区间的行可设置默认值或直接跳过（根据需求调整）。
4. 优化有效性判断：完善了有效数字的校验逻辑，覆盖非数字、非正数、纯字母字符串等无效情况。

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