Join two multidimensional arrays by matching elements on similar columns javascript es6

Question

I am working in Google sheets running chrome V8

I have two arrays made from range data on two different sheets

in a real-life situation, I do know in advance the width or length of arr1 or arr2. All I know is they are not the same dimensions

Both arrays have an Id column. arr1 will always have the Id column as the first column. I do not know in advance the column position of the Id column in arr2

arr1 if printed to a sheet would look like

Id  |Label  |SAH1   |SAH2    |SAH3
---------------------------------------
aa1 |Bob    |stuff1 |stuff10 |stuff19
aa2 |Eggbert|stuff2 |stuff11 |stuff20
aa3 |Juan   |stuff3 |stuff12 |stuff21
aa4 |Alice  |stuff4 |stuff13 |stuff22
aa5 |Fraya  |stuff5 |stuff14 |stuff23
aa6 |Ted    |stuff6 |stuff15 |stuff24
aa7 |Velma  |stuff7 |stuff16 |stuff25
aa8 |Carol  |stuff8 |stuff17 |stuff26
aa9 |LeRoy  |stuff9 |stuff18 |stuff27

arr2 if printed to a sheet would look like

Name             |SXH1      |Id     |SXH2
--------------------------------------------
Maya Townsend    |thing1    |mm1    |thing20
David Collste    |thing2    |mm2    |thing21
Bob              |thing3    |aa1    |thing22
Jennifer Menke   |thing4    |mm4    |thing23
Aissata Camara   |thing5    |mm5    |thing24
Marta Arranz     |thing6    |mm6    |thing25
Michael  Lennon  |thing7    |mm7    |thing26
Jonny Norton     |thing8    |mm8    |thing27
Carol            |thing9    |aa8    |thing28
Ted              |thing10   |aa6    |thing29
Katie Boone      |thing11   |mm11   |thing30
Adeline Sibanda  |thing12   |mm12   |thing31
Brian Blankinship|thing13   |mm13   |thing32
Christine Lai    |thing14   |mm14   |thing33
Jennifer Pierce  |thing15   |mm15   |thing34
Federico Bellone |thing16   |mm16   |thing35
Jeffrey Su       |thing17   |mm17   |thing36
Alice            |thing18   |aa4    |thing37
Sarah Ditton     |thing19   |mm19   |thing38

result arr if printed to a sheet needs to look like

Id  |Label  |SAH1   |SAH2    |SAH3    |Name |SXH1   |SXH2  
------------------------------------------------------------
aa1 |Bob    |stuff1 |stuff10 |stuff19 |Bob  |thing3 |thing22 
aa2 |Eggbert|stuff2 |stuff11 |stuff20 |""   |""     |""
aa3 |Juan   |stuff3 |stuff12 |stuff21 |""   |""     |""
aa4 |Alice  |stuff4 |stuff13 |stuff22 |Alice|thing18|thing37
aa5 |Fraya  |stuff5 |stuff14 |stuff23 |""   |""     |""
aa6 |Ted    |stuff6 |stuff15 |stuff24 |Ted  |thing10|thing29
aa7 |Velma  |stuff7 |stuff16 |stuff25 |""   |""     |""
aa8 |Carol  |stuff8 |stuff17 |stuff26 |Carol|thing9 |thing28
aa9 |LeRoy  |stuff9 |stuff18 |stuff27 |""   |""     |""

I have searched for a method to do this and could find nothing that I had the skill to alter

Here is a Google sheet with test data https://docs.google.com/spreadsheets/d/1y0CFUEdpZ77IgKGVYXUinepg7we2F0311U4D5fAP17Y/edit?usp=sharing

Thank you

Answer 1

I believe your goal and situation as follows.

• You want to achieve the conversion result arr from arr1 and arr2 in your question using Google Apps Script.
• You don't know the column of Id in arr2 sheet.

In order to achieve your goal, I would like to propose the following flow.

1. Retrieve values from 2 source sheets.
2. Create an object from the values of "arr2" sheet using the values of "Id".
3. Create an array for putting to the result sheet using the values of "arr1" and "arr2" sheets.
4. Put values to the result sheet to "Result" sheet.

When above flow is reflected to the script, it becomes as follows.

Sample script:

Please set each sheet name.

function myFunction() {
  const arr1SheetName = "arr1";
  const arr2SheetName = "arr2";
  const resultSheetName = "Result";

  const ss = SpreadsheetApp.getActiveSpreadsheet();

  // 1. Retrieve values from 2 source sheets.
  const [[header1, ...values1], [header2, ...values2]] = [arr1SheetName, arr2SheetName].map(s => ss.getSheetByName(s).getDataRange().getValues());

  // 2. Create an object from the values of "arr2" sheet using the values of "Id".
  const indexId = header2.indexOf("Id");
  const obj = values2.reduce((o, r) => {
    const id = r[indexId];
    r.splice(indexId, 1);
    return Object.assign(o, {[id]: r});
  }, {});

  // 3. Create an array for putting to the result sheet using the values of "arr1" and "arr2" sheets.
  const array = values1.map(r => obj[r[0]] ? r.concat(obj[r[0]]) : r.concat(Array(3).fill("")));
  header2.splice(indexId, 1);
  array.unshift([...header1, ...header2]);
  
  // 4. Put values to the result sheet to "Result" sheet.
  const sheet = ss.getSheetByName(resultSheetName);
  sheet.clearContents();
  sheet.getRange(1, 1, array.length, array[0].length).setValues(array);
}

• In this case, from your shared Spreadsheet, the sheet names are arr1, arr2 and Result. Please be careful this.
• And, in this script, even when the column of Id in arr2 sheet is moved, the script works.

Result:

When this script is used, the following result is obtained.

From

• arr1 sheet

  

• arr2 sheet

  

To:

• Result sheet

  

References:

• reduce()
• Destructuring assignment
• Spread syntax (...)