1

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

4
  • Welcome. A few things: 1) Sounds like you have a clear understanding of the process. Have you had a go at writing any code (anything at all) to do this. 2) How often/when/what trigger will drive this script? 3) You've specifically asked for a script; what if the same thing can be done by a function? have you considered that alternative? 4) Your demo data is only 5 rows, how many rows of data in your live data?
    – Tedinoz
    Sep 7, 2019 at 3:30
  • 1
  • Thanks. 1) I have written some code, so far i'm able to change the values of a column to x using setValue but obviously it needs to meet the criteria of the reference cells first. 2) I was thinking of having it run by adding it to a custom menu. 3) Sorry I don't mean script I mean function. 4) The data will be growing so the amount of rows will change as it grows. I believe the link that you provided changes a value in a column based on a value in another column not based on a value in B2. Also another difference is there are two conditions to meet not one.
    – Sam Davies
    Sep 9, 2019 at 2:32
  • FYI - refer Adding clause of and/or to if statement in google docs script. A good, short summary from @Sergeinsas and 'user34612' for the codes for && (and ) and || (or), as well as the conditional test of 'equal to' and 'not equal to'.
    – Tedinoz
    Sep 9, 2019 at 5:16

1 Answer 1

2

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);
}
2
  • Worked Perfectly! Thank you for taking time to provide these two answers. Now that I see the code and along with the logs it makes a lot more sense. Once I get more reputation I will give you an up vote. Thanks again!
    – Sam Davies
    Sep 9, 2019 at 21:07
  • If the answer was useful to you, you can accept it (stackoverflow.com/help/someone-answers)
    – Tedinoz
    Sep 10, 2019 at 10:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.