Help with code sending email based on two conditions

[Ramon Corrales]

This code is supposed to send the email 

when two conditions are met

1.  Numeric content is entered in a cell in colunn G
2.  In column "I" the cell is blank.

If these two conditions are not met don't send email

If met send email, and Enter "YES" in the last row in column "I"

When I run the script and an email should go out because both conditions are met.

The log states " no new data to process"

Also, I know for a fact the email address is correct.

Can someone review and provide any recommendations

function sendNewEmails(lastProcessedRow) {

  // Define the recipient email

  var recipientEmail = "jxdo...@gmail.com";

  // Define the "From" name

  var fromName = "Attendance Callout Line";

  // Retrieve the subject from the spreadsheet

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  var sheetName = "Who Called Message";

  var sheet = spreadsheet.getSheetByName(sheetName);

  var startRow = lastProcessedRow ? parseInt(lastProcessedRow) + 1 : 2; // Start from the next row

  var lastRow = sheet.getLastRow();

  var numRows = lastRow - startRow + 1;

  if (numRows <= 0) {

    // No new data

    console.log("No new data to process.");

    return;

  }

  console.log("Processing new data...");

  var dataRange = sheet.getRange(startRow, 1, numRows, sheet.getLastColumn());

  var data = dataRange.getValues();

  // Send email for each new row with numeric content in column E and "No" in column I

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

    var row = data[i];

    var subject = row[6]; // Column G

    var numericContent = /^\d+$/.test(row[4]); // Column G numeric check

    var status = row[9]; // Column I

    var body = "The agent above may have called out or may be late to work."; // Define the email body

    if (numericContent && status !== "Yes") {

      // Send email with custom "From" name

      GmailApp.sendEmail({

        to: recipientEmail,

        subject: subject,

        body: body,

        name: fromName

      });

      console.log("Email sent. Subject: " + subject);

      // Update column I with "Yes"

      var rowIndex = startRow + i;

      var statusCell = sheet.getRange(rowIndex, 9); // Column I

      statusCell.setValue("Yes");

    }

  }

  // Update the last processed row

  PropertiesService.getScriptProperties().setProperty('lastProcessedRow', lastRow);

  console.log("Processing complete.");

}

// Call the function with the last processed row

var lastProcessedRow = PropertiesService.getScriptProperties().getProperty('lastProcessedRow');

sendNewEmails(lastProcessedRow);

Ramon Corrales Workforce Manager Experience Center & Learning Space Services

University Technology Office

Arizona State University

Mail Code: 6504

1150 E University Dr. Suite 115

Tempe, AZ 85281

p: 855-278-5080

email: ramon.c...@asu.edu

web: uto.asu.edu

linkedin

ASU #1 in the U.S. for innovation

 

[Ramon Corrales]

if anyone can take a look at the issue below and provide some recommendations on how to resolve, I will

greatly appreciated.

thank you 

Ramon Corrales Workforce Manager Experience Center & Learning Space Services

University Technology Office

Arizona State University

Mail Code: 6504

1150 E University Dr. Suite 115

Tempe, AZ 85281

p: 855-278-5080

email: ramon.c...@asu.edu

web: uto.asu.edu

linkedin

ASU #1 in the U.S. for innovation

 

---

From: google-apps-sc...@googlegroups.com <google-apps-sc...@googlegroups.com> on behalf of Ramon Corrales <Ramon.C...@asu.edu>
Sent: Monday, June 26, 2023 3:44 PM
To: Google Apps Script Community <google-apps-sc...@googlegroups.com>
Subject: [Apps-Script] Help with code sending email based on two conditions

 

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/BY3PR06MB816123E04156F0CEB139D1EE8126A%40BY3PR06MB8161.namprd06.prod.outlook.com.

[Jon Couch]

Ramon, 

I'm not the smartest bulb in the pack so I'm not sure exactly why you're doing what you're doing and I use a different method to do what I think you're doing - yours is probably a lot more elegant. But in testing your code I find that you are assigning the subject using element 6 in your array, but you're testing element 4 for validation. 

When I change 

var numericContent = /^\d+$/.test(row[4]); // Column G numeric check

to

var numericContent = /^\d+$/.test(row[6]); // Column G numeric check

numbericContent evaluates to true and it sends the email.

I hope that helps,
Jon

BTW: Go Hokies!

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/BY3PR06MB81613A50603EBBF6B642548C8130A%40BY3PR06MB8161.namprd06.prod.outlook.com.