cell reference out of range on Email Notifications

Question

I have the below code for sending a email notification when a cell changes, but it is always returning Cell reference out of range(line 5, file "Missing Cuts Report"). Can anyone advise if i need to change the code or if there is a way of getting it to stop failing and still send the email notification through. The trigger i use is OnChange because when it was on OnEdit it came back with error service invoked too many times on line 17 MailApp.sendEmail(recipients, subject, msgPlain, { htmlBody: body }). Any help would really be appreciated.

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 protected];
      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 });
      }
    }
  }
}

New_2_Code New_2_Code · Accepted Answer · 2018-11-27T08:45:59

Hi this is a not a very good way of achieving the result you want. Not really to sure what you are after. I have however made some adjustments to your code with notes after the closing braces. This is not an answer to your original problem. Just some hints.

    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 protected]";
      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.
}

cell reference out of range on Email Notifications

1 Answers