Use `setValue` to update a column based on values of two others that match their respective values in reference cells

Question

I am new to Google AppScript and would really appreciate some help here.

I want to use the setValue along with the for loop and if statement in Google App Script to scan multiple columns in Google Sheets and set the value of data in column F based on the results of other columns using reference cells B2, B3.

So, if values in column D are equal to cell B2 and values in column E are equal to cell B3, change the value of each row in column F matching that criteria from Pending to Ordered

I have attached a picture showing the desired result.

Answer 1

I see at least two alternatives to update the Order Status.

1- Function
=if(and(D3=$B$2,E3=$B$3),"Ordered","Pending")
Insert this in Cell F3, and copy down the required number of rows. This will update the Order Status dynamically.

The function is based on the conventional if function but the logical expression is described by an and with two arms.

• The first arm is whether the value of cell D3 equals the value in cell $B$2. Note that cell B2 is expressed as an absolute; no matter how many rows there may be in column D, the row value will always be compared to the value in cell B2.
• The second arm is whether the value of cell E3 equals the value in cell $B$3. Again, cell B3 is expressed as an absolute; the row value in Column E will always be compared to the value in cell B3.

2 - Script
The following script so13343001 is executed by a custom menu opened by onOpen.

As suggested by the OP, the script uses a for loop and an if statement.

The if statement has two arms (just like the function) - it tests whether both the "requests" and "Supplier" values are equal to the Reference values.

The update of the order status avoids creating a setValue method for every row; this would be acceptable, but if there are a lot of rows, it could slow down the script. Instead, the specific cell value in the "data" array is updated, and the last statement is to `setValue(data)' to the datarange.

---

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Update-Order-Status')
      .addItem('Update Status', 'so13343001')
      .addToUi();
}

function so13343001() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetname = "Sheet1_script";
  var sheet = ss.getSheetByName(sheetname);

  var LR = sheet.getLastRow();
  var Columns = 4;
  var range = sheet.getDataRange();
  //Logger.log(range.getA1Notation());
  var data = range.getValues();
  //Logger.log(data);

  var refReqStatus = data[1][1];
  var refSupplier = data[2][1];
  //Logger.log("DEBUG: Reference Data: Request Status:"+refReqStatus+", Supplier: "+refSupplier)

  for (var i=0;i<LR-1;i++){
    var requests = data[i+1][3];
    var supplier = data[i+1][4];
    var orderstatus = data[i+1][5];
    var item = data[i+1][6];
    //Logger.log("DEBUG: i="+i+", Requests: "+requests+", Supplier: "+supplier+", Order Status: "+orderstatus+", Item: "+item);

    // update the status to Ordered
    if (requests == refReqStatus && supplier == refSupplier){
      // requests and supplier match the reference data
      data[i+1][5] = "Ordered";
      //Logger.log("DEBUG: Updated status for row#"+(+i+1))
    }  
  }
  range.setValues(data);
}