Make MailApp.sendEmail stop sending emails when Column A is empty

Question

I've built a spreadsheet to automate sending formulaic emails. It sends to the client's email (defined in spreadsheet) and CCs an email address that needs to receive all projects. The number of projects/emails will change each time I use the sheet.

I've run into trouble because the blank rows in the spreadsheet currently trigger emails to the CC email address. I don't think I can use something like getLastRow to fix this because I have formulas in every cell in some columns. Can you help me figure out how to only send an email if there is data in Column A?

Here is my current script:

function uploadsender() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A2:AA");
var UserData = range.getValues();
for (i in UserData) {
var row = UserData[i];
var emailaddress = row[2];
var bccaddress = row[3];
var subject = row[4];
var comments = row[5];
var filenames = row[7];
  MailApp.sendEmail(emailaddress, subject, filenames + "\n\n" + comments, {
                cc: "example@gmail.com",
                bcc: bccaddress,
                name: "John Doe"}
             );
} 
}

It seems like something like this answer might be my solution:

var Avals = ss.getRange("A1:A").getValues();
var Alast = Avals.filter(String).length;

But I assume this will conflict with lines 3 and 4 of my current script?

Answer 1

Your larger problem appears to be the existence of formulas in every Row in the spreadsheet in column H. They return as having SOME value.

I would remove all the formulas in row H and in cell H1 place:

=ARRAYFORMULA(IF(ROW(A1:A)=ROW(A1),"Email Body", IF(ISBLANK(A1:A), ,J1:J&char(10)&K1:K&char(10)&L1:L) ))

Which breaks down as follows: The ARRAYFORMULA will apply this to ever row (in this case because we use arrays of rows) from A1:A

There are then 2 nested IF statements. The first one test to see if we are in Row 1. If we are, it put Email Body in the cell. If we are not, then the second IF statement comes into play. If Cell A in this row is Blank, then do nothing (NOTE that there is NOT a "" in the true area here as that actually applies a value to the cell, unlike Excel) If cell A is not blank, create your text string.

Do this and Cooper's original answer to replace sheet.getRange("A2:AA") with sheet.getDataRange() is completely valid as you should only get data passed with getDataRange() for rows with data.

Another option would be to wrap everything in an IF(){} to check for a value in column A:

var row = UserData[i];
if(if(UserData[i][0] && UserData[i][0] !== 'ID'){){
   var row = UserData[i];
   //...  Down to sending the email
}

EDIT: Apparently getDataRange() even gets formulas via the ARRAYFURMULA if they don't return a value. So it still gets too many rows. So a quick search lead me to the answer of getting the last row via a function. That changed the code to this:

function uploadsender() {
   var sheet = SpreadsheetApp.getActiveSheet();
   var lr = getLastPopulatedRow(sheet);
   var range = sheet.getRange(2,1,lr,8);
   var UserData = range.getValues();
   for (i in UserData) {
     if(UserData[i][0] && UserData[i][0] !== 'ID'){
       //Continue to the Send Email
     }
   }
}

function getLastPopulatedRow(sheet) {
  var data = sheet.getDataRange().getValues();
  for (var i = data.length-1; i > 0; i--) {
    for (var j = 0; j < data[0].length; j++) {
      if (data[i][j]) return i+1;
    }
  }
  return 0; // or 1 depending on your needs
}

If you need more column than H change the 8 to the appropriate value in the getRange() call

Answer 2

Instead of this var range = sheet.getRange("A2:AA")how about this sheet.getDataRange()

This loops works but there seems to be something wrong with the sendMail statement and I don't want to mess with it.

function troubleinMailVille()
{
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getDataRange();
var UserData = range.getValues();
var s='<h1>Sending Mail</h1>';
for (var i=1;i<UserData.length;i++) 
  {
    var emailaddress = UserData[i][2];
    var bccaddress = UserData[i][3];
    var subject = UserData[i][4];
    var comments = UserData[i][5];
    var filenames = UserData[i][7];

    s+= '<br />1.  ' + emailaddress + ', ' + bccaddress + ', ' + subject + ', ' + comments + ', ' + filenames;
  } 
  //MailApp.sendEmail(emailaddress, subject, filenames + "\n\n" + comments, {cc: "example@gmail.com",bcc: bccaddress,name: "John Doe"});
  var html=HtmlService.createHtmlOutput(s).setWidth(800).setHeight(450);
  SpreadsheetApp.getUi().showModelessDialog(html, 'Sending Emails without actually sending them');
}

Answer 3

You can use:

sheet.getLastRow()

By this way, your script just will take the cells that have a value and this will reduce the execution time of the original script.

---

function uploadsender() {
var sheet = SpreadsheetApp.getActiveSheet();
Get the last row with a value
var last = sheet.getLastRow()-1;
//Then you can get the range that is useful for your function
// Just replace: "var range = sheet.getRange("A2:AA");" 
var UserData = sheet.getRange(2, 1, last, numColumns).getValues();
//Replace numColumns, you can use "sheet.getLastColumn();" or just writing how many columns you want to get.
for (i in UserData) {
var row = UserData[i];
var emailaddress = row[2];
var bccaddress = row[3];
var subject = row[4];
var comments = row[5];
var filenames = row[7]; 
MailApp.sendEmail(emailaddress, subject, filenames + "\n\n" + comments, {  
  cc: "example@gmail.com",          
  bcc: bccaddress,         
  name: "John Doe"
    });
 }
}