CODEWITHSUNDEEP
google-apps-script
Goutham
Goutham

Reputation: 29

Send to multiple emails using google script

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2; // First row of data to process
  var numRows = 5; // Number of rows to process
  // Fetch the range of cells A2:I6
  var dataRange = sheet.getRange(startRow, 1, numRows, 10);
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  var counter = 0;
  var qua_num = [[]];
  for (var i in data) {
    var row = data[i];
    var days = row[8];
    if (days > -14) {
    var emailAddress = row[9]; // First column
      qua_num += [row[0]];
      counter += 1;
    }
  }
  var message = qua_num + ' equipment are approaching due date or out of cal. Please check with QA for replacement or sending the equipment out for re-calibration'; // Second column
  var subject = 'Check Equipment Calibration Alert';
  MailApp.sendEmail(emailAddress, subject, message);
}

The current code lets me send only to one email address from google spreadsheet. If there is a different email address in each row (multiple rows could have the same email address as well), how do I change the function? See example of the google spreadsheet below.

Screenshot of Google Spreadsheet

Based on the information in the spreadsheet, QUA-005 and QUA-007 meet the condition of the if statement. The expected output would be:

Email 1 to emailaddress 1 QUA-005 equipment are approaching due date or out of cal. Please check with QA for replacement or sending the equipment out for re-calibration.

Email 2 to emailaddress 2 QUA-007 equipment are approaching due date or out of cal. Please check with QA for replacement or sending the equipment out for re-calibration.

If the email address is the same, then the output would be:

QUA-005, QUA-007 equipment are approaching due date or out of cal. Please check with QA for replacement or sending the equipment out for re-calibration.

Upvotes: 1

Views: 281

Answers (1)

Tanaike
Tanaike

Reputation: 201378

  • You want to send emails to the email address of the column "J", when the value of column "I" is more than -14.
  • When there are the same email addresses of the column "J", you want to aggregate the values of column "A" and want to send them to one email address.
  • You want to achieve this using Google Apps Script.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Modification points:

  • In this modification, at first, an object is created from the values retrieved from Spreadsheet. Then, using the object, the content of email is created and send it.

Modified script:

When your script is modified, please modify as follows.

From:

var counter = 0;
var qua_num = [[]];
for (var i in data) {
  var row = data[i];
  var days = row[8];
  if (days > -14) {
  var emailAddress = row[9]; // First column
    qua_num += [row[0]];
    counter += 1;
  }
}
var message = qua_num + ' equipment are approaching due date or out of cal. Please check with QA for replacement or sending the equipment out for re-calibration'; // Second column
var subject = 'Check Equipment Calibration Alert';
MailApp.sendEmail(emailAddress, subject, message);

To:

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(",") + ' equipment are approaching due date or out of cal. Please check with QA for replacement or sending the equipment out for re-calibration';
  var subject = 'Check Equipment Calibration Alert';
  MailApp.sendEmail(emailAddress, subject, message);
});

Note:

  • In this case, please enable V8 at the script editor.

References:

Upvotes: 2

Related Questions