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