How can I send multiple emails to different individuals in appscripts-CodePudding

function reminder() {
  var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  var data = sh.getDataRange().getValues()
  var d = new Date().getTime();
  for (var i=1;i<data.length;i  ){
    if (data[i][4]<=new Date(d 7*24*60*60*1000) && data[i][4]>=new Date(d 5*24*60*60*1000) && data[i][6]==''){
      MailApp.sendEmail({to:data[i][3],
        subject: 'Reminder Process Update Required in 1 week',
        htmlBody: 'Hello ' data[i][2] ', the  process page for <b>' data[i][1] '</b> is due for review in 1 week. Please review the content and contact the Process team before its due date if amendments are required.'
      })
      sh.getRange(i 1,7).setValue('sent')
    }
    else if (data[i][4]<=new Date(d 30*24*60*60*1000) && data[i][4]>=new Date(d 28*24*60*60*1000) && data[i][5]==''){
      MailApp.sendEmail({to:data[i][3],
        subject: 'Reminder Process Update Required in 1 month',
        htmlBody: 'Hello ' data[i][2] ', the process page for <b>' data[i][1] '</b> is due for review in review in 1 month. Please review the content and contact the Process Mapping team before its due date if amendments are required.'
      })
      sh.getRange(i 1,6).setValue('sent')
    }
  }
}

So currently its sending an email to "data[i][3]" I would also like to add up to 2 further recipients that it would email alongside what is there but also if these fields are left blank it should then only issue the email to the field that is filled in.

Heres a link to the demo of the sheet and script - https://docs.google.com/spreadsheets/d/1Qw8WefbVkS-AQXi1CcZ0z2CL-P0oNSZYqeT40oVF6go/edit?usp=sharing

CodePudding user response：

I believe your goal is as follows.

You want to use the recipient email addresses of the values of columns "D" to "F".
In your showing script, you are using the columns "A" to "G". In this case, the email address is the column "D". But in your question, the email addresses are the columns "D" to "F". And, you want to use the columns "A" to "I". Namely, you added 2 columns to the showing script.

In this case, how about the following modification?

Modified script:

In this modification, the values of columns "D" to "F" are used as the recipient email addresses. When the values of columns "D" to "F" are no values, MailApp.sendEmail is not run. And, for your new structure of Spreadsheet, the index for each row in the for loop was modified.

function reminder() {
  var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sh.getDataRange().getValues();
  var d = new Date().getTime();
  for (var i = 1; i < data.length; i  ) {
    var recipients = data[i].slice(3, 6).filter(String).join(",");
    if (recipients == "") continue;
    if (data[i][6] <= new Date(d   7 * 24 * 60 * 60 * 1000) && data[i][6] >= new Date(d   5 * 24 * 60 * 60 * 1000) && data[i][7] == '') {
      MailApp.sendEmail({
        to: recipients,
        subject: 'Reminder Process Update Required in 1 week',
        htmlBody: 'Hello '   data[i][2]   ', the  process page for <b>'   data[i][1]   '</b> is due for review in 1 week. Please review the content and contact the Process team before its due date if amendments are required.'
      });
      sh.getRange(i   1, 9).setValue('sent');
    }
    else if (data[i][6] <= new Date(d   30 * 24 * 60 * 60 * 1000) && data[i][6] >= new Date(d   28 * 24 * 60 * 60 * 1000) && data[i][7] == '') {
      MailApp.sendEmail({
        to: recipients,
        subject: 'Reminder Process Update Required in 1 month',
        htmlBody: 'Hello '   data[i][2]   ', the process page for <b>'   data[i][1]   '</b> is due for review in review in 1 month. Please review the content and contact the Process Mapping team before its due date if amendments are required.'
      });
      sh.getRange(i   1, 8).setValue('sent');
    }
  }
}

Note:

This modified script supposes that your showing script works for the Spreadsheet of the columns "A" to "G". Please be careful about this.
This modified script is for your sample Spreadsheet. When you changed the structure of the Spreadsheet, this script might not be able to be used. So please be careful about this.

Reference:

sendEmail(message)