如何将Google Form响应编号填入响应Sheet并解决同步问题？

问题根源 #

时间戳匹配返回-1，核心原因是表单响应的时间戳与Sheet存储的时间戳在格式/精度上不统一：

• 表单getTimestamp()返回的是完整Date对象（含毫秒），而Sheet中的时间可能被格式化截断毫秒，或存储为文本格式而非日期对象
• 直接用indexOf匹配原始日期对象，会因对象引用不同导致匹配失败

实现步骤与代码 #

以下是基于Google Apps Script的可靠解决方案，通过统一时间戳精度、构建映射关系来匹配行：

1. 基础配置 #

替换代码中的表单ID、SheetID及目标列（示例中写入B列）：

function syncResponseNumbers() {
  // 替换为你的表单ID和Sheet信息
  const FORM_ID = "你的Google表单ID";
  const SHEET_ID = "你的Google表格ID";
  const RESPONSE_SHEET_NAME = "表单响应";
  const TARGET_COLUMN = 2; // 要写入响应编号的列（B列=2）

  // 获取表单和Sheet对象
  const form = FormApp.openById(FORM_ID);
  const sheet = SpreadsheetApp.openById(SHEET_ID).getSheetByName(RESPONSE_SHEET_NAME);
  if (!sheet) throw new Error("未找到指定的响应Sheet");

  // 获取所有表单响应（按提交时间排序）
  const responses = form.getResponses();
  if (responses.length === 0) {
    console.log("无表单响应数据");
    return;
  }

  // 构建「时间戳（去毫秒）→ 对应Sheet行号」的映射（兼容重复时间戳）
  const timestampRowMap = {};
  const dataRange = sheet.getRange(2, 1, sheet.getLastRow() - 1, 1); // 第1列是时间戳，从第2行开始跳过表头
  const sheetTimestamps = dataRange.getValues();
  
  sheetTimestamps.forEach((row, index) => {
    // 将Sheet中的时间转换为统一格式：去毫秒的时间戳
    const sheetTs = new Date(row[0]).setMilliseconds(0);
    const rowNumber = index + 2; // 转换为实际Sheet行号
    
    if (!timestampRowMap[sheetTs]) {
      timestampRowMap[sheetTs] = [];
    }
    timestampRowMap[sheetTs].push(rowNumber);
  });

  // 遍历响应，匹配行并写入响应编号
  responses.forEach((response, idx) => {
    // 响应编号：可选用「提交序号（从1开始）」或「响应唯一ID」
    const responseNumber = idx + 1; 
    // const responseNumber = response.getResponseId(); // 若需要唯一ID则启用此行
    
    // 统一响应时间戳精度（去毫秒）
    const responseTs = response.getTimestamp().setMilliseconds(0);
    
    // 查找匹配的行号
    const targetRows = timestampRowMap[responseTs];
    if (targetRows && targetRows.length > 0) {
      // 若有重复时间戳，按顺序匹配未填充的行
      const targetRow = targetRows.shift();
      sheet.getRange(targetRow, TARGET_COLUMN).setValue(responseNumber);
      console.log(`已为响应${responseNumber}填充至行${targetRow}`);
    } else {
      console.log(`未找到响应${responseNumber}对应的Sheet行，时间戳：${new Date(responseTs)}`);
    }
  });

  console.log("响应编号同步完成");
}

2. 关键优化点 #

• 统一时间精度：通过setMilliseconds(0)去掉毫秒，避免因精度差异导致匹配失败
• 兼容重复时间戳：用数组存储同一时间戳对应的所有行号，按提交顺序依次匹配
• 灵活选择编号类型：可切换使用「提交序号」或表单提供的getResponseId()唯一标识

注意事项 #

• 先将Sheet中的时间列设置为日期时间格式，避免文本格式导致转换失败
• 测试时可先注释写入逻辑，用console.log验证匹配结果，再执行写入
• 若Sheet中有空行，需先清理空行再执行脚本，避免无效匹配

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