Update Sheet Based on Email Send-CodePudding

We want to send tutors an email with student info every time they fill out a form. When a tutor fills out the form for a student, the tutors email populates in column E for that student. Then, column F is changed to "No." We want to send emails only to tutors who have "No" in column F.

Currently, our function serves this purpose. However, we also want that after an email is sent, it changes the "No" in colF to "Yes" to prevent future emails. We tried to use a forloop but we aren't too savvy with coding, and it does not change "No" to "Yes" at all. Thanks in advance for any help!

function newmatchEmail() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet1=ss.getSheetByName('Active Student List');
var data=sheet1.getDataRange().getValues();

data.forEach(function(row,col){
if (col == '') return;
if (row[0] == '') return; //If ColA is empty skip
if (row[4] == '') return; //If ColE is empty skip
if (row[5] == "Yes") return; //If ColF is "Yes" skip
if (row[5] ==  "") return; //If ColF is empty skip
if (row[5] == "No") // we want ONLY tutors that have "No" in column F to receive an email

  var studentinfo = row[0];
  var studentname = row[2];
  var studentemail = row[3];

  var tutoremail = row[4];
  var subject = "Student Opening Request";
  var messagetemplate = sheet1.getRange(2,8).getValue();

  message=messagetemplate.replace("<studentinfo>",studentinfo).replace("<studentname>",studentname).replace("<studentemail>",studentemail);

  MailApp.sendEmail(tutoremail, subject, message);
  var ws = SpreadsheetApp.getActiveSpreadsheet();
  var sh=ws.getSheetByName('Active Student List');
  var values = sh.getRange("F2:F").getValues();
  for(var i in values){
       if (newvalues[i][0] == "No"){
         values.setValue([i][0]=="Yes");}} // something's not quite right here
})
}

Sheet Link Image of Sheet

CodePudding user response：

In your situation, how about the following modification?

Modified script:

function newmatchEmail() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName('Active Student List');
  var data = sheet1.getDataRange().getValues();
  var rangeList = data.map(function (row, i) {
    if (row[0] == '' || row[4] == '' || row[5] == "" || row[5] == "Yes") return "";
    var studentinfo = row[0];
    var studentname = row[2];
    var studentemail = row[3];
    var tutoremail = row[4];
    var subject = "Student Opening Request";
    var messagetemplate = sheet1.getRange(2, 8).getValue();
    message = messagetemplate.replace("<studentinfo>", studentinfo).replace("<studentname>", studentname).replace("<studentemail>", studentemail);
    MailApp.sendEmail(tutoremail, subject, message);
    return "F"   (i   1);
  }).filter(String);
  if (rangeList.length == 0) return;
  sheet1.getRangeList(rangeList).setValue("Yes");
}

In this modification, when the email is sent, Yes is put to the column "F" of the same row.
- When setValue is used in the loop, the process cost becomes high. So I used the value using RangeList.

References:

CodePudding user response：

Hey since you have already skipped the rows that are "Yes" and "", you can just use .setValue().

MailApp.sendEmail(tutoremail, subject, message);
data.getRange(col   1, 6).setValue("Yes");