Compare two, 2D Array paired string elements regardless of order for differences within Google Apps-CodePudding

Problem

I would like to compare every paired elements (if that is a thing) such as [[Store 1, A, 1]] from sheet 1 to sheet 2, and have an array return that only has which pairs did not match. I'm not attempting to see if any element from one array is one the other array, I want to see if the paired elements are within the other array regardless of the order, just the contents being the same. For example on sheet 1 I have [Store 2, 2, B] and Sheet 2 I have [Store 2, B, 2] and this would be a match.
I will then ideally access this new array to pull out each item for further use in further coding such as the Store or the Location from this new array. I'm assuming a 2-day array would be ideal for this.
The arrays will not be the same length, unlike my example. Sheet 2 would have more data. I simplified it for this example. I mention this as I've read some solutions comparing arrays but the lengths have to be the same.

Is it possible to do #1, if yes any help/advice would be appreciated!

Data Explanation

I have 2 sheets, the columns are the same but in different locations. Sheet 1 will remain constant, while sheet 2 is obtaining new data. In this example, sheet 1 is let's say our master/refer to data source, so sheet 2 should match the row/elements on it. See tables below for an example of a row/elements that match such as row 3 from sheet 1 (store 2, 2, b).

Store names will repeat as will locations, however store # will be unique and not repeat for each array/sheet. An example would be, we could have 5 Store 1's, with 5 different store numbers. There would not be 5 stores 1's, with the same store # and location.

Current Output

sheet 1 array:

[["Store 1",2,"A"],["Store 2",2,"B"],["Store 1",1,"D"],["Store 1",3,"E"],["Store 3",4,"C"],["Store 4",1,"C"],["","",""]]

sheet 2 array:

[["Store 1","A",1],["Store 2","B",2],["Store 1","D",1],["Store 1","E",3],["Store 3","C",4],["Store 4","C",2],["","",""]]

result of filter function:

[]

Desired Output

[["Store 1", 2, "A"] , ["Store 4", 1, "C"]]

Solutions tried

Attempted to find a similar problem on stackoverflow, was unable to find my exact issue
Reviewed similar questions to mine to see if somewhat similar solutions would work (see references below). This question seemed promising, but the filter function seems to only work with number elements and not strings like I have. So if my arrays were [[0,1,2]] and [[0,],[1]] the filter method I have would work.
Attempted to use a for loop similar to the questions I've linked, but this is resulting in null answers
attempted to use the filter and find methods from similar answers but this also seemed unable to help me reach my desired outcome
reviewed on https://developer.mozilla.org/ if perhaps Object.keys() or Object.entries() would suffice, but that will not work since ideally we're using the elements in a pair as opposed to individual entries
Tried to use JSON.stringify() to turn the elements in the arrays into strings...however after further research it seems the items in the array are already strings? Also using JSON.stringify() only allows for certain methods (search and match) to be utilized so this did not seem feasible
Unable I think to utilize flat() to deconstruct the 2d into a 1d since I want the elements to stay grouped instead of being isolated.

Current Code

function test() {
  const ss1 = SpreadsheetApp.getActive().getSheetByName('Sheet1');
  const ss2 = SpreadsheetApp.getActive().getSheetByName('Sheet2');

  var1 = ss1.getRange(2,1,ss1.getLastRow(),3).getValues();
  var2 =  ss2.getRange(2,1,ss1.getLastRow(),3).getValues();

  var test1= var1.filter(a => !var2.find(b => b[0] === a[0]))//returns [] at the moment

  var testR = new Array();

    for (var i=0;i<var1.length;i  ){
      for (var j=0;j<var1.legnth;j  ){
        if(var1[i][j]=!var2[i][j]){
         testR.push(var1[i]);
        }
        }
        }
        //for loop results in null at the moment


}

Raw Data Example

Sheet 1:

Store	Store #	Location
Store 1	2	A
Store 2	2	B
Store 1	1	D
Store 1	3	E
Store 3	4	C
Store 4	1	C

Sheet 2:

Store	Location	store #
Store 1	A	1
Store 2	B	2
Store 1	D	1
Store 1	E	3
Store 3	C	4
Store 4	C	2

Similar Questions

CodePudding user response：

Can't speak for the elegance or speed of this, but taking some inspiration from this answer, one way to do it would be to first swap the B and C columns from Sheet 2 to match the ones in Sheet 1, then use the stringify way to compare both arrays.

function myFunction() {
  const ss1 = SpreadsheetApp.getActive().getSheetByName('Sheet1');
  const ss2 = SpreadsheetApp.getActive().getSheetByName('Sheet2');

  var var1 = ss1.getRange(2,1,ss1.getLastRow()-1,3).getValues();
  var var2 = ss2.getRange(2,1,ss2.getLastRow()-1,3).getValues();

  //swaps positions and returns a stringified array
  var2 = var2.map(x=>JSON.stringify([x[0], x[1], x[2]] = [x[0], x[2], x[1]]))
  
  //stringifies each element in var1 before matching to var2
  var output = var1.filter(x => !var2.includes(JSON.stringify(x)));

  console.log(output)
}

Output: [ [ 'Store 1', 2, 'A' ], [ 'Store 4', 1, 'C' ] ]

CodePudding user response：

Create a array of Sets from the second array and,
Filter in arrays of first array, if it doesn't contain every element in some of the second array Sets

/*<ignore>*/console.config({maximize:true,timeStamps:false,autoScroll:false});/*</ignore>*/ 
const arr1 = [
    ['Store 1', 2, 'A'],
    ['Store 4', 1, 'C'],
    ['Store 2', 'A', 2],
    ['C', 'Store 3', 1],
  ],
  arr2 = [
    ['Store 2', 2, 'A', 3],
    ['Store 3', 1, 'C', 9],
  ],
  arr2Set = arr2.map((row) => new Set(row)),
  out = arr1.filter(
    (row) => !arr2Set.some((set) => row.every((el) => set.has(el)))
  );
console.log(out);

<!-- https://meta.stackoverflow.com/a/375985/ -->    <script src="https://gh-canon.github.io/stack-snippet-console/console.min.js"></script>