Search code examples
javascriptarraysgoogle-apps-scriptconcatenation

Google Apps Script: Combine Arrays When there is A Match Between Both Arrays


Background

I have two arrays (Array 1 and Array 2). Both of them share an ID type (Column 2 of Array 1 and Column 0 of Array 2). I want to combine the two arrays vertically only when there is a match in that ID.

Starting Arrays:

array1 = [[AB2C, Red, 113]       <--Match
          [BE4F, Green,164]
          [AE3G, Blue, 143]];    <--Match

array2 = [[143, FabricB2, W5]    <--Match
          [189, FabricC9, W4]
          [113, FabricA3, W5]];  <--Match

Desired Ending Array

array3 = [[AB2C, Red, 113, FabricA3, W5]
          [AE3G, Blue, 143, FabricB2, W5]];

Methodology

It seems to me like the most efficient way to do this would be to:

  1. Create an empty array.
  2. Push Array 1 data to Array 3 if the ID column makes a match with one in Array 2.
  3. Push Array 2 data to Array 3 where the ID column matches with Array 3. We should also only add Columns 1 & 2 of Array 2 so we aren't duplicating the ID Column.

What I've Tried

For Step 2 I've been trying to push Array 1 to Array 3 with a push and map combo like the below but it's not working properly. My intention was to take each row of Array 1, run it through every row of Array 2 to check for a match; if there's a match then push to Array 3.

For Step 3 my thought was to take each row of Column 2 of Array 3, run it through each row of Column 0 of Array 2, and when finding a match, concat those to Array 3. I never got past the equation for Step 2 in order to get to this.

I would REALLY appreciate your help!

array1 = [[AB2C, Red, 113]
          [BE4F, Green,164]
          [AE3G, Blue, 143]];

array2 = [[143, FabricB2, W5]
          [189, FabricC9, W4]
          [113, FabricA3, W5]];

array3 = [];

array3.push(array1.map( function(e) { return e[2] == array2.map ( function (f) { return f[0] } ) }));

Solution

  • array1 = [
      ['AB2C', 'Red', 113],
      ['BE4F', 'Green', 164],
      ['AE3G', 'Blue', 143],
    ];
    
    array2 = [
      [143, 'FabricB2', 'W5'],
      [189, 'FabricC9', 'W4'],
      [113, 'FabricA3', ' W5'],
    ];
    
    
    const res = array1
      .map(x => array2
        .filter(y => y[0] === x[2])
        .map(y => [x[0], x[1], ...y]))
      .flatMap(x => x)
    
    console.log(res)