Overview

These are sample scripts for processing the duplicated rows of 2 dimensional arrays using Google Apps Script.

Description

When I use Google Spreadsheet and/or see Stackoverflow, I sometimes see the situation which is required to process the duplicated rows of 2 dimensional arrays. I thought that when the sample scripts for it have already prepared, they will be useful for other users including me. So I published this post. This sample scripts can be also used for Javascript. If this post is useful for you, I’m glad.

Sample situation

Sample situation for scripts is as follows.

There is a Spreadsheet including values. The array retrieved by SpreadsheetApp.getActiveSheet().getDataRange().getValues() is as follows.

var ar1 = [
    ["a1", "b1", "c1"],
    ["a2", "b2", "c2"],
    ["a3", "b3", "c3"],
    ["a4", "b4", "c4"],
    ["a5", "b5", "c5"],
    ["a6", "b6", "c6"],
    ["a7", "b7", "c7"],
    ["a8", "b8", "c8"],
    ["a9", "b9", "c9"],
    ["a10", "b10", "c10"]
];

There is one more Spreadsheet including values. The array retrieved by SpreadsheetApp.getActiveSheet().getDataRange().getValues() is as follows. This array is used for checking duplicated rows.
```
var ar2 = [
    ["a1",    "dummy", "dummy"],
    ["a2",    "b2",    "dummy"],
    ["a3",    "b3",    "c3"],
    ["dummy", "dummy", "c4"],
    ["dummy", "b5",    "c5"]
];
```

Sample scripts

Here, I would like to introduce 3 sample scripts. ar1 and ar2 mentioned above were used for the following sample scripts.

Sample 1

In this sample script, the rows that the special column is duplicated are retrieved.

var checkIndex = 0;
var res = ar2.filter(function(e) {
  return ar1.some(function(f) {
    return f[checkIndex] == e[checkIndex];
  });
});

// Result: [["a1","dummy","dummy"],["a2","b2","dummy"],["a3","b3","c3"]]

In this sample, 1st column is compared and when the same value is found, the row is retrieved as the duplicated row.
If var checkIndex = 1 is used, the 2nd column is compared.

Sample 2

In this sample script, the rows that all columns are duplicated are retrieved.

var res = ar2.filter(function(e) {
  return ar1.some(function(f) {
    return e.every(function(g, k) {
      return g == f[k];
    });
  });
});

// Result: [["a3","b3","c3"]]

Sample 3

In this sample script, the rows that the special columns are duplicated are retrieved.

var checkIndex = [0, 1];
var res = ar2.filter(function(e) {
  return ar1.some(function(f) {
    return checkIndex.every(function(g) {
      return f[g] == e[g];
    });
  });
});

// Result: [["a2","b2","dummy"],["a3","b3","c3"]]

In this sample, 1st and 2nd columns are compared and when the same values are found, the row is retrieved as the duplicated row.
If var checkIndex = [1, 2] is used, the 2nd and 3rd columns are compared.
Also when you use var checkIndex = [0], the same result with “Sample 1” can be retrieved.
Also when you use var checkIndex = [0, 1, 2], the same result with “Sample 2” can be retrieved.

References

These are sample scripts. So please modify them for your situation.

Processing Duplicated Rows of 2 Dimensional Arrays using Google Apps Script