Google Apps Script中用FindAll获取匹配行A1Notations及同行列值方法

云上老周

2026-10-1

修改后可直接运行的代码

var searchTerm = "Changeme"; // 替换为你要搜索的关键词
var sourceSheetName = "Sheet1"; // 替换为数据源表的表名
var targetSheetName = "匹配结果"; // 替换为要写入的目标表名

var ss = SpreadsheetApp.getActiveSpreadsheet();
var dataSheet = ss.getSheetByName(sourceSheetName);
// 仅获取H列的有效数据范围，限制搜索仅在H列执行
var searchRange = dataSheet.getRange("H1:H" + dataSheet.getLastRow());
var completeSearch = searchRange.createTextFinder(searchTerm).findAll();

// 存储匹配到的A/B/C列数据
var resultList = [];
for (var i = 0; i < completeSearch.length; i++) {
  var range = completeSearch[i];
  var currentRow = range.getRow();
  // 提取当前行A、B、C三列的值
  var colA = dataSheet.getRange(currentRow, 1).getValue();
  var colB = dataSheet.getRange(currentRow, 2).getValue();
  var colC = dataSheet.getRange(currentRow, 3).getValue();
  resultList.push([colA, colB, colC]);
}

// 写入目标工作表，不存在则自动创建
var targetSheet = ss.getSheetByName(targetSheetName) || ss.insertSheet(targetSheetName);
// 清空目标表原有内容，避免旧数据干扰
targetSheet.clearContents();
// 批量写入所有匹配结果
if (resultList.length > 0) {
  targetSheet.getRange(1, 1, resultList.length, 3).setValues(resultList);
}