CODEWITHSUNDEEP
google-apps-script
Nick Francis
Nick Francis

Reputation: 39

How to fix code not returning e-mail addresses

I posted a question earlier on here helping me out with some code for a rota sheet I'm working on.

It was working. But now for some reason it isn't anymore & I've been staring at it for hours.

Here are the sheets in question link

Error message I'm getting is "no recipient".

I've stepped through the code as much as possible and tried to see if I can work out the fault but can't get to the bottom of it.

Here is the code:

for (var x = 1; x < 5; x++) { // 5 because emails are till col4

    var emailAddress = []; // Start by collecting the non-blank emails in an array

    if (getEmailFromName(row[x]) != "") {

      emailAddress.push(getEmailFromName(row[x]))

    }

  }

  emailAddress = emailAddress.join();  // Join the array to get a comma separated string

  MailApp.sendEmail(emailAddress, subject, message);

Here is the getEmailFromName function:

function getEmailFromName(sKey) {



  // to use this function, don’t put anything in the first column (A) or row (1).

  // Put the name (i.e. the key, or what we’re looking for) in column B.

  // Put what we want to return in column C. 



  var columnToSearch = 1; //column B



  // Set the active sheet to our email lookup

  var ss1 = SpreadsheetApp.getActiveSpreadsheet();

  var sh1 = ss1.getSheetByName("EmailContactList")

  ss1.setActiveSheet(sh1);



  var data = SpreadsheetApp.getActiveSheet().getDataRange().getValues();

  var line = -1;

  for( var i = 0; i < data.length; i++ ) {

    if( data[i][columnToSearch] == sKey ) {

      line = i;

      break;

    }

  }



  if( line != -1 ) {

    //do what you want with the data on "line"

    return data[line][2]; //value on column C of the matched line

  } 

  else {

    return "";

  // if criteria is not found

 }

}

What I want to happen is it just to fire e-mails off.

Ideally I'd like to rotate the rota so the dates run down column A & the roles run across the top but I'll get to that once I can get this reliable & stable!

Upvotes: 1

Views: 34

Answers (1)

Tanaike
Tanaike

Reputation: 201358

How about this modification?

Modified script:

  • When var emailAddress = []; is inside of the for loop, emailAddress of emailAddress = emailAddress.join() becomes []. By this, I think that such error occurs.

In order to avoid this, please modify as follows.

From: 
for (var x = 1; x < 5; x++) { // 5 because emails are till col4
  var emailAddress = []; // Start by collecting the non-blank emails in an array
To: 
var emailAddress = []; // Start by collecting the non-blank emails in an array
for (var x = 1; x < 5; x++) { // 5 because emails are till col4

If I misunderstood your question and this was not the result you want, I apologize.

Upvotes: 1

Related Questions