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|          |        |         |
+-----------+----------+--------+---------+

Could you please clarify the context of "When I test this in a web ide" - do you mean Apps Script Editor? Would be nice to see the driver function used. Please, update the question with info on when and in what execution context the function fails — Oleg Valter is with Ukraine, Jun 20 '20 at 05:07
What I meant, was when I test the single function in a web ide. Not the entire functionality. — trueCamelType, Jun 21 '20 at 01:59
Please note that the correct term for the app you were testing your code in is the Apps Script Editor. I was trying to decide on adding a relevant tag, but "web IDE" is too generic and context points to the editor. Re:test - this is exactly what I meant - driver function is the one used to test the correctness of the program. Since Tanaike gave a great answer already, just another small note - it is important to include execution context not in words, but in code, as the reason why it works in editor may be the difference between how you test the code and how it runs as a custom function. — Oleg Valter is with Ukraine, Jun 21 '20 at 06:48

score 3 · Answer 1 · answered Jun 20 '20 at 08:06

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] : [""]);
}

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

1 Answers1

Modification points:

Sample script:

Result:

References: