Change cell value on button click to column value based on a criteria

Question

I would like to change the value in cell G2 by clicking on a button (D3). The (A-column) values that are being looped through should meet the criteria "yes" in the column B.

Answer 1

The OP's goal is to loop through the list of names stopping on each name with a value of "yes".

Obviously this requires skipping over the names with a value of "no". And there is a special challenge when dealing with the last name in the list AND that name having a "select" value of "no". In that case, the loop should "restart" at the top of the list.

The following code address each of these contingencies.

Using the sample data shown below; the code should stop on Paul, Dave, Brett and Ted.
If the value in Cell G2 is "Ted", then the next selected name should be Paul.

---

Sample data screenshot

---

function so5794149403() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('57941494');
  var rg=sh.getRange(2,1,sh.getLastRow()-1,2);
  var vA=rg.getValues();
  var vAnum = vA.length;

  var names = sh.getRange(2,1,sh.getLastRow()-1,1).getValues();
  //flatten the array
  var users = names.map(function (row) { return row[0]; }); 

  // set some variables
  var i=0;
  var failedNTLN = 0; // fail but Not The Last Name
  var modifiedTLN = 0;// fail but The Last Name

  //Loop through names
  for (i;i<names.length;i++){
    //Logger.log("DEBUG:0:i = "+i+" and failedNTLN = "+failedNTLN+" and modifiedTLN = "+modifiedTLN);

    if (failedNTLN ==99){
      // if last name was a failure AND it was not the Last name, then do nothing
    }
    else {

      // get the name in Cell G2
      var g2=sh.getRange("G2").getValue();
      // Logger.log("DEBUG:1 currently selected name = "+g2);

      // get the position of G2 in the list of names
      var Pos = users.indexOf(g2);
      // Logger.log("DEBUG:1 position = "+Pos);

      if (modifiedTLN == 99){ 
        // if last name was a failure AND it was The Last Name
        i=0;
        Logger.log("DEBUG:1: modifiedTLN is "+modifiedTLN+", and i = "+i);
      }
      else{
        // test if this is the last name in the list
        if (Pos+1 == names.length){
          // this is the last name
          i=0;
          failedNTLN = 0;
          modifiedTLN = 0;
          Logger.log("DEBUG:1: Pos is last position. i = "+i)
        }
        else
        {
          // this is not the last name
          i = Pos+1;
          // Logger.log("DEBUG:1: Pos is NOT the last position. set i = "+i)
        }
      }
    }

    // Logger.log("DEBUG:1: settings are: i="+i+", next status = "+vA[i][1].toString().toLowerCase()+", next name = "+vA[i][0]+", g2 = "+g2);

    // logic statement if status = yes, and name isn't = G2
    if(vA[i][1].toString().toLowerCase()=='yes' && vA[i][0]!=g2) {

      // Logger.log("DEBUG:2: IF - outcome success: i="+i+", status = "+vA[i][1].toString().toLowerCase()+", name = "+vA[i][0]+" does not equal "+g2);
      sh.getRange("G2").setValue(vA[i][0]);
      // Logger.log("DEBUG:2: setting G2 to "+vA[i][0]+", i = "+i+" and break") 
      failedNTLN=0;modifiedTLN=0;
      break;
    }
    else if (users.indexOf(vA[i][0]) == (names.length-1)){
      // the next name is the last name
      // Logger.log("DEBUG:3: the next name is the last name");
      modifiedTLN = 99;
      failedNTLN=0;
      i=0;
      // Logger.log("DEBUG:3: position = "+users.indexOf(vA[i][0]))
      // Logger.log("DEBUG:3: the next name: "+users.indexOf(vA[i][0])+" is the last name;  i = "+i+"; set modifiedTLN to "+modifiedTLN+"; set failedNTLN to "+failedNTLN);
    }
    else{
      // the next name is NOT the last name
       failedNTLN = 99;
       modifiedTLN = 0;
       //Logger.log("DEBUG:4: the next name: "+users.indexOf(vA[i][0])+" is NOT the last name;  i = "+i+"; set failedNTLN to "+failedNTLN+"; set modifiedTLN to "+modifiedTLN);
    }
  }
}