Apps Script filter function not reproducing expected result

0
votes

I have this Google sheet that contains 3 columns, data with three columns Here is the data: Data Source I wrote a script to filter for when column A and column C meets certain criteria The script then produces the result in sheet 'PharmacyV1'. The result was generated as expected, as there are 14 rows in this sheet.

The next part of the script, I basically filtered for the exact opposite of the first criteria, essentially grabbing data that wasn't filtered to sheet 'Pharmacy V1' and rewrite it to the original sheet 'V1'.

My Original V1 sheet contains 1,631 rows of data (headers excluded)

My PharmacyV1 sheet filtered out 14 rows of data

But the V1 result ended up with 1,575 rows of data, but I should expect 1631-14 = 1617 rows.

Can someone please point out how or why there is such a discrepancy?

function S02_FilterPharmacy_Copy2NewSheet() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('V1');
  var firstRow = sheet.getRange(1,1,1,sheet.getLastColumn()).getValues();
  var C1 = firstRow[0].indexOf('TxnUser');
  var C2 = firstRow[0].indexOf('Location');

  // Filter for IV therapy and pharmacy users using filtered Range:
  var rangeVals = sheet.getRange(1,1,sheet.getLastRow()-1,sheet.getLastColumn()).getValues();
  var filteredRange = rangeVals.filter(function(val){
    return val[C2] === "IV THERAPY" && (val[C1] === "DEOK645"|| val[C1] === "JETH850" || 
      val[C1] === "JOON409"|| val[C1] === "THAM1452");
    });
  var targetSheet = ss.insertSheet('PharmacyV1');
  targetSheet.getRange(2,1,filteredRange.length,filteredRange[0].length).setValues(filteredRange);
  var sheet = ss.getSheetByName('V1');
  targetSheet.getRange(1,1,1,sheet.getLastColumn()).setValues(firstRow);
  
  
  //Filter and replace original V1
  var rangeVals = sheet.getRange(1,1,sheet.getLastRow()-1,sheet.getLastColumn()).getValues();
  var filteredRange = rangeVals.filter(function(val){
   return val[C2] != "IV THERAPY" && (val[C1] != "JOON409"|| val[C1] != "JETH850" || 
     val[C1] != "JOON409"|| val[C1] != "THAM1452");
     });
  
  var RANGE = sheet.getDataRange(); RANGE.clearContent();
  var newRange = sheet.getRange(1,1,filteredRange.length, filteredRange[0].length);
  newRange.setValues(filteredRange);
 
}
1
google-apps-scriptgoogle-sheetsfilter

1 Answers

2
votes
  • You want to filter the values of var rangeVals = sheet.getRange(1,1,sheet.getLastRow()-1,sheet.getLastColumn()).getValues(); with val[C2] === "IV THERAPY" && (val[C1] === "DEOK645"|| val[C1] === "JETH850" || val[C1] === "JOON409"|| val[C1] === "THAM1452");.
  • You want to retrieve the filtered values. (In your shared Spreadsheet, it's 14 rows)
  • You want to retrieve the not filtered values. (In your shared Spreadsheet, it's 1617 rows)
  • You want to know the reason that when the not filtered values are retrieved from the total rows of 1631, your script doesn't return 1631 - 14 = 1617 rows.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Issue:

At first, I would like to compare the both filters for retrieving filteredRange.

  1. val[C2] === "IV THERAPY" && (val[C1] === "DEOK645"|| val[C1] === "JETH850" || val[C1] === "JOON409"|| val[C1] === "THAM1452")

    • This returns 14 rows.

  2. val[C2] != "IV THERAPY" && (val[C1] != "JOON409"|| val[C1] != "JETH850" || val[C1] != "JOON409" || val[C1] != "THAM1452")

    • THis returns 1576 rows. This is not 1617 which is 1631 - 14 = 1617.

Here, I think that for 2nd filter, DEOK645 should be JOON409. And when you want to create the opposite filter with the 1st filter, == and != are modified to != and ==, respectively. And also, && and || are modified to || and &&, respectively. In your script, I think that the latter is the reason of your issue.

Solution and workaround:

When your shared Spreadsheet is used, the following 3 patterns returns the values of 1617 rows.

Pattern 1:

In this pattern, above modification is used. Please modify as follows.

//Filter and replace original V1
var rangeVals = sheet.getRange(1,1,sheet.getLastRow()-1,sheet.getLastColumn()).getValues();
var filteredRange = rangeVals.filter(function(val){
 return val[C2] != "IV THERAPY" && (val[C1] != "JOON409"|| val[C1] != "JETH850" || 
   val[C1] != "JOON409"|| val[C1] != "THAM1452");
   });

//Filter and replace original V1
var rangeVals = sheet.getRange(1,1,sheet.getLastRow()-1,sheet.getLastColumn()).getValues();
var filteredRange = rangeVals.filter(function(val){
  return return val[C2] != "IV THERAPY" || (val[C1] != "DEOK645" && val[C1] != "JETH850" && val[C1] != "JOON409" && val[C1] != "THAM1452"); // Modified
});

Pattern 2:

In this pattern, 1st filter is used. Please modify as follows.

//Filter and replace original V1
var rangeVals = sheet.getRange(1,1,sheet.getLastRow()-1,sheet.getLastColumn()).getValues();
var filteredRange = rangeVals.filter(function(val){
 return val[C2] != "IV THERAPY" && (val[C1] != "JOON409"|| val[C1] != "JETH850" || 
   val[C1] != "JOON409"|| val[C1] != "THAM1452");
   });

//Filter and replace original V1
var rangeVals = sheet.getRange(1,1,sheet.getLastRow()-1,sheet.getLastColumn()).getValues();
var filteredRange = rangeVals.filter(function(val){
  return !(val[C2] === "IV THERAPY" && (val[C1] === "DEOK645"|| val[C1] === "JETH850" || val[C1] === "JOON409"|| val[C1] === "THAM1452")); // Modified
});

Pattern 3:

In this pattern, both the filtered values and not filtered values are retrieved in one loop.

var rangeVals = sheet.getRange(1,1,sheet.getLastRow()-1,sheet.getLastColumn()).getValues();
var filteredRange = rangeVals.reduce(function(obj, val){
  if (val[C2] === "IV THERAPY" && (val[C1] === "DEOK645"|| val[C1] === "JETH850" || val[C1] === "JOON409"|| val[C1] === "THAM1452")) {
    obj.filteredValue.push(val);
  } else {
    obj.noFilteredValue.push(val);
  }
  return obj;
}, {filteredValue: [], noFilteredValue: []});
  • In this case, filteredRange.filteredValue and filteredRange.noFilteredValue are the filtered values and the not filtered values, respectively.

References:

If I misunderstood your question and this was not the result you want, I apologize.