Update order status if all items in order are complete (i.e. Set first cell value of group of rows h-CodePudding

I populate a google sheet with customer orders. Each row is an item in an order, so there could be multiple rows for the same order.

I would like to use a script that updates an order status (column A) if each product in that order (column C) has the product status "Completed" (column B).

For example my sheet looks like this:

In this case I would need "Manufacturing" to be replaced with "Ready To Ship" in rows 5 & 6 (0rders 0002 and 0003), as those meet the above requirements of all items in a particular order being completed.

So far I have the following:

  var active = SpreadsheetApp.getActive();
   var sheet = active.getSheetByName("Incoming Orders");
   var lastRow = sheet.getLastRow();
   var myRange = sheet.getRange("A2:D"   lastRow); //First row is header
   var data = myRange.getValues();
  for (var i = 0; i < data.length; i  ){
    if (data[i][1] === "Completed") {
     var seenType = {};
        if(seenType[data[i][2]]){
            continue;
        }
        seenType[data[i][2]] = true;
                sheet.getRange(i 2,1).setValue("Ready To Ship"); //First row is header
            }
  }
}

When I remove the if (data[i][1] === "Completed") it will write "Ready to Ship" for each order number, but it is not conditional on to whether or not each item in the order is completed:

function batchStatus() {
  var active = SpreadsheetApp.getActive();
   var sheet = active.getSheetByName("Incoming Orders");
   var lastRow = sheet.getLastRow();
   var myRange = sheet.getRange("A2:D"   lastRow); //First row is header
   var data = myRange.getValues();
   var seenType = {};
  for (var i = 0; i < data.length; i  ){
        if(seenType[data[i][2]]){
            continue;
        }
        seenType[data[i][2]] = true;
                sheet.getRange(i 2,1).setValue("Ready To Ship"); //First row is header
  }
}

I am very new to scripting, so hopefully this is not too far off base.

CodePudding user response：

I believe your goal is as follows.

When your sample image is used, you want to replace "A5:A6" with the value of "Ready To Ship".
From your following reply,

I am providing a very simplified example of my sheet. On my original I used the first 3 rows as headers. I can see how that is confusing so I have updated my question so that just the first row is the header to match the example.
- It seems that your showing script is different from your actual script. So, I'm worried that even when your showing script is modified, your goal might not be achieved.

If your showing script is modified using your sample image, how about the following modification?

Modified script:

function batchStatus() {
  var active = SpreadsheetApp.getActive();
  var sheet = active.getSheetByName("Incoming Orders");
  var lastRow = sheet.getLastRow();
  var myRange = sheet.getRange("A2:D"   lastRow);
  var data = myRange.getValues();

  // I modified below script.
  var obj = data.reduce((o, r, i) => {
    if (o[r[2]]) {
      o[r[2]].values.push(r[1])
    } else {
      o[r[2]] = { values: [r[1]], cell: `A${i   2}` };
    }
    return o;
  }, {});
  var rangeList = Object.values(obj).filter(o => o.values.every(e => e == "Completed")).map(({ cell }) => cell);
  sheet.getRangeList(rangeList).setValue("Ready To Ship");
}

In this modification, at first, an object is created as the key of "Order Nubmer", and a range list is created using the object. And then, the value of Ready To Ship is put on the range list.
When the sheet of your sample image is used with this modified script, the value of "Order Nubmer" is put to the cells "A5:A6".

Modified script:

References: