如何通过Google Apps Script跨Sheet匹配值并写入当前日期

核心改进点 #

• 无需解析getA1Notation()，Range对象自带getRow()和getColumn()方法可直接获取行列号，更可靠高效
• 替换固定搜索值为Sheet1的选中单元格内容
• 完整实现Sheet1的「已完成」标记逻辑，以及Sheet2对应匹配项右侧的日期写入功能

完整代码 #

function markComplete() {
  const spreadsheet = SpreadsheetApp.getActive();
  const contentSheet = spreadsheet.getSheetByName("sheet1"); // 内容页
  const indexSheet = spreadsheet.getSheetByName("sheet2"); // 索引页
  
  // 获取Sheet1中选中的单元格（支持单个或多个选中）
  const selectedRanges = contentSheet.getActiveRangeList().getRanges();
  
  // 获取当前日期（格式可自定义，默认使用系统日期格式）
  const currentDate = new Date();
  
  for (const range of selectedRanges) {
    const targetValue = range.getValue().toString().trim(); // 去除首尾空格避免匹配误差
    
    // 1. 在Sheet1标记为已完成：示例为设置灰色背景+添加✅标记，可按需修改
    range.setBackground("#e0e0e0");
    range.setValue(`${targetValue} ✅`);
    
    // 2. 在Sheet2中精准查找匹配值，并在右侧单元格写入日期
    const textFinder = indexSheet.createTextFinder(targetValue)
      .matchEntireCell(true) // 匹配整个单元格内容，避免部分匹配（如"chapter1"不会匹配"chapter12"）
      .ignoreCase(true); // 忽略大小写增强匹配容错性
    
    const matchedRanges = textFinder.findAll();
    
    for (const matchRange of matchedRanges) {
      const row = matchRange.getRow();
      const col = matchRange.getColumn();
      // 右侧单元格为当前列+1
      indexSheet.getRange(row, col + 1).setValue(currentDate);
    }
  }
}

代码说明 #

• 选中单元格处理：使用getActiveRangeList()支持多选场景，逐个处理每个选中的单元格
• 已完成标记：示例采用灰色背景加✅符号，你可根据需求修改为其他样式（如修改字体颜色、添加单元格备注等）
• TextFinder优化：添加matchEntireCell(true)和ignoreCase(true)确保匹配精准度
• 日期写入：通过getRow()和getColumn()直接定位匹配单元格，右侧单元格直接用col + 1获取，无需解析A1格式字符串

按钮绑定步骤 #

1. 打开谷歌表格，点击顶部菜单「扩展程序」→「Apps 脚本」
2. 将上述代码粘贴到脚本编辑器，保存项目
3. 返回表格，点击「插入」→「绘图」，绘制按钮或插入形状
4. 点击绘制好的按钮，选择「分配脚本」，输入函数名markComplete并确认

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