求可替代现有公式的脚本：提取同名含指定内容的单元格及批注

以下是替代原公式的脚本，可提取所有符合以下条件的数据及对应单元格批注：

• 匹配A1:A100中的名称（对应H2:H270中的所有同名行）
• 该行中包含B1指定内容的单元格（保留原公式的点号转义逻辑）

function extractMatchingDataWithComments() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet(); // 可改为指定工作表，如ss.getSheetByName("Sheet1")
  
  // 获取输入参数
  const nameRange = sheet.getRange("A1:A100");
  const names = nameRange.getValues().flat().filter(name => name !== ""); // 过滤空名称
  const targetText = sheet.getRange("B1").getValue();
  const escapedText = targetText.replace(/\./g, "\\."); // 转义点号，与原公式逻辑一致
  const dataRange = sheet.getRange("H2:AQ270");
  const dataValues = dataRange.getValues();
  const dataComments = dataRange.getNotes(); // 获取所有单元格批注
  const headers = dataRange.getValues()[0]; // 可选：获取表头用于返回对应列名
  
  const results = [];
  
  // 遍历每个待匹配名称
  names.forEach(targetName => {
    // 遍历数据区域，找到所有同名行
    dataValues.forEach((row, rowIndex) => {
      const currentName = row[0]; // H列对应数组第0位（从0计数）
      if (currentName === targetName) {
        // 检查该行所有单元格是否包含目标文本
        row.forEach((cellValue, colIndex) => {
          if (cellValue && new RegExp(escapedText).test(cellValue.toString())) {
            // 收集结果：匹配名称、对应列名、单元格值、批注
            results.push([
              targetName,
              headers[colIndex],
              cellValue,
              dataComments[rowIndex][colIndex] || "" // 无批注时返回空字符串
            ]);
          }
        });
      }
    });
  });
  
  // 写入结果到工作表（默认从C1开始，可自行修改）
  if (results.length > 0) {
    const resultHeaders = ["匹配名称", "对应列", "单元格值", "批注"];
    sheet.getRange("C1:F1").setValues([resultHeaders]);
    sheet.getRange("C2:F" + (1 + results.length)).setValues(results);
  } else {
    SpreadsheetApp.getUi().alert("未找到符合条件的数据");
  }
}

使用步骤 #

1. 打开目标Google表格，点击顶部菜单栏扩展程序 > Apps 脚本
2. 删除默认代码，粘贴上述脚本
3. 保存项目（可自定义名称），点击运行按钮完成权限授权
4. 运行extractMatchingDataWithComments函数，结果将自动写入C列开始的区域

自定义调整 #

• 如需指定固定工作表，将sheet = ss.getActiveSheet()改为ss.getSheetByName("你的工作表名")
• 修改结果输出位置：调整代码中sheet.getRange("C1:F1")和sheet.getRange("C2:F" + ...)的范围
• 无需返回列名时，删除headers[colIndex]相关代码，同时调整结果数组结构及表头

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