CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets-api
Cyborgninja
Cyborgninja

Reputation: 11

How do I skip blank cells using MailApp.SendEmail?

I've been using Google form to collect information to a workbook, then using that information to send customized emails back to the sender. I am a noob at coding, but I have managed to cobble together enough for this task. My problem is that, when it comes to a blank email cell it stops. I've tried to put in a loop to make it skip, with no success. Any help would be appreciated. This is the program I am using without a loop.

// This constant is written in column C for rows for which an email
// has been sent successfully.
var EMAIL_SENT = "EMAIL_SENT";

function sendEmails2() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var numRows = 2000;   // Number of rows to process
  // Fetch the range of cells A2:B2001
  var dataRange = sheet.getRange(startRow, 1, numRows, 2001)
  // 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[0];  // First column
    var message = row[1];       // Second column
    var emailSent = row[2];     // Third column
    if (emailSent != EMAIL_SENT) {  // Prevents sending duplicates
      var subject = "Bounce Reply";  
      MailApp.sendEmail(emailAddress, subject, message);
      sheet.getRange(startRow + i, 3).setValue(EMAIL_SENT);
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
      }
    }
  }

Upvotes: 1

Views: 1575

Answers (1)

Jack Brown
Jack Brown

Reputation: 5892

You can use string.match() function to determine if a cell is empty or not. Since you are looking for email address you might as well see if the cell has '@' sign. If not exclude the cell and continue on to the next cell like so:

if (emailAddress.match('@')  === null){
 continue;  // skip this iteration of the loop and go to the next one
}

Your final code will look like:

var EMAIL_SENT = "EMAIL_SENT";
function sendEmails2() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var numRows = 2000;   // Number of rows to process
  // Fetch the range of cells A2:B2001
  var dataRange = sheet.getRange(startRow, 1, numRows, 2001)
  // 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[0]  // First column
    if (emailAddress.match('@')  === null){
       continue;  // skip this iteration of the loop and go to the next one
    };  
    var message = row[1];       // Second column
    var emailSent = row[2];     // Third column
    if (emailSent != EMAIL_SENT) {  // Prevents sending duplicates
      var subject = "Bounce Reply";  
      MailApp.sendEmail(emailAddress, subject, message);
      sheet.getRange(startRow + i, 3).setValue(EMAIL_SENT);
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
      }
    }
  }

Edit

To check if something is empty or not you can try something like this.

if (message.match(/[a-zA-Z0-9_]/) === null) {  //" /[a-zA-Z0-9_]/ " is regex  to match any and every alphanumeric letter including underscore
 continue; // skip this iteration of the loop and go to the next one
}

You can find more details on function match and regex here.

Hope that helps!

Upvotes: 1

Related Questions