编写Google Apps Script：匹配表格内容清除目标表格指定行

需求：当学生姓名存在于spreadsheetA的「Not returning」工作表A列时，移除spreadsheetB的「Master」工作表中该学生及其兄弟姐妹所在行的B至I列内容。具体规则：遍历spreadsheetB每一行，若spreadsheetA的A列任意姓名与当前行的C、E、G或I列匹配，则清空该行B至I的内容。

原代码存在的问题 #

• 变量名错误：使用了未定义的ss1、ss2，应对应ssA、ssB
• 循环逻辑错误：i > ssA.getLastRow()的反向判断会直接跳过循环，且未遍历spreadsheetB的所有行
• 匹配范围错误：仅对比了单个单元格，未覆盖需求指定的C/E/G/I列
• 清空范围错误：仅清空了I列单个单元格，未覆盖B至I列
• 性能问题：循环中反复调用getRange()、getValue()，会大幅降低脚本执行效率

优化修正后的代码 #

function removeNotReturning() {
  // 1. 获取spreadsheetA的待移除姓名列表，转为Set提升查询效率
  const ssA = SpreadsheetApp.openById("spreadsheetA").getSheetByName("Not returning");
  const notReturningNames = new Set(
    ssA.getRange(2, 1, ssA.getLastRow() - 1, 1) // 选取A2到最后一行的有效数据
      .getValues()
      .flat() // 二维数组转一维
      .filter(name => name !== "") // 过滤空值避免误匹配
  );

  // 2. 获取spreadsheetB的目标数据范围
  const ssB = SpreadsheetApp.openById("spreadsheetB").getSheetByName("Master");
  const dataRange = ssB.getRange(2, 2, ssB.getLastRow() - 1, 8); // B2:I区域（共8列）
  const data = dataRange.getValues();

  // 3. 遍历数据，处理需清空的行
  const updatedData = data.map(row => {
    // 检查当前行的C/E/G/I列（对应数组索引1、3、5、7）是否在待移除列表中
    const hasMatch = [row[1], row[3], row[5], row[7]].some(name => notReturningNames.has(name));
    // 匹配则清空整行B-I，否则保留原数据
    return hasMatch ? Array(8).fill("") : row;
  });

  // 4. 批量写入更新后的数据，减少API调用次数
  dataRange.setValues(updatedData);
}

优化说明 #

• 用Set存储待移除姓名：将查询时间复杂度从O(n)降至O(1)，大幅提升匹配效率
• 批量读写数据：仅调用2次getValues()和1次setValues()，避免循环中频繁调用Google Sheets API，提升脚本执行速度
• 精准匹配指定列：明确覆盖需求要求的C、E、G、I列
• 完整清空目标范围：匹配行直接填充8个空字符串，覆盖B至I整列区域
• 过滤空值：避免spreadsheetA中的空白行干扰匹配逻辑

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