Filter a 2D Array From Elements of Another

Question

I have two sets of numeric elements stored as 2D array. The values were grabbed from a column using .getValues(). One is a full list the other is a partial list. I want a function that returns full list minus partial list.

• The partialListArr may contain duplicates. The fullListArr does not.
• I need the output to also be a 2D list as they will be used in .setValues().
• Values are all numbers.

Here is what I've tried.

function myFunction() {
  var ss = SpreadsheetApp.getActive();
  var partialListArr = ss.getSheetByName('sheet 2').getRange(1,1,357,1).getValues();
  var fullListArr = ss.getSheetByName('sheet 1').getRange(1,1,942,1).getValues();

  var arr = fullListArr.filter(function(item){
    return partialListArr.indexOf(item.id) === -1;
  }); 

  Logger.log(arr.length)
  Logger.log(arr)
}

This returns the full list.

I also tried this:

function myFunction2(){
  var ss = SpreadsheetApp.getActive();
  var partialListArr = ss.getSheetByName('sheet 2').getRange(1,1,357,1).getValues();
  var fullListArr = ss.getSheetByName('sheet 1').getRange(1,1,942,1).getValues();

  var arr = fullListArr.map(function(e){return e[0];})
    .filter(function(e,i,a){return (a.indexOf(e)==i && partialListArr.indexOf(e) ==-1); })  

  Logger.log(arr.length)
  Logger.log(arr)
} 

It will return only part of the results. If fullListArr has 943 and partialListArr has 288 unique values, I should have 655 values in arr but I'm getting 895 and it is not returning it as a 2D array.

Here is a sheet with a data set and both of these included.

Attempts: First I need to flatten if using the first function.

  var ss = SpreadsheetApp.getActive();
  var partialListArr = ss.getSheetByName('sheet 2').getRange(1,1,357,1).getValues();
  var fullListArr = ss.getSheetByName('sheet 1').getRange(1,1,942,1).getValues();

  var flatPartialListArr = [].concat.apply([], partialListArr);
  var flatFullListArr = [].concat.apply([], fullListArr);

  var arr = flatFullListArr.filter(function(item){
    return flatPartialListArr.indexOf(item) === -1;
  }); 

  Logger.log(arr.length)
  Logger.log(arr)

That gave me the correct number for arr. Next step is to make it a 2d array again for me to plug it in .setValues. Here is the function with the full solution.

function myFunction() {
  var ss = SpreadsheetApp.getActive();
  var partialListArr = ss.getSheetByName('sheet 2').getRange(1,1,357,1).getValues();
  var fullListArr = ss.getSheetByName('sheet 1').getRange(1,1,942,1).getValues();

  var flatPartialListArr = [].concat.apply([], partialListArr);
  var flatFullListArr = [].concat.apply([], fullListArr);

  var flatArr = flatFullListArr.filter(function(item){
    return flatPartialListArr.indexOf(item) === -1;
  }); 

  //Convert to 2D again for input into .setValues
  var newArr = [];
  while(flatArr.length) newArr.push(flatArr.splice(0,1));

  Logger.log(newArr.length)
  Logger.log(newArr)

  return newArr;
}

Thank you Akrion!

Answer 1

It seems you have something like this:

var full = [[1],[2],[3],[4],[5]]
var partial = [[3],[4]]

var result = full.filter(x => !partial.find(p => p[0] === x[0]))
console.log(result)

This will filter the way you want. Simply put your filter does not work since you did not take into account the result returned, which is [[], [], []] or array of arrays.

So in your context try this:

var arr = fullListArr.filter(item => !partialListArr.find(p ==> p[0] === item[0]);