时间戳

首页 > 解决方案 > 电子邮件通知上的单元格引用超出范围

google-apps-script - 电子邮件通知上的单元格引用超出范围

问题描述

我有以下代码，用于在单元格更改时发送电子邮件通知，但它总是返回超出范围的单元格引用（第 5 行，文件“Missing Cuts Report”）。任何人都可以建议我是否需要更改代码，或者是否有办法让它停止失败并仍然通过发送电子邮件通知。我使用的触发器是 OnChange，因为当它在 OnEdit 上时，它返回错误服务，在第 17 行 MailApp.sendEmail(recipients, subject, msgPlain, { htmlBody: body }) 上调用了太多次。任何帮助将不胜感激。

function sendEnailNotification(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var cell = ss.getActiveCell().getA1Notation();
var cellvalue = ss.getActiveCell().getValue().toString();

if(SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName() == "Missing Cuts Report") {
if(cell.indexOf('B')!=-1){
 if(cellvalue > "") {
  //Define Notification Details
      var recipients = "email@email.co.uk;
      var subject = "New Missing Cut Added";
      var body = 'A new line has been added on the Missing Cuts Report on line  <b>' + cell + '</b> - click <a href="' + ss.getUrl() + '">here</a> to view the update';

   var msgPlain = body.replace(/(<([^>]+)>)/ig, ""); // clear html tags for plain mail
    MailApp.sendEmail(recipients, subject, msgPlain, { htmlBody: body });
      }
    }
  }
}

标签： google-apps-scriptemail-notifications

解决方案

嗨，这不是实现您想要的结果的好方法。不确定你在追求什么。但是，我在右大括号后对您的代码进行了一些调整。这不是您原始问题的答案。只是一些提示。

    function sendEmailNotification() {

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var cell = ss.getActiveCell().getA1Notation();
var cellvalue = ss.getActiveCell().getValue().toString();

if(sheet.getName() === "Missing Cuts Report") {
//if(cell.indexOf('B')!== -1){
 if(cellvalue !== "") {
  //Define Notification Details
      var recipients = "email@address.org";
      var subject = "New Missing Cut Added";
      var body = 'A new line has been added on the Missing Cuts Report on line  <b>' + cell + '</b> - click <a href="' + ss.getUrl() + '">here</a> to view the update';

   var msgPlain = body.replace(/(<([^>]+)>)/ig, ""); // clear html tags for plain mail
    MailApp.sendEmail(recipients, subject, msgPlain, { htmlBody: body });
      } // "<" and ">" are not for comparing string values. (At least not in this case.)
    //} //IndexOf will always result in -1 if you are looking for just "B" as your result will always be "B1/B2/B3/ect."
  } // Already have the sheet object, no need to call again.
}

推荐阅读