Google表单响应自动生成格式化命名工作表脚本报错求助

问题背景 #

我们是学校，需要实现以下需求：

• 将Google表单的「Form Responses 1」响应数据导入新工作表
• 每条响应需满足：
  A) 生成新工作表
  B) 自动套用「John Doe Sheet」的格式
  C) 用问题1的学生姓名命名工作表（格式如「John Doe Response」）

当前使用代码时出现报错：TypeError: Cannot read properties of undefined (reading 'values')，推测和表格名称有关，但缺乏编程基础，需要明确指导。

报错原因 #

1. 触发方式错误：直接在脚本编辑器手动运行onFormSubmit函数时，没有表单提交的事件参数e，导致e.values未定义
2. 模板表名称不匹配：代码里写的是Template，但实际模板是「John Doe Sheet」
3. 字段顺序可能不匹配：代码里的变量顺序需要和「Form Responses 1」的列顺序完全对应

修正后的代码 #

function onFormSubmit(e) {
  // 避免手动运行函数时报错
  if (!e || !e.values) {
    SpreadsheetApp.getUi().alert('请通过表单提交触发此函数，不要手动运行！');
    return;
  }

  // 对应「Form Responses 1」的列顺序：时间戳、姓名、家庭电话、手机、辅导员、邮箱、爱好、活动、交通
  const [timestamp, name, homephone, cellphone, counselor, email, hobbies, activity, transport] = e.values;
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  // 替换为实际模板表名称「John Doe Sheet」
  const template = ss.getSheetByName('John Doe Sheet');
  
  // 检查模板是否存在
  if (!template) {
    SpreadsheetApp.getUi().alert('未找到模板表「John Doe Sheet」，请检查表名！');
    return;
  }

  const sheetname = `${name} Response`;
  let sheet = ss.getSheetByName(sheetname);

  if (!sheet) {
    // 复制模板并设置名称
    sheet = template.copyTo(ss).setName(sheetname);
  }

  // 填充数据到对应单元格
  sheet.getRange('B1').setValue(name);
  sheet.getRange('B2').setValue(homephone);
  sheet.getRange('B3').setValue(cellphone);
  sheet.getRange('B4').setValue(email);
  sheet.getRange('B5').setValue(counselor);

  sheet.getRange('A7').setValue(hobbies);
  sheet.getRange('A11').setValue(activity);

  if (transport === 'Yes') {
    sheet.getRange('C14').check();
  }
}

操作步骤 #

1. 确认模板表名称：确保代码里ss.getSheetByName('John Doe Sheet')的名称和你的模板表完全一致（注意大小写、空格）
2. 设置表单提交触发器：
   • 打开表格的「工具 > 脚本编辑器」
   • 点击左侧闹钟形状的「触发器」图标
   • 点击「添加触发器」，按以下配置设置：
     • 运行函数：onFormSubmit
     • 部署为：Head
     • 事件源：从电子表格
     • 事件类型：表单提交
   • 保存并完成权限授权
3. 验证字段顺序：打开「Form Responses 1」，确认列顺序为：时间戳、姓名、家庭电话、手机、辅导员、邮箱、爱好、活动、交通，若顺序不同，需调整代码里的变量顺序和列对应

注意事项 #

• 不要手动运行onFormSubmit函数，必须通过表单提交触发
• 若学生姓名重复，新提交会覆盖已有同名工作表的数据
• 确保模板表「John Doe Sheet」未被删除或重命名

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