With two sets of numeric elements stored as 2D arrays, one representing a full list and the other a partial list, I am seeking a function that can return the full list minus the partial list.
- The
partialListArrmay contain duplicates, while thefullListArrdoes not. - The output also needs to be a 2D list as it will be utilized in
.setValues(). - All values are numerical.
Here are the attempted solutions:
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)
}
At this point, the function accurately obtains the full list.
Another approach was tried with the following code:
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)
}
However, this method falls short by returning only a portion of the expected results. Considering a scenario where fullListArr contains 943 values and partialListArr has 288 unique values, the anticipated result should be 655 values in arr, yet the actual output is 895, failing to return it as a 2D array.
For further clarification and to view code snippets, refer to the associated spreadsheet.
Efforts: The initial step involved flattening the lists when 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)
This modification resulted in the proper count for arr. The subsequent step involved transforming the output back into a 2D array for compatibility with .setValues. Below is the comprehensive function with the complete 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;
}
Many thanks, Akrion!