Google Sheets - email with dynamic message tags-CodePudding

I'm using a Google Sheet to send email notifications (a Google Form is not an option here). I'm having a problem with the message text.

Essentially, the spreadsheet has new rows added based on user data. It adds five columns:

The goal is that the spreadsheet then sends an email to each student, telling them where to report. It should email each student only once, but for each email, the Name, Academy teacher, and Requesting Teacher fields should be dynamic.

What works: if you only have one row of data (or one new row), it sends the correct information, and logs that they were notified. Whether emailing one individual or multiple people (one new row or many), it at least sends a message, and logs that the message was sent.

What doesn't work: if you have more than one row of new data, it emails all of the people, but the Name, Academy teacher, and Requesting teacher variables in each email refer only to the first new row of data. So, Harry, Seamus, and Hermione all get emails to their correct addresses, but the text of the emails are all identical, and reflect Harry's meeting, rather than being dynamic for each. Again, if only one new row has been added, this problem is absent.

I would prefer not to use an onEdit tag, which I know could solve the problem. Data entry errors happen, and people can change their minds. I'd rather set the function to trigger every 10 minutes.

Here's the code I've cobbled together, based on other coding I've seen:

function SendEmail() {
var ss = SpreadsheetApp.getActiveSpreadsheet()
var sheet1=ss.getSheetByName('ConcatenatedReport');
var subject = sheet1.getRange(2,7).getValue();
var message = sheet1.getRange(2,9).getValue();
var n=sheet1.getLastRow();
for (var i = 2; i < n 1 ; i   ) {
var name=sheet1.getRange(i,1).getValue();
var teacher=sheet1.getRange(i,4).getValue();
var instructor=sheet1.getRange(i,3).getValue();
var teacher1=sheet1.getRange(i,4).getValue();
var emailAddress = sheet1.getRange(i,5).getValue();
var emailstatus = sheet1.getRange(i,6).getValue();
if(emailstatus == ""){ 
message=message.replace("<name>",name).replace("<teacher>",teacher).replace("<instructor>",instructor).replace("<teacher1>",teacher1);
MailApp.sendEmail(emailAddress, subject, message);
sheet1.getRange(i,6).setValue("Notified");}}}

The Notified value keeps us from emailing the same person over and over again. The message reads as follows:

Hello name variable, This email is to inform you that you have an appointment with one of your professors (teacher variable), during your next homeroom class. Check in with your homeroom professor (instructor variable) immediately after your third hour, then go straight to teacher1 variable's room as soon as you're told to do so.

Where am I going wrong? How do I get the email to be correctly tailored to each student?

PS - I'd also really like it to just use their first name in the email, rather than Last, First Middle. Is there any way to do that? I'm pretty weak in my coding skills, so there's a lot I don't know. Any help is much appreciated!

CodePudding user response：

I believe your goal is as follows.

You want to send the emails by retrieving the values from the cells "A2:F". And, when the column "F" has empty, you want to send the email.

In this case, how about the following modified script?

Modified script:

function SendEmail() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet1 = ss.getSheetByName('ConcatenatedReport');
  var [subject,,message] = sheet1.getRange(2, 7, 1, 3).getValues()[0];
  var lastRow = sheet1.getLastRow();
  var values = sheet1.getRange("A2:F"   lastRow).getValues();
  values.forEach(([name,,instructor,teacher,emailAddress,emailstatus]) => {
    if (emailstatus == "") {
      var temp = message;
      var teacher1 = teacher;
      temp = temp.replace("<name>", name).replace("<teacher>", teacher).replace("<instructor>", instructor).replace("<teacher1>", teacher1);
      MailApp.sendEmail(emailAddress, subject, temp);
    }
  });
  sheet1.getRange("F2:F"   lastRow).setValue("Notified");
}

In your script, by message=message.replace("<name>",name)..., message is overwritten by 1st loop. By this, after 2nd loop, messaeg is not updated. I thought that this might be the reason for your issue.
And also, when getValues is used, the process cost will become low.

References:

CodePudding user response：

You are almost there

function SendEmail() {
var ss = SpreadsheetApp.getActiveSpreadsheet()
var sheet1=ss.getSheetByName('ConcatenatedReport');
var data = sheet1.getDataRange().getValues();
var subject = data[1][6]
//var message = sheet1.getRange(2,9).getValue();
 for (i=1; i<data.length; i  ) {
  if(data[i][5] == ""){ 
     message="Hello "   data[i][1] ", This email is to inform you that you have an appointment with one of your professors "   data[i][2] ", during your next homeroom class. Check in with your homeroom professor "   data[i][3] " immediately after your third hour, then go straight to "   data[i][4] "'s room as soon as you're told to do so.";
     MailApp.sendEmail(data[i][4], subject, message);
     sheet1.getRange((i 1),6).setValue("Notified");
  }
 }
}