Vishnukumar
New member
I have attached the individual mail coding for reference
[Solved] Collate list of Pending actions and sent together in email, i have code for single mail for each pending actions based on date
- Thread starter Vishnukumar
- Start date
Hello Vishnukumar,
Thanks for reaching us. I understand you want to collate pending actions and send them in a single email instead of individual mail for each pending action. We can accomplish this by filtering the pending actions first and then sending the filtered data in tabular format via Email.
From the code you provided, I understand that your dataset consists of machine calibration data and you want to notify the responsible person about the expiration of machine calibration. To solve your problem, I have taken a similar dataset in my Google Sheets spreadsheet.
From your code, I also understand that you were trying to filter machine calibration data in two groups: expiration within two weeks and expiration within one month. To achieve your required result, we can use the following Google Apps Script code:
Save and Run this code. Provide necessary permissions for Apps Script to access your Spreadsheet and Gmail App.
After executing the script, the responsible person (email id that you provided) will receive one or two emails like the following depending on the expiration date of machine calibrations.
The link to the Google Sheets file I used is given below:
Collate Pending Tasks and Send Them via Gmail
Hopefully, this will solve your problem. Let us know your feedback.
Regards,
Seemanto Saha
ExcelDemy
Thanks for reaching us. I understand you want to collate pending actions and send them in a single email instead of individual mail for each pending action. We can accomplish this by filtering the pending actions first and then sending the filtered data in tabular format via Email.
From the code you provided, I understand that your dataset consists of machine calibration data and you want to notify the responsible person about the expiration of machine calibration. To solve your problem, I have taken a similar dataset in my Google Sheets spreadsheet.
From your code, I also understand that you were trying to filter machine calibration data in two groups: expiration within two weeks and expiration within one month. To achieve your required result, we can use the following Google Apps Script code:
JavaScript:
function CollatePendingTasks(){
//Define spreadsheet and source data sheet
var spst = SpreadsheetApp.getActiveSpreadsheet();
var dataSheet = spst.getSheetByName("Calibration Data"); // Insert the name of your Data Sheet source
// 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();
//select data range
var dataRange = dataSheet.getRange(startRow,strtCol,lastRow-1,lastCol);
var data = dataRange.getValues();
// helper parameters
var expirationDateCol = 7;
var checkTwoWeekExpire = 1;
var checkOneMonthExpire = 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]<=afterTwoWeeksDate){
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){
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');
// 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();
// Email the first filtered group
if(ss1){
var twLastRow = ss1.getLastRow;
var twRange = ss1.getRange(1,1,checkTwoWeekExpire,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, "", {
htmlBody: twBody,
from: sender
});
spst.deleteSheet(ss1);
}
// Email the second filtered group
if(ss2){
var omLastRow = ss2.getLastRow;
var omRange = ss2.getRange(1,1,checkOneMonthExpire,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, "", {
htmlBody: omBody,
from: sender
});
spst.deleteSheet(ss2);
}
}
// 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;
}Save and Run this code. Provide necessary permissions for Apps Script to access your Spreadsheet and Gmail App.
After executing the script, the responsible person (email id that you provided) will receive one or two emails like the following depending on the expiration date of machine calibrations.
The link to the Google Sheets file I used is given below:
Collate Pending Tasks and Send Them via Gmail
Hopefully, this will solve your problem. Let us know your feedback.
Regards,
Seemanto Saha
ExcelDemy
Vishnukumar
New member
Seemanto Saha Thanks for the reply, i gone through the content, when i execute the same i am getting mail, when i mentioned the date as past date i.e.., 12 june 2023 with respect to todays date i.e.., 14 june 2023, i am not getting mail so i added same coding again for pastdates and execute i am getting 3mails for past date, two weeks, one month all three are executing, how can i resolve this, if past date means that alone have to execute, within two weeks means again another mail with one month is not necessary. if possible can i able to learn this coding by any chance through any assistance
Thanks for giving us your feedback. I understand that you also want to filter the rows for which machine calibration has already expired. I modified a few Expiration Dates in my dataset for this.
You also don’t want the same rows in multiple emails. This can be accomplished by adding some AND criteria in the IF statements. The modified code is given below:
JavaScript:
function CollatePendingTasks() {
//Define spreadsheet and source data sheet
var spst = SpreadsheetApp.getActiveSpreadsheet();
var dataSheet = spst.getSheetByName("Calibration Data"); // 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 = 7; // 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();
// Email the first filtered group
if (ss1) {
var twLastRow = ss1.getLastRow;
var twRange = ss1.getRange(1, 1, checkTwoWeekExpire, 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, "", {
htmlBody: twBody,
from: sender
});
spst.deleteSheet(ss1);
}
// Email the second filtered group
if (ss2) {
var omLastRow = ss2.getLastRow;
var omRange = ss2.getRange(1, 1, checkOneMonthExpire, 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, "", {
htmlBody: omBody,
from: sender
});
spst.deleteSheet(ss2);
}
if (ss3) {
var uwLastRow = ss3.getLastRow;
var uwRange = ss3.getRange(1, 1, checkbeforeYesterday, expirationDateCol);
var uwData = uwRange.getValues();
var uwTable = formatDataAsTable(uwData);
var uwBody = "Machine Calibration Expired,Top Priority." + uwTable;
GmailApp.sendEmail(recipient, subject, "", {
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;
}Email:1
Collate Pending Tasks and Send Them via Email
However, I understand that your dataset is different than mine and you may require specific columns of the dataset via email. You can share your Spreadsheet with us for that.
Let us know your feedback.
Regards,
Seemanto Saha
ExcelDemy
Vishnukumar
New member
Thanks Seemanto Saha, this code is perfect if i made as per sample, one final Doubt in the coding Var Start Row = 2, if i have to add(insert) Some description in the first row then all other data will come one row down, that time coding is shifting/Skipping one row, let us say i started description of table in Row no-02 followed by monitered datas i.e.., machine and calibration dates(We can consider your sample data also). how can i add that first line description along with existing data in the auto email,
if i have to add cc(Copy) in the mail to include others, what i should do
Again Thanks a lot for the support.......!!!!!
Good day.......!!!!!!
if i have to add cc(Copy) in the mail to include others, what i should do
Again Thanks a lot for the support.......!!!!!
Good day.......!!!!!!
Glad we were able to help you.
About your doubt, I understand that you want to add some description in the first row and shift other data to one row below. I inserted a table header to my data to demonstrate your problem.
You also wanted to add a CC in the sent mail. The modified Apps Script for these requirements is given below:
After the execution of the script, I received 3 emails. We can see the changes in the data table:
An additional email is also sent to the CC address.
The link to the Spreadsheet I used is given below. Let us know your feedback.
Collate Pending Tasks and Send Them via Email with CC
Regards,
Seemanto Saha
ExcelDemy
About your doubt, I understand that you want to add some description in the first row and shift other data to one row below. I inserted a table header to my data to demonstrate your problem.
You also wanted to add a CC in the sent mail. The modified Apps Script for these requirements is given below:
JavaScript:
function CollatePendingTasks() {
//Define spreadsheet and source data sheet
var spst = SpreadsheetApp.getActiveSpreadsheet();
var dataSheet = spst.getSheetByName("Calibration Data"); // 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 = 3;
var strtCol = 1;
var lastRow = dataSheet.getLastRow();
var lastCol = dataSheet.getLastColumn();
var expirationDateCol = 7; // 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, 2, lastCol);
headerRow.copyTo(pastDateSheet.getRange("A1"));
}
var expiredData = dataSheet.getRange(i + 2, 1, 1, lastCol);
expiredData.copyTo(pastDateSheet.getRange(checkbeforeYesterday+1, 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, 2, lastCol);
headerRow.copyTo(twoWeekSheet.getRange("A1"));
}
var twoWeekData = dataSheet.getRange(i + 2, 1, 1, lastCol);
twoWeekData.copyTo(twoWeekSheet.getRange(checkTwoWeekExpire+1, 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, 2, lastCol);
newHeaderRow.copyTo(oneMonthSheet.getRange("A1"));
}
var oneMonthData = dataSheet.getRange(i + 2, 1, 1, lastCol);
oneMonthData.copyTo(oneMonthSheet.getRange(checkOneMonthExpire+1, 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, checkTwoWeekExpire, 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, checkOneMonthExpire, 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, checkbeforeYesterday, expirationDateCol);
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 fstRow = data[0].shift()
var table = "<table style='border-collapse: collapse;'>";
table += "<tr>";
table += "<th colspan='7' style='text-align: center; border: 1px solid black; padding: 5px;'>" + fstRow + "</th>";
table += "</tr>";
for (var i = 1; 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 == 1) {
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;
}An additional email is also sent to the CC address.
The link to the Spreadsheet I used is given below. Let us know your feedback.
Collate Pending Tasks and Send Them via Email with CC
Regards,
Seemanto Saha
ExcelDemy
Vishnukumar
New member
i just shared the screen shot of the mail i recieved header is ok, but description is repeated as two times, Machine 14 came in already expired but it was aug 03 2023 only should not come.
Row No 3 & 4 Expiration date i changed remaining are same as your shared file, sorry i didnt shared last time, you just try by changing row 3 & 4 from the data first two expiration yellow as highlighted,
after you add header this problem is coming if you check without header and keeping var Start row =2 it will work but i didnt changed intermediate rows to confirm as per the above picture you kept, thanks and sorry hope i am not irritating you?????
in the mail ensure already expired, within two weeks, within one month datas and collated datas in the mail are matches, datas i referred to total no of dates that are set
Sorry for the inconvenience. After adding the table header, I forgot to increase the filtered range parameters by 1. That's why column headers were repeated. The updated Apps Script is given below:
The link to the spreadsheet is given below:
Collate Pending Actions and Send Emails
Thanks for having patience and staying with us. Let us know your feedback.
Regards,
Seemanto Saha
JavaScript:
function CollatePendingTasks() {
//Define spreadsheet and source data sheet
var spst = SpreadsheetApp.getActiveSpreadsheet();
var dataSheet = spst.getSheetByName("Calibration Data"); // 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 = 3;
var strtCol = 1;
var lastRow = dataSheet.getLastRow();
var lastCol = dataSheet.getLastColumn();
var expirationDateCol = 7; // 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, 2, lastCol);
headerRow.copyTo(pastDateSheet.getRange("A1"));
}
var expiredData = dataSheet.getRange(i + 3, 1, 1, lastCol);
expiredData.copyTo(pastDateSheet.getRange(checkbeforeYesterday+1, 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, 2, lastCol);
headerRow.copyTo(twoWeekSheet.getRange("A1"));
}
var twoWeekData = dataSheet.getRange(i + 3, 1, 1, lastCol);
twoWeekData.copyTo(twoWeekSheet.getRange(checkTwoWeekExpire+1, 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, 2, lastCol);
newHeaderRow.copyTo(oneMonthSheet.getRange("A1"));
}
var oneMonthData = dataSheet.getRange(i + 3, 1, 1, lastCol);
oneMonthData.copyTo(oneMonthSheet.getRange(checkOneMonthExpire+1, 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, expirationDateCol);
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 fstRow = data[0].shift()
var table = "<table style='border-collapse: collapse;'>";
table += "<tr>";
table += "<th colspan='7' style='text-align: center; border: 1px solid black; padding: 5px;'>" + fstRow + "</th>";
table += "</tr>";
for (var i = 1; 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 == 1) {
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;
}Collate Pending Actions and Send Emails
Thanks for having patience and staying with us. Let us know your feedback.
Regards,
Seemanto Saha
Vishnukumar
New member
Hi i have a doubt with above code its working fine with 30 Rows x 20 Columns, will it work up to 150 Rows x 20Columns or do we have any restriction in number of rows and columns. i need it as maximum number of rows and columns
Last edited:
Hello Vishnukumar,
Welcome back to ExcelDemy Forum.
Google Sheets support a maximum of 10,000,000 cells. So, based on the number of cells available, you can add as many rows as you want. However, the maximum number of columns supported in Google Sheets is 18,278. Hence, the above code should work properly for 150 Rows x 20 Columns.
Regards,
Seemanto Saha
ExcelDemy
Google Sheets support a maximum of 10,000,000 cells. So, based on the number of cells available, you can add as many rows as you want. However, the maximum number of columns supported in Google Sheets is 18,278. Hence, the above code should work properly for 150 Rows x 20 Columns.
Regards,
Seemanto Saha
ExcelDemy
Vishnukumar
New member
i was exceed my limit sending Mail, so tomorrow i will check earlier mistakenly i set for Every Hour, Per Candidature 130Mail * 7Memebers Went, last mail trail i found 68Rows added not more than that for checking, i dont know due to Mail sent limit exceeded or other reasons, i need to check up totally 1632 Cell data was transferred to mail which have 68 Row * 24 Columns totally i need to add up 141 Rows * 24Columns i will Check tomorrow and tell you in future may add so only asked how much data i can have
Vishnukumar
New member
Please check the code, First Row is Definition of Each Columns, 15th Column is expiring date, I am using Cells A to X Row From 1 to 143 today simulated i am getting one mail with 66rows fully (Actual should be 142 rows with A to X Columns and another mail with below mentioned images came i.e.., without data only tabular column but that should not come as Calibration Expired, calibration expiry date set for trail is 04-10-2023, can you support me in this regard
Dear Vishnukumar,
I checked your code. There were a few small discrepancies in your code. I edited those errors and tried the code for a dataset that contains 40 rows and 10 columns.
Here is the updated code for your file:
Click the link below to create a copy of the worksheet I used for this problem.
Best Regards,
Seemanto Saha
ExcelDemy
I checked your code. There were a few small discrepancies in your code. I edited those errors and tried the code for a dataset that contains 40 rows and 10 columns.
Here is the updated code for your file:
JavaScript:
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 = 15; // 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;
}Click the link below to create a copy of the worksheet I used for this problem.
Best Regards,
Seemanto Saha
ExcelDemy
Vishnukumar
New member
earlier itself i am getting 67 Rows X 24 Columns, can you check up please i need 150 Rows and 24 Columns Can you copy paste in your excel data upto 150Rows and see are u getting data in your mail
Dear Vishnukumar,
I checked the code for 150 rows and 13 columns. After executing the code, I received the mails properly. Here is a sample mail:
All machines that require calibration within one month are listed properly here. Below is the link to the Spreadsheet used for this solution.
New Updated Code for Collate Pending Tasks and Send Via Mail
Regards,
Seemanto Saha
ExcelDemy
I checked the code for 150 rows and 13 columns. After executing the code, I received the mails properly. Here is a sample mail:
All machines that require calibration within one month are listed properly here. Below is the link to the Spreadsheet used for this solution.New Updated Code for Collate Pending Tasks and Send Via Mail
Regards,
Seemanto Saha
ExcelDemy
Vishnukumar
New member
I just gone through the contents my problem is I have small portable equipments of 150nos all are given in date of expiry as same day, when the day reaches the limit, i need all 150 rows in mail not whatever the expired in 150, bcoz all 150 are same date calibration is happening by different team, I am getting one mail with 67rows as I mentioned earlier another mail with empty data rows and columns, i need all 150Rows in Mail
Last edited:
Dear Vishnukumar,
I set the same expiration date for all the rows and then executed the code. Only one mail was sent. However, I got 127 complete rows and 1 partial row (like in the following image). This is because the message is clipped. We can click the View entire message command to view all 150 rows.
The following video shows an overview of the process.
The link to the spreadsheet is also given below:
Regards,
Seemanto Saha
ExcelDemy
I set the same expiration date for all the rows and then executed the code. Only one mail was sent. However, I got 127 complete rows and 1 partial row (like in the following image). This is because the message is clipped. We can click the View entire message command to view all 150 rows.
The following video shows an overview of the process.
The link to the spreadsheet is also given below:
Regards,
Seemanto Saha
ExcelDemy
Vishnukumar
New member
Yes Thanks Dude for the Support, I didnt notice "view entire message" in the mail mine also coming
Vishnukumar
New member
Can you check for Already expired Content for me I am getting Header Rows not below to that, it seems, its checking yesterday date only , if expired can i get details of the same, not just monitoring yesterday.
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.
JavaScript:
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;
}Seemanto Saha
ExcelDemy