Row Removal if value on B Colum is "XYZ" or Empty on this function

user_19826276 0
Viewed 22
function ABCDEF() {
  var ss=SpreadsheetApp.getActive();
  var s=ss.getSheetByName('Sheet1');
  var ds=ss.getSheetByName("Sheet2");
  var simDataList=['A4:AL428'];
  var oA=[];
  for(var i=0;i<simDataList.length;i++) {
    var vA=s.getRange(simDataList[i]).getValues();//get data from each range  
    for(var j=0;j<vA.length;j++) {
      oA.push(vA[j]);//append each row to output array
    }
  }
  ds.getRange(ds.getLastRow()+1,1,oA.length,oA[0].length).setValues(oA);
}

Function works but I need to trim the data, Sheet 1 should not change.

Is there a way to remove ALL rows if column B has string value of "XYZ" or an EMPTY value, WHEN the function triggers/paste it on Sheet2. Again, Sheet 1 should not be updated.

google-apps-script google-sheets
1 Answers

I believe your goal is as follows.

  • By modifying your showing script, you want to remove the rows from the array oA, when column "B" includes the values of "XYZ" or the column "B" is empty.

In this case, how about the following modification?

From:

oA.push(vA[j]);//append each row to output array

To:

if (vA[j][1].includes("XYZ") || vA[j][1].toString() == "") continue;
oA.push(vA[j]);//append each row to output array

  • By this, when column "B" includes the values of "XYZ" or the column "B" is empty, the rows are not pushed to the array oA.

  • If you want to skip when the column "B" is "XYZ", please modify vA[j][1].includes("XYZ") to vA[j][1] == "XYZ".

Reference:

user_71086530
Related