function sendEmailWithData() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("AUTOMAIL");

  // Email groups
  var email1 = "Pravin.kumar@meenakshipolymers.com"; // A to G
  var email2 = "rajender.yadav@meenakshipolymers.com"; // I to O
  var email3 = "vibezmattress@meenakshipolymers.com,production@vibezzzmattress.com"; // Q to W
  var email4 = "rajender.yadav@meenakshipolymers.com,vibezmattress@meenakshipolymers.com,production@vibezzzmattress.com"; // Y to AE
  var email5 = "vibezmattress@meenakshipolymers.com,production@vibezzzmattress.com"; // AG to AM
  var email6 = "rajender.yadav@meenakshipolymers.com"; // AO to AU
  var email7 = "vibezmattress@meenakshipolymers.com,production@vibezzzmattress.com";//AW to BC
  var email8 = "narendra.singh@meenakshipolymers.com"; // BE to BK

  // CCs
  var cc1 = cc2 = cc3 = cc4 = cc5 = cc6 = cc7 = 
    "anu.singh@meenakshipolymers.com,sandeep.sareen@meenakshipolymers.com,ppc@meenakshipolymers.com,chanchal.rani@meenakshipolymers.com";

  var cc8 = "dharmesh.kumar@meenakshipolymers.com,chanchal.rani@meenakshipolymers.com,avanish.kumar@meenakshipolymers.com,mamta.vashishtha@meenakshipolymers.com,rajender.yadav@meenakshipolymers.com,vibezmattress@meenakshipolymers.com,production@vibezzzmattress.com,ppc@meenakshipolymers.com,anu.singh@meenakshipolymers.com,sandeep.sareen@meenakshipolymers.com";

  // Column ranges
  var range1 = sheet.getRange("A1:G" + sheet.getLastRow()).getValues(); 
  var range2 = sheet.getRange("I1:O" + sheet.getLastRow()).getValues(); 
  var range3 = sheet.getRange("Q1:W" + sheet.getLastRow()).getValues(); 
  var range4 = sheet.getRange("Y1:AE" + sheet.getLastRow()).getValues(); 
  var range5 = sheet.getRange("AG1:AM" + sheet.getLastRow()).getValues(); 
  var range6 = sheet.getRange("AO1:AU" + sheet.getLastRow()).getValues(); 
  var range7 = sheet.getRange("AW1:BC" + sheet.getLastRow()).getValues(); 
  var range8 = sheet.getRange("BE1:BK" + sheet.getLastRow()).getValues();

  // Send emails only if non-empty data exists
  if (hasData(range1.slice(1))) sendTableEmail(email1, cc1, "SALES ORDER DATE N/A ==ORDER FMS MANESAR ", range1);
  if (hasData(range2.slice(1))) sendTableEmail(email2, cc2, "PRDN. ACTUAL DATE N/A ==ORDER FMS MANESAR", range2);
  if (hasData(range3.slice(1))) sendTableEmail(email3, cc3, "PRDN. ACTUAL DATE N/A ==ORDER FMS MANESAR", range3);
  if (hasData(range4.slice(1))) sendTableEmail(email4, cc4, "HANDOVER. DATE N/A ==ORDER FMS MANESAR", range4);
  if (hasData(range5.slice(1))) sendTableEmail(email5, cc5, "HANDOVER. DATE N/A ==ORDER FMS MANESAR", range5);
  if (hasData(range6.slice(1))) sendTableEmail(email6, cc6, "PRDN. PLAN DATE N/A ==ORDER FMS MANESAR", range6);
  if (hasData(range7.slice(1))) sendTableEmail(email7, cc7, "PRDN. PLAN DATE N/A ==ORDER FMS MANESAR", range7);
  if (hasData(range8.slice(1))) sendTableEmail(email8, cc8, "DISP. Plan Failure ==ORDER FMS MANESAR", range8);
}

// Check for any non-empty cell
function hasData(range) {
  return range.some(row => row.some(cell => String(cell).trim() !== ""));
}

// Format date to "6-Feb-2025"
function formatDate(date) {
  if (Object.prototype.toString.call(date) === "[object Date]" && !isNaN(date)) {
    var day = date.getDate();
    var month = date.toLocaleString('default', { month: 'short' });
    var year = date.getFullYear();
    return day + '-' + month + '-' + year;
  }
  return date;
}

// Send filtered table data via email
function sendTableEmail(email, cc, subject, rangeData) {
  var sheetLink = "https://docs.google.com/spreadsheets/d/1_VxfBsQAQ6zvFNkWdYW37-z-5oL2a4GRqGJB8h6GT5I/edit?gid=0#gid=0";
  
  var htmlBody = "<p>Please add details in your respective ORDER FMS MANESAR sheet.</p>";
  htmlBody += "<p><a href='" + sheetLink + "' target='_blank'><b>Click here to update</b></a></p>";

  htmlBody += "<table border='1' style='border-collapse: collapse;'>";

  //  Filter out empty rows except for the header
  var filteredData = rangeData.filter((row, index) => {
    if (index === 0) return true; // keep header
    return row.some(cell => String(cell).trim() !== "");
  });

  // Build table
  for (var i = 0; i < filteredData.length; i++) {
    htmlBody += "<tr>";
    for (var j = 0; j < filteredData[i].length; j++) {
      var cellValue = filteredData[i][j];
      if (Object.prototype.toString.call(cellValue) === "[object Date]" && !isNaN(cellValue)) {
        cellValue = formatDate(cellValue);
      }
      htmlBody += "<td><b>" + cellValue + "</b></td>";
    }
    htmlBody += "</tr>";
  }

  htmlBody += "</table>";

  // Send the email
  MailApp.sendEmail({
    to: email,
    cc: cc,
    subject: subject,
    htmlBody: htmlBody
  });
}