Google Sheets脚本需求：编辑表单时重置审批状态下拉框

我们办公环境使用Google Workspace，通过Google表单收集休假申请，数据同步至Google Sheets。目前已实现通过匹配邮箱统一员工姓名、主管信息等功能，但在Google Apps Script开发中遇到问题：需要实现当员工通过表单的「Edit Response」提交取消休假申请时，自动清空对应行K列（审批状态列，含Data Validation下拉选项）的内容。尝试了多版OnEdit及OnEditCancel脚本均未生效，附上现有代码，请求技术协助。

现有代码文件 #

1. code.gs（onEdit触发器） #

function onEdit(e) {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); 

  // EVENT VARIABLES 
  let row = e.range.getRow(); 
  let col = e.range.getColumn(); 
  let cellValue = sheet.getActiveCell().getValue(); 
  let n = sheet.getRange(row,2).getValue();
  let em = sheet.getRange(row,3).getValue();
  let sd = sheet.getRange(row,5).getValue().toLocaleDateString();
  let ed = sheet.getRange(row,6).getValue().toLocaleDateString();
  let tl = sheet.getRange(row,8).getValue();
  let sem = sheet.getRange(row,10).getValue();
  let rs = sheet.getRange(row,12).getValue()

  let projectName = sheet.getRange(row,1).getValue(); 
  let user = Session.getActiveUser().getEmail();   

  if ( col == 11 && cellValue == "APPROVED") {
      MailApp.sendEmail({
        to: em,
        cc: sem + ',' + 'REDACTED EMAIL',
        subject: 'Your ' + projectName + ' request has been reviewed',
        htmlBody: n + ',' + "<br//>" + "<br//>"+'Your ' + projectName + ' (' + tl + ') scheduled for ' + sd + ' to ' + ed + ' has been <b><u><font color=GREEN>APPROVED</b></u> by ' + user + '.'
  }); 
  }; 
  if ( col == 11 && cellValue == "DENIED") {
      MailApp.sendEmail({
        to: em,
        cc: sem,
        subject: 'Your ' + projectName + ' request has been reviewed',
        htmlBody: n + ',' + "<br//>" + "<br//>"+'Your ' + projectName + ' (' + tl + ') scheduled for ' + sd + ' to ' + ed + ' has been <b><u><font color=RED>DENIED</b></u> by ' + user + '.' + "<br//>" + 'Reason: ' + rs 
  }); 
  }; 
  if ( col == 11 && cellValue == "PENDING") {
      MailApp.sendEmail({
        to: em,
        cc: sem,
        subject: 'Your ' + projectName + ' request has been reviewed',
        htmlBody: n + ',' + "<br//>" + "<br//>"+'Your ' + projectName + ' (' + tl + ') scheduled for ' + sd + ' to ' + ed + ' has been <b><u><font color=BLUE>PENDING</b></u> by ' + user + '.'
  }); 
  }; 
    if ( col == 11 && cellValue == "CANCELED") {
      MailApp.sendEmail({
        to: em,
        cc: sem + ',' + 'REDACTED EMAIL',
        subject: projectName + ' request has been reviewed',
        htmlBody: n + ',' + "<br//>" + "<br//>"+'Your (' + tl + ') scheduled for ' + sd + ' to ' + ed + ' has been <b><u><font color=ORANGE>CANCELED</b></u> by ' + user + '.'
  }); 
  }; 
}

2. SortResponses.gs #

function sortResponses() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("Form Responses 1");
  sheet.sort(5, true);
}

3. Copy.gs #

function Copy() {
  var spreadsheet = SpreadsheetApp.getActive();
  
  // Copy and paste operations
  spreadsheet.getRange('M3:Q500').activate();
  spreadsheet.getRange('M1:Q1').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  
  // Update names based on email addresses
  updateNamesBasedOnEmails();
}

// Function to update names based on email addresses
function updateNamesBasedOnEmails() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 1");
  var arraySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Array");
  
  // Get data from Form Responses 1
  var emailRange = sheet.getRange("C2:C"); // Adjust if necessary
  var emailValues = emailRange.getValues();
  
  // Get data from Array sheet
  var arrayRange = arraySheet.getRange("A:B").getValues();
  var lookupMap = {};
  
  // Create lookup map from Array sheet
  for (var i = 0; i < arrayRange.length; i++) {
    lookupMap[arrayRange[i][0]] = arrayRange[i][1];
  }
  
  // Update column B with names based on emails
  for (var j = 0; j < emailValues.length; j++) {
    var email = emailValues[j][0];
    var name = lookupMap[email] || ""; // Default to empty string if no match
    sheet.getRange(j + 2, 2).setValue(name); // Update column B
  }
}

4. OnEditCancel.gs #

function onFormSubmit(e) {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form Responses 1'); // Adjust if your sheet name differs
  let responses = e.values; // This contains an array of form responses
  
  let row = sheet.getLastRow(); // Get the last row where the form response was added
  
  // Assuming the "Type of Leave" is in column H (8th column in sheet)
  let typeOfLeave = sheet.getRange(row, 8).getValue();

  // Check if the type of leave is 'Cancel'
  if (typeOfLeave == 'Cancel') {
    // Clear the approval status in Column K (11th column)
    sheet.getRange(row, 11).clearContent();
    Logger.log("Leave type 'Cancel' detected, clearing approval status at row " + row);
  }
}

解决方案 #

问题根源 #

原onFormSubmit函数的核心问题是：当用户编辑表单响应时，数据会更新到原有行，而非新增行，所以sheet.getLastRow()会错误地指向表格最后一行，而非被编辑的目标行。此外，简单触发的onEdit无法监测到表单发起的编辑操作，必须使用可安装的表单提交触发器。

修正后的代码 #

替换原OnEditCancel.gs中的onFormSubmit函数为以下代码：

function handleFormSubmit(e) {
  const targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form Responses 1');
  if (!targetSheet) return;

  // 获取被编辑/提交的行号
  const editedRow = e.range.getRow();
  // 获取表单中"休假类型"字段对应的值（对应表格H列，第8列）
  const typeOfLeave = targetSheet.getRange(editedRow, 8).getValue();

  // 检测是否为取消申请
  if (typeOfLeave.trim() === 'Cancel') {
    // 清空K列（第11列）的审批状态，保留数据验证规则
    targetSheet.getRange(editedRow, 11).clearContent();
    Logger.log(`已清空行${editedRow}的审批状态`);
  }
}

关键设置步骤 #

1. 删除原有无效触发器：打开脚本编辑器 → 点击左侧「触发器」图标 → 删除所有与onFormSubmit相关的触发器。
2. 创建可安装表单提交触发器：
   • 在脚本编辑器中点击「编辑」→「当前项目的触发器」
   • 点击「添加触发器」
   • 配置选项：
     • 选择要运行的函数：handleFormSubmit
     • 选择部署类型：「Head」
     • 选择事件源：「表单提交」
     • 选择事件类型：「来自表单」
     • 点击「保存」，按提示完成授权

补充说明 #

• 可安装触发器拥有更高权限，能正常监测表单的新增和编辑操作；
• e.range会直接指向表单操作对应的表格行，无需手动判断行号；
• clearContent()只会清空单元格内容，保留原有的数据验证下拉规则，符合需求。

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