Google Apps Script需求：移除Google表单中ID不匹配的提交响应

需求说明 #

需要实现以下功能：

• 读取Google Sheets中存储ID的工作表（ID位于第一列）
• 用户提交Google Form时，若填写的ID（表单首个问题）与工作表中的ID不匹配，移除该条响应，仅保留ID匹配的提交内容

原代码核心问题 #

• 逻辑错误：最后将不匹配的响应重新追加回表单响应表，完全违背需求
• 效率低下：读取整列A:A获取ID，包含大量空行
• 未利用表单提交事件：只能手动运行，无法自动处理新提交的响应
• 列索引错误：误将表单响应表的第一列（提交时间戳）当作用户填写的ID
• 数据类型未统一：若ID是数字，表单提交的可能是字符串，导致匹配失败

修正后的代码 #

1. 实时处理表单提交（推荐，绑定触发器自动运行） #

// 绑定Google表单的「表单提交」触发器，每次用户提交自动执行
function onFormSubmit(e) {
  try {
    const SPREADSHEET_ID = '1sh5dcQJPRJtbFpe7qVjpo7yIUwi5_mCGKrAnhWm-Hng';
    const spreadsheet = SpreadsheetApp.openById(SPREADSHEET_ID);
    
    // 获取存储ID的工作表（假设为第一个工作表，可根据实际修改索引）
    const idsSheet = spreadsheet.getSheets()[0];
    if (!idsSheet) throw new Error("未找到存储ID的工作表");
    
    // 获取表单响应工作表
    const formResponsesSheet = spreadsheet.getSheetByName("Form Responses");
    if (!formResponsesSheet) throw new Error("未找到表单响应工作表");
    
    // 获取有效ID列表（仅读取有数据的行，统一转为字符串避免类型不匹配）
    const idsData = idsSheet.getRange(1, 1, idsSheet.getLastRow(), 1).getValues();
    const validIds = idsData.flat().filter(id => id !== "").map(id => String(id));
    
    // 获取本次提交的ID（表单第一个问题的响应）
    const submittedId = String(e.response.getItemResponses()[0].getResponse());
    
    // 获取本次提交在响应表中的行号
    const responseRow = e.range.getRow();
    
    // 检查ID是否匹配，不匹配则删除该行
    if (!validIds.includes(submittedId)) {
      formResponsesSheet.deleteRow(responseRow);
      Logger.log(`删除不匹配ID的响应：${submittedId}`);
    } else {
      Logger.log(`保留匹配ID的响应：${submittedId}`);
    }
  } catch (error) {
    Logger.log(`处理失败：${error.message}`);
  }
}

2. 批量清理历史不匹配响应 #

// 批量清理现有响应表中ID不匹配的记录
function batchRemoveUnmatchedResponses() {
  try {
    const SPREADSHEET_ID = '1sh5dcQJPRJtbFpe7qVjpo7yIUwi5_mCGKrAnhWm-Hng';
    const spreadsheet = SpreadsheetApp.openById(SPREADSHEET_ID);
    
    const idsSheet = spreadsheet.getSheets()[0];
    if (!idsSheet) throw new Error("未找到存储ID的工作表");
    
    const formResponsesSheet = spreadsheet.getSheetByName("Form Responses");
    if (!formResponsesSheet) throw new Error("未找到表单响应工作表");
    
    // 使用Set存储有效ID，大幅提高查询效率，同时统一转为字符串
    const idsData = idsSheet.getRange(1, 1, idsSheet.getLastRow(), 1).getValues();
    const validIds = new Set(idsData.flat().filter(id => id !== "").map(id => String(id)));
    
    // 获取响应表数据（跳过第一行表头）
    const lastRow = formResponsesSheet.getLastRow();
    const lastCol = formResponsesSheet.getLastColumn();
    const responsesData = formResponsesSheet.getRange(2, 1, lastRow - 1, lastCol).getValues();
    
    // 筛选匹配的响应记录
    const matchedResponses = responsesData.filter(row => {
      const responseId = String(row[1]); // 响应表第二列是用户填写的ID（第一列为时间戳）
      return validIds.has(responseId);
    });
    
    // 清空响应表（保留表头）
    formResponsesSheet.getRange(2, 1, lastRow - 1, lastCol).clearContent();
    
    // 写入匹配的响应记录
    if (matchedResponses.length > 0) {
      formResponsesSheet.getRange(2, 1, matchedResponses.length, matchedResponses[0].length).setValues(matchedResponses);
    }
    
    Logger.log(`批量清理完成，保留${matchedResponses.length}条匹配记录`);
  } catch (error) {
    Logger.log(`批量清理失败：${error.message}`);
  }
}

关键修正说明 #

• 核心逻辑修复：删除原代码中追加不匹配响应的错误代码，改为直接删除不匹配记录
• 数据类型统一：将所有ID转为字符串，避免数字/字符串类型差异导致的匹配失败
• 效率优化：使用Set存储有效ID，查询效率远高于数组includes；仅读取有数据的单元格范围，避免空行浪费资源
• 事件驱动处理：通过表单提交事件onFormSubmit实现实时自动处理，无需手动运行
• 列索引修正：明确表单响应表第一列为时间戳，用户填写的ID位于第二列，修正原代码的索引错误

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