Gists

# 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.

1. 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"]
];

2. 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.