How to write in the same cell where 'progress' is written. It must write only, on the cells where the conditions are met? Could someone help me?

Question

This yields in sending emails with the details, whose due dates are due. Also, I want to change the status from 'Pogress' to 'Forwarded' to know the details have been forwarded.

function myEmailBot() {
  var ss = SpreadsheetApp.getActiveSheet();
  //var rangeList = ss.getRangeList(['C2:D3', 'L2:M2']);
  //rangeList.activate();
  var nameData = ss.getRange("C2:C").getValues();
  var addressData = ss.getRange("D2:D").getValues();
  var mobData = ss.getRange("E2:E").getValues();
  var vaccineData = ss.getRange("K2:K").getValues();
  var fdoseData = ss.getRange("L2:L").getValues();
  var dueData = ss.getRange("M2:M").getValues();
  var rDaysData = ss.getRange("O2:O").getValues();
  var status = ss.getRange("R2:R").getValues();

  for ( i = 0; i <= 492; i++){

    if(dueData [i]== "OD" & status[i] == "Forwarded") {
      //Logger.log("Horray");
      //return;
    } else if (dueData [i] == "OD" & status [i] == "Progress") {

      var name = nameData[i];
      var address = addressData[i];
      var mob = mobData[i];
      var vaccine = vaccineData[i];
      var fDose = fdoseData[i];
      var rDays = rDaysData[i];

      let msg = "Name: " + name + "\r\n" + "Address: " + address + "\r\n" + "Mob: " + mob + "\r\n" + "Vaccine:     " + vaccine + "\r\n" + "First Dose Date: " + fDose + "\r\n" + "Remaning Days: " + rDays ;


      //GmailApp.sendEmail("[email protected]", "Vaccination Due", msg)
      var test = ss.getRange(2 + i, 17 ).setValue("Forwarded");
      Logger.log(test);
    }
    else {
     //Logger.log("Due Date Not Reached");
    }
  }
}

Answer 1

function myEmailBot() {
  const ss = SpreadsheetApp.getActive();
  const sh = SpreadsheetApp.getActiveSheet();
  const vs = sh.getRange(2, 1, sh.getLastRow() - 1, sh.getLastColumn()).getValues();
  let status = vs.map(r => [r[17]]);
  vs.forEach((r, i) => {
    if (r[10] == "OD" && status[i] == "Progress") {
      status[i] = "Forwarded";
      let msg = "Name: " + r[2] + "\r\n" + "Address: " + r[3] + "\r\n" + "Mob: " + r[4] + "\r\n" + "Vaccine:     " + r[10] + "\r\n" + "First Dose Date: " + r[1] + "\r\n" + "Remaning Days: " + r[14];
      GmailApp.sendEmail("[email protected]", "Vaccination Due", msg)
    }
  });
  sh.getRange(2, 18, status.length, 1).setValues(status);
}

Answer 2

The only problem I can suggest here: if you figure on changing column R (I don't know it's just my guess) you need to change this line:

var test = ss.getRange(2 + i, 17 ).setValue("Forwarded");

this way:

var test = ss.getRange(2 + i, 18 ).setValue("Forwarded");

Since R is 18th column in this case.