Join data from multiple columns and send email alert from google script-CodePudding

I have the following sheet with multiple columns and rows of data. I want to send email alerts with data from more than 1 column (column A and D) and I have currently been able to achieve this for data from 1 column(column A). How can we map multiple columns in google script and add the data to the email?

Thanks in advance.

function sendEmails() 
{
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 1; // First row of data to process
  var numRows = 300; // Number of rows to process
  // Fetch the range of cells A1:P300
  var dataRange = sheet.getRange(startRow, 1, numRows, 16);
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  var dayofemail = data[0][15];
  data.reduce((m, [qua_num,,,,,,,,daysPastCal,emailAddress]) => {
  if (daysPastCal > -14) m.set(emailAddress, m.has(emailAddress) ? [...m.get(emailAddress), qua_num] : [qua_num]);
  return m;
}, new Map())
.forEach((qua_num, emailAddress) => {
  var message = qua_num.join("\n")   '\nThe above equipment are approaching due date or out of cal.\nPlease check with QA for replacement or sending the equipment out for re-calibration.';
var subject = 'AUTOMATED ALERT: CHECK EQUIPMENT CALIBRATION ';
  MailApp.sendEmail(emailAddress, subject   dayofemail, message);
});
}

CodePudding user response：

In your script, how about the following script?

From:

  data.reduce((m, [qua_num,,,,,,,,daysPastCal,emailAddress]) => {
  if (daysPastCal > -14) m.set(emailAddress, m.has(emailAddress) ? [...m.get(emailAddress), qua_num] : [qua_num]);
  return m;
}, new Map())

To:

data.reduce((m, [qua_num, , , serial, , , , , daysPastCal, emailAddress]) => {
  if (daysPastCal > -14) m.set(emailAddress, m.has(emailAddress) ? [...m.get(emailAddress), qua_num, serial] : [qua_num, serial]);
  return m;
}, new Map())

By this modification, the value of the column "D" is added. So, qua_num of var message = qua_num.join("\n") '\nThe above equipment are approaching due date or out of cal.\nPlease check with QA for replacement or sending the equipment out for re-calibration.' has 2 values of the column "A" and "D".