Sending email using google sheet

Question

I have a G sheet that contains a transaction of our staff. And i'm trying to make a script that will sent a notification email to the employee that they had a pending on particular account no.

The Email address is automatically added when we choose the staff. (Employee had a multiple account)

Every account no. has own G sheet, we're just importing the pending per Gsheet of the account no.

----------------------------------------------------------------
 ACCOUNT NO.|STAFF|    PENDING |    REMARKS     |     EMAIL    |
----------|-------|------------|----------------|---------------|
 1231242  | JOHN  | PENCIL - 1 |     2ND        |               |
          |       | PAPER- 2   |   FOLLOW UP    |JOHN@GMAIL.COM |
 ---------|-------|------------|----------------|---------------| 
          |       |            |                |      N/A      |
 ---------|-------|------------|----------------|---------------|
 1231243  |  TED  |            | 1ST FOLLOW UP  | TED@GMAIL.COM | 
 ---------|-------|------------|----------------|---------------|
          |       | TAPE- 1    |                |               |
 1231244  | MARY  | NOTEBOOK-16|  TOP URGENT    | MARY@GMAIL.COM|
 ---------|-------|------------|----------------|---------------|
          |       | BAG - 1    |                |               |
 12312467 | JOHN  | BOARD-16   |   URGEN   T    |JOHN@GMAIL.COM |
 ----------------------------------------------------------------

This is the code i'm using. When there is no email address on column Email it's not working

 function CheckPending() {
  // Fetch the Pendings
  var PendingRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Week 2").getRange("C2:C"); 
  var DailyPendings = PendingRange.getValue();

  // Check totals Pending
  if (DailyPendings ==! ""){

    // Fetch the email address
    var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Week 2").getRange("D2:D");
    var emailAddress = emailRange.getValues();

    // Send Alert Email.
    var message = 'YOU HAVE ' + DailyPendings + ' PENDING, PLEASE CHECK '; // Second column
    var subject = 'PENDING ALERT';
        MailApp.sendEmail(emailAddress, subject, message);

    }
}

Answer 1

See commented code below:

function CheckPending() {
  // Fetch the Pendings
  // Best to get values for both columns at once for performance reasons, and then work on the resulting array
  var PendingRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Week 2").getRange("C2:D"); 
  var DailyPendings = PendingRange.getValues(); // Here you need getValueS() as you take values from a range

  // Check totals Pending
  // Since you consider multiple rows, you need to loop through all rows ; 
  // Note that it might be wiser in your case to use getDataRange() above and consider columns 3 and 4 of your array, as otherwise you'll include empty lines
  for (var i = 0 ; i < DailyPendings.length ; i++){
    if (DailyPendings[i][0] !== ""){ // Your operator here was in the wrong order
      Logger.log(DailyPendings[i][0]);
      // Fetch the email address
      // Can be removed, not useful: var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Week 2").getRange("D2:D");
      var emailAddress = DailyPendings[i][1];

      // Send Alert Email.
      var message = 'YOU HAVE ' + DailyPendings[i][0] + ' PENDING, PLEASE CHECK '; // Second column
      var subject = 'PENDING ALERT';
      MailApp.sendEmail(emailAddress, subject, message);

    }
  }
}

Update:

function CheckPending() {
  // Collect all data in a JS array
  var DailyPendings = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Week 2").getDataRange().getValues(); 


  // Loop through each row, starting at row with index 1 (second row) since the first one contains headers
  for (var i = 1 ; i < DailyPendings.length ; i++){
    /*
    Adjusted to your edited question: 
    ** Column with index 0 is first column: account number
    ** Column with index 1 is second column: staff
    ** Column with index 2 is third column: pendings
    ** Column with index 3 is fourth column: remarks
    ** Column with index 4 is fifth column: email
    */
    if (DailyPendings[i][2] !== ""){ // If pending isn't empty

      var emailAddress = DailyPendings[i][4];
      var account = DailyPendings[i][0];

      // Send Alert Email.
      var message = 'YOU HAVE ' + DailyPendings[i][2] + ' PENDING, PLEASE CHECK '; // Add the variable 'account' anywhere in your (adjusted) message
      var subject = 'PENDING ALERT';
      MailApp.sendEmail(emailAddress, subject, message);

    }
  }
}