Google Apps Script问题：弹窗确认后deleteExam函数无法触发

我正在开发一款Google Apps Script，用于在申请人未通过考试时自动删除其答卷。当前使用的脚本如下：

function onEdit1(e) {
  const sh=e.range.getSheet();
  if(sh.getName()=='{I} Apps' && e.range.columnStart==13 && e.value=='TRUE') {
    var resp=SpreadsheetApp.getUi().alert('Did the applicant pass their exam?', SpreadsheetApp.getUi().ButtonSet.YES_NO);
    if(resp==SpreadsheetApp.getUi().Button.YES) {
      var resp=SpreadsheetApp.getUi().alert('Send out the appropriate letter so they can do their practical', SpreadsheetApp.getUi().ButtonSet.OK);
      if(resp==SpreadsheetApp.getUI().Button.OK){
        return;
      }
    }else{
      var resp=SpreadsheetApp.getUi().alert('By pressing OK, you confirm the applicant failed their exam, that you sent the correct letter and that their previous results can be removed.', SpreadsheetApp.getUi().ButtonSet.OK_CANCEL);
      if(resp==SpreadsheetApp.getUI().Button.OK){
        deleteExam();
        return;
    }
     
      return;
    }
  }
}

function deleteExam(){
    var sheet = SpreadsheetApp.getActive().getSheetByName('{I} Exams')
    var rows = sheet.getDataRange();
    var numRows = rows.getNumRows();
    var values = rows.getValues();

    var rowsDeleted = 0;
    for (var i = 0; i <= numRows - 1; i++) {
    var row = values[i];
    if (row[20] == 'delete') { // This searches all cells in columns A (change to row[1] for columns B and so on) and deletes row if cell has value 'delete'.
      sheet.deleteRow((parseInt(i)+1) - rowsDeleted);
      rowsDeleted++;
    }
  }
}

相关说明：

• {I} Apps：概览标签页，勾选复选框可成功触发弹窗
• {I} Exams：表单提交标签页

目前各功能单独运行正常：弹窗可正常弹出，手动运行deleteExam函数也能正常工作。已尝试以下方案但均无效：

1. 使用触发器触发deleteExam函数
2. 将deleteExam的代码直接写入确认OK的判断分支中
3. 将两个函数放在不同脚本文件中
4. 反复核对deleteExam中的列号和名称引用

请问如何修改才能让点击确认弹窗的OK按钮后，deleteExam函数正常执行？

核心问题定位 #

代码中存在方法名大小写错误：Google Apps Script的弹窗相关方法是SpreadsheetApp.getUi()（小写ui），但你在两处判断弹窗返回值时错误使用了SpreadsheetApp.getUI()（大写UI），这会导致条件判断永远不成立，因此deleteExam()从未被调用。

修改后的完整代码 #

function onEdit1(e) {
  const sh = e.range.getSheet();
  // 检查触发条件：指定工作表、第13列（M列）、复选框被勾选
  if (sh.getName() === '{I} Apps' && e.range.columnStart === 13 && e.value === 'TRUE') {
    const passResp = SpreadsheetApp.getUi().alert('Did the applicant pass their exam?', SpreadsheetApp.getUi().ButtonSet.YES_NO);
    
    if (passResp === SpreadsheetApp.getUi().Button.YES) {
      SpreadsheetApp.getUi().alert('Send out the appropriate letter so they can do their practical', SpreadsheetApp.getUi().ButtonSet.OK);
      return;
    } else {
      const deleteResp = SpreadsheetApp.getUi().alert(
        'By pressing OK, you confirm the applicant failed their exam, that you sent the correct letter and that their previous results can be removed.',
        SpreadsheetApp.getUi().ButtonSet.OK_CANCEL
      );
      // 修正getUi()大小写，确保判断生效
      if (deleteResp === SpreadsheetApp.getUi().Button.OK) {
        deleteExam();
        return;
      }
    }
  }
}

function deleteExam() {
  const sheet = SpreadsheetApp.getActive().getSheetByName('{I} Exams');
  const values = sheet.getDataRange().getValues();
  // 优化删除逻辑：从下往上删除，避免索引偏移问题（比原方法更高效）
  for (let i = values.length - 1; i >= 0; i--) {
    const row = values[i];
    // 检查第21列（索引20）是否为'delete'
    if (row[20] === 'delete') {
      sheet.deleteRow(i + 1);
    }
  }
}

额外优化说明 #

1. 变量命名优化：将重复的resp变量改为passResp、deleteResp，提升代码可读性
2. 删除逻辑优化：改为从最后一行往前删除，无需维护rowsDeleted变量，避免因行删除导致的索引混乱，执行效率更高
3. 代码格式统一：使用===严格相等判断，规范缩进，提升代码可维护性

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