仅当单元格存在值时触发Alert弹窗问题排查及代码求助

修改后的代码 #

function sendMailEdit(e) {
  // 单元格为空或删除值时直接退出，不触发任何操作
  if (!e.value || e.value.trim() === '') {
    return;
  }

  // 限定仅在"Response"工作表中触发逻辑
  const activeSheet = e.source.getActiveSheet();
  if (activeSheet.getName() !== 'Response') {
    return;
  }

  // 判断是否符合触发邮件的规则：目标列（第5列）或指定状态值
  const isTargetColumn = e.range.columnStart === 5;
  const isTargetValue = ['Approved', 'Rejected', 'Duplicate'].includes(e.value);
  if (!isTargetColumn && !isTargetValue) {
    return;
  }

  // 满足条件后才弹出确认框
  const ui = SpreadsheetApp.getUi();
  const response = ui.alert("Do you want to send an email?", ui.ButtonSet.YES_NO);
  
  if (response === ui.Button.YES) {
    // 获取整行数据（取6列，对应原代码中Status所在的第6列）
    const rowData = activeSheet.getRange(e.range.rowStart, 1, 1, 6).getValues()[0];
    
    const employeeName = rowData[2];
    const reviewer = rowData[4];
    const status = rowData[5];
    const ccmail = ''; // 请在此填写CC收件邮箱，例如'xxx@example.com'

    // 构建HTML邮件内容
    const emailBody = `
      <html>
        <head>
          <style>table, th, tr{border: 2px solid black;}</style>
        </head>
        <body>
          <tr style="background-color:#d3ecdc;">
            <td width=500 height=auto colspan=6>
              <br>Hi ${employeeName},<br><br>
              Your suggestion has been ${status} by ${reviewer}<br>
              You can check the comments here<br><br>
              Regards,<br>
              Rajanee<br><br>
            </td>
          </tr>
        </body>
      </html>
    `;

    // 发送邮件
    MailApp.sendEmail({
      to: employeeName,
      subject: 'Idea Status!',
      htmlBody: emailBody,
      name: 'Ideas',
      cc: ccmail
    });
  }
}

关键修改说明 #

• 前置空值判断：将单元格空值/删除操作的判断放在最开头，直接终止流程，避免触发弹窗
• 限定工作表范围：仅在指定的"Response"表中执行逻辑，防止其他工作表误触发
• 调整逻辑顺序：先验证触发规则，再弹出确认框，减少不必要的交互
• 修正数据索引：原代码中访问第6列数据却只取了5列范围，修改为取6列避免索引越界报错
• 优化代码可读性：用数组includes简化值判断，用模板字符串重构邮件内容

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