Google sheets compare values in 2 columns and email list when matching conditions-CodePudding

Sheet link: https://docs.google.com/spreadsheets/d/1HhJZXDGr1KmAAiYBTIQG_omNT26ZYOFqbes2U7rif6I/edit?usp=sharing

I have an email alert setup which emails me a list of "Equip" from the "Maint. Schedule" tab when the value in Column "U" (engine oil due) reaches a value equal to or greater than 15 less of Column "S" (Current Hours) value. My issue is, I receive as many emails as the amount of equipment meeting the email requirements. Some emails have a single piece of equipment included, some have all, and some have a varying amount of pieces included. I don't know enough to find the specific issue. Neither am I familiar with the way this site works so, noob alert.

This is my current code. I did copy/paste and modify from the internet somewhere, if this is your code, I don't remember where I got it to thank you!

function sendTableEmail() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Maint Sched."); // Get your table data
  var startRow = 4;  // First row of data to process
  var numRows = sheet.getLastRow(); // Number of rows to process
  var dataRange = sheet.getRange(startRow, 1, numRows, 22); // Fetch the range of cells A7:D
  var data = dataRange.getValues(); // Fetch values for each row in the Range.

      
  // Loop through the data to build your table
  var message = "<html><body><table style=\"text-align:center\"></tr><th>Vehicle</th><th></th><th></th><th></th><th>Due</th><th></th><th></th><th></th><th>Current</th></tr>";
  for (var i = 0; i < data.length; i  ) {
    var rowData = data[i];
    var equip = rowData[1]
    var current = rowData[18];
    var due = rowData[20];
    if (due > 0) {
      if (current 15 >= due) {
      Logger.log(equip   " "   current   " "  due)
        var equip = rowData[1]
        var current = rowData[18];
        var due = rowData[20];
        message  = "<tr><td>" equip "</td><td></td><td></td><td></td><td>" due "</td><td></td><td></td><td></td><td>" current "</td></tr>";
        message  = "</table></body></html>";
        var subject = "Services Due";
        MailApp.sendEmail({
        to: "*********", 
        subject: subject, 
        htmlBody: message
      } )
    }
  } ;
 };
}

I added Logger which shows correct info leaving me to believe the issue is somehow with the email function.

CodePudding user response：

For each loop, you are adding stuff in message with operator = and send email. So first email will contains only 1 element, last email will have all.

If you want to send only one mail at the end of the job :

  function sendTableEmail() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Maint Sched."); // Get your table data
  var startRow = 4;  // First row of data to process
  var numRows = sheet.getLastRow(); // Number of rows to process
  var dataRange = sheet.getRange(startRow, 1, numRows, 22); // Fetch the range of cells A7:D
  var data = dataRange.getValues(); // Fetch values for each row in the Range.

  // Loop through the data to build your table
  var message = "<html><body><table style=\"text-align:center\"></tr><th>Vehicle</th><th></th><th></th><th></th><th>Due</th><th></th><th></th><th></th><th>Current</th></tr>";
  for (var i = 0; i < data.length; i  ) {
    var rowData = data[i];
    // var equip = rowData[1]
    var current = rowData[18];
    var due = rowData[20];
    if (due > 0) {
      if (current 15 >= due) {
        Logger.log(equip   " "   current   " "  due)
        var equip = rowData[1]
        var current = rowData[18];
        var due = rowData[20];
        message  = "<tr><td>" equip "</td><td></td><td></td><td></td><td>" due "</td><td></td><td></td><td></td><td>" current "</td></tr>";
      }
    }
  }
  var subject = "Services Due";
  message  = "</table></body></html>";
  MailApp.sendEmail({
    to: "*********", 
    subject: subject, 
    htmlBody: message
  });
}

Or if you want to send email for every loop :

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

    //re-init message at each loop
    message = "<html><body><table style=\"text-align:center\"></tr><th>Vehicle</th><th></th><th></th><th></th><th>Due</th><th></th><th></th><th></th><th>Current</th></tr>";

    var rowData = data[i];
    var equip = rowData[1]
    var current = rowData[18];
    var due = rowData[20];
    if (due > 0) {
      if (current 15 >= due) {
      Logger.log(equip   " "   current   " "  due)
        var equip = rowData[1]
        var current = rowData[18];
        var due = rowData[20];
        message  = "<tr><td>" equip "</td><td></td><td></td><td></td><td>" due "</td><td></td><td></td><td></td><td>" current "</td></tr>";
        message  = "</table></body></html>";
        var subject = "Services Due";
        MailApp.sendEmail({
        to: "*********", 
        subject: subject, 
        htmlBody: message
      } )
    }
  } ;