CODE EXAMPLE FOR JAVASCRIPT

javascript loop array

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