I have a function that needs to use emails that are within a cell range on my sheet-CodePudding

I have a function for Google Sheets that sends an email based on whether or not a cell value has a particular variable in it. Every part of the below code works more or less as intended except for the part where it needs an email recipient to send the email. I know where the problem is, I'm just not experienced with code at all (sorry in advance) and don't know how to fix this.

The error: Exception: Failed to send email: no recipient (anonymous) @ Untitled.gs:15 sendEmails @ Untitled.gs:5

Thank you for your patience.

function sendEmails() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Emails'); // change Sheet1 to the name of your sheet
  const data = sh.getRange('A2:D' sh.getLastRow()).getValues();
  data.forEach(r=>{
     let sendemailValue = r[1];  
     if (sendemailValue === "X"){
       let lrow=sh.getLastRow();
         let name = r[0];
         let amount = r[2];
         let item = r[3];
         let emailAddress = r[4];
         let message = 'Hello '   name   ', your receipt is '   amount   ' for your '   item;
         let subject = 'Your Receipt.'
         MailApp.sendEmail(emailAddress, subject, message); 
     }
  });  
}

CodePudding user response：

You might wish to consider an addition like this:

if(emailAddress) {
  MailApp.sendEmail(emailAddress, subject, message); 
} else {
  console.log(`Row: ${i   2}`);//and add ( ,i) to the forEach
}

also change this:

const data = sh.getRange('A2:D' sh.getLastRow()).getValues();

to this:

const data = sh.getRange(2, 1, sh.getLastRow()-1,5).getValues();

I increased the number of columns and changed the format to insure it will not return a bunch of nulls at the bottom. But now that I think about it the problem was probably that you are looking for the emailaddress outside of the range of you data.

Altogether:

function sendEmails() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Emails'); // change Sheet1 to the name of your sheet
  const data = sh.getRange(2, 1, sh.getLastRow() - 1, 5).getValues();
  data.forEach((r,i) => {
    let sendemailValue = r[1];
    if (sendemailValue === "X") {
      let lrow = sh.getLastRow();
      let name = r[0];
      let amount = r[2];
      let item = r[3];
      let emailAddress = r[4];//this is column 5
      let message = 'Hello '   name   ', your receipt is '   amount   ' for your '   item;
      let subject = 'Your Receipt.'
      if(emailAddress) {
        MailApp.sendEmail(emailAddress, subject, message);
      } else {
        console.log(`Row: ${i   2}`);//will let you know what row it's failing on
      }
    }
  });
}