[Solved] Collate list of Pending actions and sent together in email, i have code for single mail for each pending actions based on date

[Vishnukumar]

Dear Vishnukumar,

I checked the code present in the previous workbooks. I took a few records with expiration dates that have already passed. These dates include yesterday's date or earlier dates. When I ran the code, the emails were sent properly. The following video shows an overview of the process.​

Collate Pending Tasks and Send Via Email

Here is the required code in case you have lost the previous code.

function UpdatedCodeForCollatePendingTasks() {

  //Define spreadsheet and source data sheet
  var spst = SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = spst.getSheetByName("Instruments & Gauges"); // Insert the name of your Data Sheet source

  // define yesterday
  var pastCurrentDate = new Date();
  var beforeDate = pastCurrentDate;
  beforeDate.setDate(beforeDate.getDate() - 1);

  // define two weeks from today
  var currentDate = new Date();
  var afterTwoWeeksDate = currentDate;
  afterTwoWeeksDate.setDate(afterTwoWeeksDate.getDate() + 14);

  // define one month from today
  var newCurrentDate = new Date();
  var afterOneMonthDate = newCurrentDate;
  afterOneMonthDate.setDate(afterOneMonthDate.getDate() + 30);

  // define dataset parameters
  var startRow = 2;
  var strtCol = 1;
  var lastRow = dataSheet.getLastRow();
  var lastCol = dataSheet.getLastColumn();
  var expirationDateCol = 10;  // i used due date in between cells not last cell considered this parameter and defined cell number

  //select data range
  var dataRange = dataSheet.getRange(startRow, strtCol, lastRow - 1, lastCol);
  var data = dataRange.getValues();
  // helper parameters
  var checkTwoWeekExpire = 1;
  var checkOneMonthExpire = 1;
  var checkbeforeYesterday = 1;

  // go through the enitre range to filter dataset into two required groups
  for (var i = 0; i < data.length; i++) {
    if (data[i][expirationDateCol - 1] <= beforeDate) {
      checkbeforeYesterday = checkbeforeYesterday + 1;
      if (checkbeforeYesterday == 2) {
        var pastDateSheet = spst.insertSheet();
        pastDateSheet.setName("Already Expired");
        var headerRow = dataSheet.getRange(1, 1, 1, lastCol);
        headerRow.copyTo(pastDateSheet.getRange("A1"));
      }
      var expiredData = dataSheet.getRange(i + 2, 1, 1, lastCol);
      expiredData.copyTo(pastDateSheet.getRange(checkbeforeYesterday, 1));
    }
    if (data[i][expirationDateCol - 1] <= afterTwoWeeksDate && data[i][expirationDateCol - 1] > beforeDate) {
      checkTwoWeekExpire = checkTwoWeekExpire + 1;
      if (checkTwoWeekExpire == 2) {
        var twoWeekSheet = spst.insertSheet();
        twoWeekSheet.setName("Expire Within Two Weeks");
        var headerRow = dataSheet.getRange(1, 1, 1, lastCol);
        headerRow.copyTo(twoWeekSheet.getRange("A1"));
      }
      var twoWeekData = dataSheet.getRange(i + 2, 1, 1, lastCol);
      twoWeekData.copyTo(twoWeekSheet.getRange(checkTwoWeekExpire, 1));
    }

    if (data[i][expirationDateCol - 1] <= afterOneMonthDate && data[i][expirationDateCol - 1] > afterTwoWeeksDate) {
      checkOneMonthExpire = checkOneMonthExpire + 1;
      if (checkOneMonthExpire == 2) {
        var oneMonthSheet = spst.insertSheet();
        oneMonthSheet.setName("Expire Within One Month");
        var newHeaderRow = dataSheet.getRange(1, 1, 1, lastCol);
        newHeaderRow.copyTo(oneMonthSheet.getRange("A1"));
      }
      var oneMonthData = dataSheet.getRange(i + 2, 1, 1, lastCol);
      oneMonthData.copyTo(oneMonthSheet.getRange(checkOneMonthExpire, 1));
    }
  }

  // define helper data sheets for filter groups
  var ss1 = spst.getSheetByName("Expire Within two Weeks");
  var ss2 = spst.getSheetByName('Expire Within One Month');
  var ss3 = spst.getSheetByName("Already Expired");

  // define gmail parameters
  var subject = "Machine Calibration Expriation";
  var recipient = "[email protected]"; // change the email address to your required email address
  var sender = Session.getActiveUser().getEmail();
  var ccMail = "[email protected]"

 // Email the first filtered group
  if (ss1) {
      var twLastRow = ss1.getLastRow();
    var twRange = ss1.getRange(1, 1, twLastRow, lastCol);
    var twData = twRange.getValues();

    var twTable = formatDataAsTable(twData);
    var twBody = "Machine Calibration will be expired within two weeks, plan at the earliest." + twTable;

    GmailApp.sendEmail(recipient, subject,"[email protected]", {
      cc: ccMail,
      htmlBody: twBody,
      from: sender,
    });

   spst.deleteSheet(ss1);
  }

  // Email the second filtered group
  if (ss2) {
    var omLastRow = ss2.getLastRow();
    var omRange = ss2.getRange(1, 1, omLastRow, lastCol);
    var omData = omRange.getValues();

    var omTable = formatDataAsTable(omData);
    var omBody = "Machine Calibration will be expired within one month, plan at the earliest." + omTable;

    GmailApp.sendEmail(recipient, subject, "", {
      cc: ccMail,
      htmlBody: omBody,
      from: sender
    });

   spst.deleteSheet(ss2);
  }
  if (ss3) {
    var uwLastRow = ss3.getLastRow();
    var uwRange = ss3.getRange(1, 1, uwLastRow, lastCol);
    var uwData = uwRange.getValues();

    var uwTable = formatDataAsTable(uwData);
    var uwBody = "Machine Calibration Expired,Top Priority." + uwTable;

    GmailApp.sendEmail(recipient, subject, "", {
      cc: ccMail,
      htmlBody: uwBody,
      from: sender
    });

    spst.deleteSheet(ss3);
  }

}

// Helper function to format the filtered data in tabular format
function formatDataAsTable(data) {
  var table = "<table style='border-collapse: collapse;'>";
  for (var i = 0; i < data.length; i++) {
    table += "<tr>";
    for (var j = 0; j < data[i].length; j++) {
      var cellValue = data[i][j];
      if (cellValue instanceof Date) {
        cellValue = formatDate(cellValue);
      }
      if (i == 0) {
        table += "<th style='border: 1px solid black; padding: 5px; font-weight: bold;'>" + cellValue + "</th>";
      } else {
        table += "<td style='border: 1px solid black; padding: 5px;'>" + cellValue + "</td>";
      }
    }
    table += "</tr>";
  }
  table += "</table>";
  return table;
}

// Helper function to format date in mm/dd/yyyy format
function formatDate(date) {
  var formattedDate = Utilities.formatDate(date, Session.getScriptTimeZone(), "MMM dd, yyyy");
  return formattedDate;
}

Regards,
Seemanto Saha
ExcelDemy

Yes i am getting i just formatted as date.