Google Script优化：表单定性回答转量化分数及自动触发

原脚本的问题分析 #

• 行索引错误：部分条件下将分数写入i+1行，导致分数与原回答错位
• 列范围错误：仅遍历J列及以后的列，未覆盖需要处理的D(3)、F(5)、H(7)、J(9)、L(11)列
• 终止逻辑不合理：仅因J列空就终止循环，忽略其他列的有效数据
• 重复判断效率低：多个独立if语句可替换为映射对象简化逻辑
• 缺少自动触发机制：未绑定表单提交事件，无法自动处理新响应

修正后的脚本 #

手动运行的量化处理脚本 #

function scoreResponses() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const data = sheet.getDataRange().getValues();
  // 定义定性回答到分数的映射
  const scoreMap = {
    "Strongly Agree": 3,
    "Agree": 2,
    "Somewhat Agree": 1,
    "Unsure / Unknown": 0,
    "Somewhat Disagree": -1,
    "Disagree": -2,
    "Strongly Disagree": -3
  };
  // 需要处理的列（D、F、H、J、L，对应0索引3、5、7、9、11）
  const targetCols = [3, 5, 7, 9, 11];

  // 从第2行开始遍历（假设第1行是表头）
  for (let i = 1; i < data.length; i++) {
    targetCols.forEach(col => {
      const response = data[i][col];
      // 仅当有回答且对应列的右侧单元格为空时写入分数（避免重复处理）
      if (response && sheet.getRange(i + 1, col + 2).getValue() === "") {
        const score = scoreMap[response] || "";
        sheet.getRange(i + 1, col + 2).setValue(score);
      }
    });
  }
}

自动处理新提交的脚本 #

添加表单提交触发函数，仅处理最新的一行响应：

function onFormSubmit(e) {
  const sheet = e.source.getActiveSheet();
  const row = e.range.getRow();
  const scoreMap = {
    "Strongly Agree": 3,
    "Agree": 2,
    "Somewhat Agree": 1,
    "Unsure / Unknown": 0,
    "Somewhat Disagree": -1,
    "Disagree": -2,
    "Strongly Disagree": -3
  };
  const targetCols = [3, 5, 7, 9, 11]; // D、F、H、J、L列的0索引

  targetCols.forEach(col => {
    const response = sheet.getRange(row, col + 1).getValue();
    const score = scoreMap[response] || "";
    sheet.getRange(row, col + 2).setValue(score);
  });
}

设置自动触发的步骤 #

1. 打开脚本编辑器（工具 > 脚本编辑器）
2. 点击左侧菜单栏的「触发器」图标（时钟形状）
3. 点击「添加触发器」按钮
4. 配置触发器：
   • 选择函数：onFormSubmit
   • 选择部署类型：「事件源」选「电子表格」
   • 选择事件类型：「表单提交」
   • 点击「保存」，授权脚本所需权限

脚本说明 #

• 分数会写入原回答列的右侧相邻单元格（如D列回答的分数写入E列，F列写入G列等），保留原回答存档
• 手动运行scoreResponses时会跳过已处理过的行（右侧单元格有值则不重复写入）
• 自动触发函数onFormSubmit会在新表单提交后立即处理该行数据

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