CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsautomationgoogle-sheets-formulacloud-automation-manager
FRT Kerala
FRT Kerala

Reputation: 1

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?

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");
    }
  }
}

Upvotes: -1

Views: 51

Answers (2)

Cooper
Cooper

Reputation: 64082

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);
}

Upvotes: 1

Yuri Khristich
Yuri Khristich

Reputation: 14537

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.

Upvotes: 1

Related Questions