Google Apps Script数据迁移与自动邮件功能故障排查

问题诊断 #

1. 核心函数未调用：populateFormRow定义了正确的列映射规则，但onFormSubmit中从未执行该函数，导致调试时变量均为undefined，数据也未按预期迁移。
2. 数据写入逻辑错误：onFormSubmit直接将表单数据从Creator表的B列开始写入，完全忽略预设的列映射规则（如Form的B列对应Creator的A列），导致数据错位。
3. 空行查找函数缺陷：findNextBlankRow从行索引0开始检查，且仅通过第一列是否为空判断空行，容易误判非空行，返回错误行号。
4. 邮件函数变量错误：sendEmailOnFormSubmitOrEdit存在未定义变量（如columnK、dueDateL、senderA），且表单提交时错误读取「Form Responses 2」表数据，应读取「Creator」表的对应行。
5. 函数嵌套错误：createTriggers被嵌套在sendEmailOnFormSubmitOrEdit内部，无法独立执行，导致触发器创建失败。

修正后的完整代码 #

// 表单数据到Creator表的列映射函数
function populateFormRow(sheet, startingRow, data) {
  // data为表单提交数据（去掉时间戳后的数组）
  if (data.length < 9) {
    Logger.log("数据数组元素不足，无法完成映射");
    return;
  }

  // 提取表单数据（对应Form Responses 2的B-J列）
  var bValue = data[0]; // Form B → Creator A
  var cValue = data[1]; // Form C → Creator C
  var dValue = data[2]; // Form D → Creator E
  var eValue = data[3]; // Form E → Creator F
  var fValue = data[4]; // Form F → Creator G
  var gValue = data[5]; // Form G → Creator H
  var hValue = data[6]; // Form H → Creator K
  var iValue = data[7]; // Form I → Creator L
  var jValue = data[8]; // Form J → Creator M

  // 按映射规则写入Creator表
  sheet.getRange(startingRow, 1).setValue(bValue);
  sheet.getRange(startingRow, 3).setValue(cValue);
  sheet.getRange(startingRow, 5).setValue(dValue);
  sheet.getRange(startingRow, 6).setValue(eValue);
  sheet.getRange(startingRow, 7).setValue(fValue);
  sheet.getRange(startingRow, 8).setValue(gValue);
  sheet.getRange(startingRow, 11).setValue(hValue);
  sheet.getRange(startingRow, 12).setValue(iValue);
  sheet.getRange(startingRow, 13).setValue(jValue);
}

// 查找Creator表的下一个空行（优化版）
function findNextBlankRow(sheet) {
  var lastRow = sheet.getLastRow();
  // 表为空时返回第2行（假设第1行是表头）
  if (lastRow === 0) return 2;
  
  // 检查最后一行是否为空，为空则返回该行，否则返回lastRow+1
  var lastRowData = sheet.getRange(lastRow, 1, 1, sheet.getLastColumn()).getValues()[0];
  var isLastRowEmpty = lastRowData.every(cell => cell === "");
  
  return isLastRowEmpty ? lastRow : lastRow + 1;
}

// 表单提交触发的数据迁移函数
function onFormSubmit(e) {
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var creatorSheet = ss.getSheetByName("Creator");
    if (!creatorSheet) {
      Logger.log("错误：未找到「Creator」工作表");
      return;
    }

    // 获取表单提交数据（去掉第一个时间戳元素）
    var formData = e.values.slice(1);
    Logger.log("表单提交数据：" + formData);

    // 获取下一个空行
    var nextBlankRow = findNextBlankRow(creatorSheet);
    Logger.log("将写入行号：" + nextBlankRow);

    // 调用映射函数完成数据迁移
    populateFormRow(creatorSheet, nextBlankRow, formData);
    Logger.log("数据已成功迁移到「Creator」工作表");

    // 提交后直接检查是否需要发邮件
    sendEmailOnFormSubmitOrEdit({range: creatorSheet.getRange(nextBlankRow, 1)});
  } catch (error) {
    Logger.log('onFormSubmit执行错误：' + error.toString());
  }
}

// 编辑或表单提交时触发邮件发送
function sendEmailOnFormSubmitOrEdit(e) {
  var sheet, row, status;
  if (e.range) {
    sheet = e.range.getSheet();
    // 仅处理Creator表的操作
    if (sheet.getName() !== "Creator") return;
    row = e.range.getRow();
    // 跳过表头行
    if (row === 1) return;
    status = sheet.getRange(row, 13).getValue();
  } else {
    Logger.log("事件对象不符合要求，终止执行");
    return;
  }

  if (status === 'Yes') {
    try {
      // 从Creator表读取数据
      var emailB = sheet.getRange(row, 2).getValue();
      var emailD = sheet.getRange(row, 4).getValue();
      var nameD = sheet.getRange(row, 3).getValue();
      var columnI = sheet.getRange(row, 9).getValue();
      var columnJ = sheet.getRange(row, 10).getValue();
      // 格式化金额
      var columnJFormatted = columnJ.toLocaleString('en-US', {style: 'currency', currency: 'USD'});
      var dueDateL = sheet.getRange(row, 12).getValue();
      var senderA = sheet.getRange(row, 1).getValue();

      // 构建邮件内容
      var emailBody = `Dear ${nameD},

We would like to officially offer a contract with the following conditions:

${columnI}

${columnJFormatted}.

The due date for your contract is ${dueDateL}

Please let me know if you have any questions.

Thank you,
${senderA}`;
      var subject = "Contract Offer";
      var recipient = `${emailB},${emailD},craigw@goalbookapp.com`;

      // 发送邮件
      MailApp.sendEmail(recipient, subject, emailBody);
      Logger.log("邮件已成功发送");
    } catch (error) {
      Logger.log('邮件发送错误：' + error.toString());
    }
  }
}

// 创建触发器
function createTriggers() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // 删除现有同名触发器，避免重复创建
  var existingTriggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < existingTriggers.length; i++) {
    var trigger = existingTriggers[i];
    if (trigger.getHandlerFunction() === "sendEmailOnFormSubmitOrEdit" || trigger.getHandlerFunction() === "onFormSubmit") {
      ScriptApp.deleteTrigger(trigger);
    }
  }

  // 创建表单提交触发器
  ScriptApp.newTrigger('onFormSubmit')
      .forSpreadsheet(ss)
      .onFormSubmit()
      .create();
  
  // 创建编辑触发器
  ScriptApp.newTrigger('sendEmailOnFormSubmitOrEdit')
      .forSpreadsheet(ss)
      .onEdit()
      .create();
  
  Logger.log("触发器已成功创建");
}

关键修改点说明 #

1. 调用映射函数：在onFormSubmit中执行populateFormRow，按照预设规则将表单数据写入Creator表对应列，解决数据错位问题。
2. 优化空行查找：findNextBlankRow改为从最后一行反向检查，判断整行是否为空，避免误判空行。
3. 修复邮件函数：修正未定义变量，限制仅处理Creator表的操作，确保读取迁移后的正确数据。
4. 调整函数结构：将createTriggers从嵌套中移出，使其可独立执行，正确创建触发器。
5. 添加错误捕获：在邮件发送逻辑中加入try-catch，避免单个错误导致函数崩溃。

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