CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
lfilleur
lfilleur

Reputation: 49

SendEmail function : error because of formulas in blank rows

I have a sendEmail function which works almost perfectly with my Google Sheet.

The getLastRow function return all my blank rows because they have an hidden formula and I'd like to grab only filled rows.

Here is my code :

var EMAIL_SENT = 'EMAIL SENT';
  
function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2; // First row of data to process
  var numRows = sheet.getLastRow();
  // Fetch the range of cells 
  var dataRange = sheet.getRange(2, 1,numRows-1,20);
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var emailAddress = row[7] + "," + row[9] + "," + row[13]; 
    var message = row[18]; 
    var emailSent = row[19]; 
    if (emailSent != EMAIL_SENT) { // Prevents sending duplicates
      var subject = 'Un nouvel adhérent vous a été affecté';
      MailApp.sendEmail(emailAddress, subject, message);
      sheet.getRange(startRow + i, 20).setValue(EMAIL_SENT);
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
    }
  }
}

Already tried var data = dataRange.getDisplayValues(); instead of var data = dataRange.getValues();without significant result.

Do you have any idea ?

Here is a sample of my sheet :

enter image description here

Upvotes: 2

Views: 47

Answers (2)

Marios
Marios

Reputation: 27350

Solution:

Replace this:

var numRows = sheet.getLastRow();

with:

var numRows = sheet.getRange("F1:F").getDisplayValues().filter(String).length;

The latter will filter out the cells of your sheet that are empty even if they contain a formula.

References:

Upvotes: 2

Maciek
Maciek

Reputation: 131

Why don't you just add an if statement inside a for loop and check if one of the cells is empty, and break the loop if so.

Your code would look something like this:

var EMAIL_SENT = 'EMAIL SENT';
  
function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2; // First row of data to process
  var numRows = sheet.getLastRow();
  // Fetch the range of cells 
  var dataRange = sheet.getRange(2, 1,numRows-1,20);
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    if(row[6] === "") break; 
    var emailAddress = row[7] + "," + row[9] + "," + row[13]; 
    var message = row[18]; 
    var emailSent = row[19]; 
    if (emailSent != EMAIL_SENT) { // Prevents sending duplicates
      var subject = 'Un nouvel adhérent vous a été affecté';
      MailApp.sendEmail(emailAddress, subject, message);
      sheet.getRange(startRow + i, 20).setValue(EMAIL_SENT);
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
    }
  }
}

Upvotes: 1

Related Questions