Google Sheets跨标签匹配求和脚本故障求助

原脚本存在的问题 #

第一个 score 函数 #

• 作为自定义函数却直接读取整个 AQ2:AQ 范围，导致所有单元格返回相同总和，无法实现逐行计算
• 内层循环重复读取「Error Categories」表数据，严重拖慢运行效率
• 未处理逗号分隔值前后的空格，易引发匹配失败

第二个 searchCommaSeparatedText 函数 #

• getRange("AQ2:AQ").getValue() 仅读取 AQ2 单个单元格值，无法处理整列数据
• findText 搜索整个数据区域而非目标列，且 getValue(match.row, 3) 是错误调用方式
• 最终将单一总和写入整个 CB2:CB 范围，导致所有单元格值完全相同

方案1：自定义函数（逐单元格计算） #

在目标单元格（如 CB2）输入公式 =CALCULATE_SCORE(AQ2)，下拉即可填充整列：

function CALCULATE_SCORE(inputText) {
  if (!inputText) return 0;
  
  // 获取Error Categories表的A2:C有效数据
  const errorSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Error Categories");
  const errorData = errorSheet.getRange("A2:C").getValues().filter(row => row[0] !== "");
  
  // 创建快速查找映射表，避免重复循环
  const scoreMap = new Map();
  errorData.forEach(row => {
    scoreMap.set(row[0].trim(), row[2]);
  });
  
  // 拆分输入文本并计算匹配值总和
  const items = inputText.split(",").map(item => item.trim());
  let total = 0;
  
  items.forEach(item => {
    if (scoreMap.has(item)) {
      total += scoreMap.get(item);
    }
  });
  
  return total;
}

方案2：批量处理脚本（一次性计算所有行） #

适合大数据量场景，运行后自动填充整个 CB 列：

function BATCH_CALCULATE_SCORES() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const precheckSheet = ss.getSheetByName("Precheck");
  const errorSheet = ss.getSheetByName("Error Categories");
  
  // 获取Precheck表中AQ列的有效数据范围
  const lastRow = precheckSheet.getLastRow();
  const inputRange = precheckSheet.getRange("AQ2:AQ" + lastRow);
  const inputValues = inputRange.getValues();
  
  // 构建Error Categories的映射表
  const errorData = errorSheet.getRange("A2:C").getValues().filter(row => row[0] !== "");
  const scoreMap = new Map();
  errorData.forEach(row => {
    scoreMap.set(row[0].trim(), row[2]);
  });
  
  // 逐行计算总和
  const results = inputValues.map(row => {
    const inputText = row[0];
    if (!inputText) return [0];
    
    const items = inputText.split(",").map(item => item.trim());
    let total = 0;
    items.forEach(item => {
      if (scoreMap.has(item)) {
        total += scoreMap.get(item);
      }
    });
    return [total];
  });
  
  // 将结果写入CB列
  precheckSheet.getRange("CB2:CB" + lastRow).setValues(results);
}

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