使用Google Scripts配合Sheets实现单元格变更发邮件报错咨询

错误核心原因 #

• 首先sendEmailOnApproval函数调用checkStatusIsApproved时，没有把事件参数e传入，导致checkStatusIsApproved内部的e为undefined，访问e.range直接抛出类型错误，这是你当前报错的直接诱因。
• 除此之外代码还有多个语法、逻辑错误，一并整理修复：
  1. SpreadsheetApp.getActiveSheet.getRange缺少括号，正确写法是SpreadsheetApp.getActiveSheet().getRange
  2. sendEmailByRow函数里先调用了row_values[0]，之后才定义row_values，执行顺序颠倒
  3. getRange的参数使用错误，getRange(row,1,row,5)第三个参数是要获取的行数，填row会导致获取多行数据，应该改为getRange(row,1,1,5)
  4. 多处获取工作表的逻辑混乱，一会用当前激活表一会用指定的Client_Status表，容易出现逻辑偏差
  5. 简单触发器onEdit的权限限制无法调用MailApp.sendEmail接口，需要改为可安装的编辑触发器才有权限发送邮件

修复后完整代码 #

// 注意：需要删除原来的简单onEdit触发器，在脚本编辑器的【编辑-当前项目的触发器】中添加可安装的onEdit触发器，绑定runOnEdit函数，否则无法发送邮件
function runOnEdit(e){
    sendEmailOnApproval(e);
}

function checkStatusIsApproved(e){
  const targetSheet = SpreadsheetApp.getActive().getSheetByName('Client_Status');
  // 先判断编辑的是不是目标表，不是直接返回
  if(e.source.getActiveSheet().getSheetName() !== 'Client_Status') return 0;
  const range = e.range;
  // 判断编辑的列是否覆盖第4列
  if(range.getColumn() <= 4 && range.getLastColumn() >=4){
    const editedRow = range.getRow();
    const status = targetSheet.getRange(editedRow,5).getValue();
    if(status === 'Canceled'){
      return editedRow;
    }
  }
  return 0;
}

function sendEmailOnApproval(e){
  const approvedRow = checkStatusIsApproved(e);
  if(approvedRow <=0) return;
  sendEmailByRow(approvedRow);
}

function sendEmailByRow(row){
  const targetSheet = SpreadsheetApp.getActive().getSheetByName('Client_Status');
  const values = targetSheet.getRange(row,1,1,5).getValues();
  const rowValues = values[0];
  const mailInfo = composeApprovedEmail(rowValues);
  MailApp.sendEmail(mailInfo.email, mailInfo.subject, mailInfo.message);
}

function composeApprovedEmail(rowValues){
  const email = rowValues[0];
  const message = rowValues[1];
  const subject = rowValues[3];
  return {
    email: email,
    subject: subject,
    message: message
  }
}

后续配置注意事项 #

• 代码修复后不要直接手动运行测试，需要先配置可安装触发器：打开脚本编辑器，点击顶部菜单【扩展】-【Apps脚本】，在打开的脚本页左侧点击【触发器】，点击右下角【添加触发器】，选择要运行的函数为runOnEdit，选择事件来源为「电子表格」，选择事件类型为「编辑时」，保存并按照提示授权权限即可。
• 注意确认Client_Status表的列结构和代码逻辑匹配：第1列是收件人邮箱，第2列是邮件正文，第4列是邮件主题，第5列是状态字段。

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