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

Posted on

Use `setValue` to update a column based on values of two others that match their respective values in reference cells – Whether you’re just starting out with Google Sheets or are a seasoned pro, sooner or later one of your formulas will give you a formula or script parse error message rather than the result you want. However, every Formula user, irrespective of whether it’s a beginner or expert, has invariably come across a formula parse error in Google Sheets at least once in their life (and if you haven’t. you soon will). It can be frustrating, especially if it’s a longer formula where the formula parse error may not be obvious. In this article, we will talk about some common script or formula parse errors in Google Sheets, like Use `setValue` to update a column based on values of two others that match their respective values in reference cells, and how to fix it.

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.

enter image description here

Answer :

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

Leave a Reply

Your email address will not be published.