Cross reference names on spreadsheets to get specific data points (looping through an array)

I have two sheets. Test Data has 3-4k entries of many columns of data and Order Changes has no data at all. I would like to search two specific columns on Test Data, a column of names and a column of yes or no. If column two of Test Data contains a ‘yes’ in the cell then the name of that person would be placed into a cell on order changes.

This is what I have so far:

    function isThreshold(){
  
  var data = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Test Data");
  var cdata = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Order Changes");
  var lc = data.getLastColumn();
  var lookUp = data.getRange(1,6,3,2).getValues();
  
  lookUp.forEach(var info in lookUp){
  
  }
  Logger.log(lookUp);
}

I probably shouldn’t loop through that many entries but I don’t know of any other way. Should I combine the forEach loop with an if loop to get the desired result or use some other method?

Answer

I believe your goal as follows.

• You want to retrieve the values from the cells “F1:G” of sheet “Test Data”.
• You want to search yes from the column “G” and when the column “G” is yes, you want to put the value of the column “F” to the sheet “Order Changes”.

Modification points:

• SpreadsheetApp.getActiveSpreadsheet() can be declared one time.
• In this case, you can retrieve the values from the range of "F1:G" + data.getLastRow() of “Test Data”, and create the array for putting to the sheet “Order Changes”, and put it.

When above points are reflected to your script, it becomes as follows.

Modified script:

function isThreshold(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var data = ss.getSheetByName("Test Data");
  var cdata = ss.getSheetByName("Order Changes");
  var valuesOfTestData = data.getRange("F1:G" + data.getLastRow()).getValues();
  var valuesForOrderChanges = valuesOfTestData.reduce((ar, [f, g]) => {
    if (g.toLowerCase() == "yes") ar.push([f]);
    return ar;
  }, []);
  if (valuesForOrderChanges.length > 0) {
    cdata.getRange(1, 1, valuesForOrderChanges.length, valuesForOrderChanges[0].length).setValues(valuesForOrderChanges);
    // or cdata.getRange(cdata.getLastRow() + 1, 1, valuesForOrderChanges.length, valuesForOrderChanges[0].length).setValues(valuesForOrderChanges);
  }
}

• In this modified script, from your question, it supposes that the columns “F” and “G” are the value of name and yes or no.

References:

• getRange(a1Notation) of Class Sheet
• reduce()