Why isn't array Intersection in google spreadsheet working as expected?

Question

This is my first time using google spreadsheets, and I'm trying to accomplish something in the functions. I know it's javascript (A language I'm not entirely familiar with). I'm trying to take a range from two tables, and get the intersection location of each, and fill in missing values for rows that already should exist.

For example, assuming the following two sheets:

Sheet1
+-----------+----------+--------+---------+
|   Fruit   |  Color   | Weight | isApple |
+-----------+----------+--------+---------+
| Banana    | Yellow   |      3 | no      |
| Orange    | Orange   |      3 | no      |
| Apple     | Red      |      2 | yes     |
| Pineapple | Brownish |      5 | no      |
+-----------+----------+--------+---------+

Sheet2
+-----------+----------+--------+---------+
|   Fruit   |  Color   | Weight | isApple |
+-----------+----------+--------+---------+
| Banana    |          |        |         |
| Apple     |          |        |         |
| Pear      |          |        |         |
| Watermelon|          |        |         |
+-----------+----------+--------+---------+

I want to find the intersections of the Fruit row, and fill in the color, weight, and isApple, of each one we know.

I wrote something I felt confident should work to get the intersection of range1, and it returns an empty array for some reason. When I test this in Apps Script Editor, it seems to work fine.

//assuming range1 = Sheet1!A2:A5 and range2=Sheet2!A2:A5
function intersection(range1, range2) {
  var i = 0;
  var j = 0;
  var matches = new Array();

  while(i < range1.length){
    if(range2.toString().includes(range1[i].toString())){
      matches.push(i);
    }
    i++
  }
  return matches;
}

I would expect this to return an array of [0,2] since the 0th element Banana, and the 1st element Apple from Sheet1, exist in sheet 2.

I would then use that data to fill in the rows for Apple, and Banana from the information in Sheet2.

I'm not at that second part yet, since I can't seem to get the sheet to even find the intersection.

My end goal expected output would be that Sheet 2 is changed to:

Sheet2
+-----------+----------+--------+---------+
|   Fruit   |  Color   | Weight | isApple |
+-----------+----------+--------+---------+
| Banana    | Yellow   |      3 | no      |
| Apple     | Red      |      2 | yes     |
| Pear      |          |        |         |
| Watermelon|          |        |         |
+-----------+----------+--------+---------+

Answer 1

I believe your goal as follows.

• You want to achieve the result from "Sheet1" and "Sheet2" as shown in your question.
• You want to achieve this using the custom function.

For this, how about this answer?

Modification points:

• In your script, I thought that you are using the custom function of =intersection(Sheet1!A2:A5,Sheet2!A2:A5). In this case, the arguments of range1 and range2 of function intersection(range1, range2) { are [["Banana"],["Orange"],["Apple"],["Pineapple"]] and [["Banana"],["Apple"],["Pear"],["Watermelon"]], which are 2 dimensional arrays, respectively. Namely, the values on the sheet are sent to the arguments. In order to achieve your goal, I would like to propose to use the custom function like =intersection(Sheet1!A2:D5,Sheet2!A2:A5). By this, the values from "Sheet1" can be used in the custom function.
• In your script, the value of [0,2] is retrieved as matches. This is the indexes of range1. But in this case, it is required to also know the indexes matching to range2. This has already mentioned in your question. In this case, how about the following flow?
• In order to achieve your goal, I would like to propose the following flow.
  1. From the values of "Sheet1", create an object for searching values of the column "A".
  2. From the values of "Sheet2", create an result array using the created object.

The sample script reflected above flow is as follows.

Sample script:

Please copy and paste the following script, and out the custom function =intersection(Sheet1!A2:D5,Sheet2!A2:A5) to the cell "B2" of "Sheet2". By this, the result that you showed at the bottom of your question is obtained.

function intersection(values1, values2) {
  const obj = values1.reduce((o, [a, ...bcd]) => Object.assign(o, {[a]: bcd}), {});
  return values2.map(([a]) => obj[a] ? obj[a] : [""]);
}

Result:

References:

• Custom Functions in Google Sheets
• reduce()
• Object.assign()
• Spread syntax (...)
• map()