Get Unique Value Based on Multiple Columns from Spreadsheet using Google Apps Script and JavaScript

Question

So, Basically what I want is to get unique data from a 2d array where I can pass multiple column values which I have stored in an array. Like I want to get unique based on column 1 and 2 so, I can pass the column value like an array [0, 1] to the function and based on the column array it should return unique value.

I have tried something like this:

function sortFunction(a, b) {
  if (a[0] === b[0]) {  // 0 for column one
    return 0;
  }
  else {
    return (a[0] < b[0]) ? -1 : 1;
  }
}

function getUniqueData_(arr) {
  arr = arr.sort(sortFunction)

  let prevEl;
  const newArr = arr.reduce((prevArr, currentVal) => {
    const idx = prevArr.findIndex(val => val[0] === currentVal[0]);
    if (idx === -1) {
      if (prevEl && prevEl[0] !== currentVal[0]) {
        prevArr.push(currentVal);
      }
    } else {
      prevArr.splice(idx, 1);
    }
    prevEl = currentVal;
    return prevArr;

  }, [])

  console.log(newArr);
  return newArr;
}

Instead of passing column value manually I want to pass it dynamically and multiple columns at the same time which I have stored it in array like [0,1] for column 1 and 2.

Hi, I'm not sure I understand your goal here. Do you want to remove the duplicates from a 2D array? — Iamblichus, Jun 16 '22 at 10:42
Could you clarify how the result should look like in this case: https://imgur.com/9y7PuqR.png ? — Yuri Khristich, Jun 17 '22 at 09:36

score 3 · Accepted Answer · answered Jun 17 '22 at 08:14

Solution:

Use filter to remove the undesired elements from your array.
For each row, use map to get the combination of values related to the columns you want to check, so that if another row exists with this combination, both rows should be filtered out.
For each row, use some to check whether there is another row with this same combination.
Use every to compare both combinations and check they are the same.

Code sample:

function getUniqueData(arr = [[1, 2, 3],[1, 2, 2],[1, 2, 3],[2, 1, 3]], uniqueCols = [0]) {
  const uniqueData = arr.filter((currentRow,i) => {
    const currentCombo = uniqueCols.map(index => currentRow[index]);
    return !arr.some((row,j) => {
      const combo = uniqueCols.map(index => row[index]);
      return combo.every((c1,k) => c1 === currentCombo[k]) && i !== j;
    });
  });
  return uniqueData;
}

Note:

In the sample above, I added the example you provided in comments data = [[1, 2, 3],[1, 2, 2],[1, 2, 3],[2, 1, 3]] and I want to check unique in column 1 as default parameters. In this case, the returned uniqueData is [2, 1, 3], as expected.

Arslan Arshad · Answer 2 · 2022-06-16T14:07:22.500

1

Try this code:

function sortData(){
  //change data and column index according to requirements
  var columns = [0,2] //0 for column 1
  var data = [[1,2,3],[1,2,2],[1,2,3]]
  var temp_arr = [];
  var unique_arr = []; // for output
  var specific_columns_data = getSpecificColumnsData(data, columns) // this function return array according to columns. eg [[1,3],[1,2],[1,3]]
  
  for(var i=0;i<specific_columns_data.length;i++){
    if(specific_columns_data[i].join("")!==""){
      
      if(temp_arr.indexOf(specific_columns_data[i].join(""))==-1){
        unique_arr.push(data[i]);
      }
      temp_arr.push(specific_columns_data[i].join(""))
    }
  }
  Logger.log(unique_arr)
}

function getSpecificColumnsData(data, columns){
  var specific_columns_data = []
  var temp_arr = []
  for(var i=0;i<data.length;i++){
    temp_arr = []
    for(var j = 0;j<columns.length;j++){
      if(data[i][j]){
        temp_arr.push(data[i][columns[j]])
      }
    }
    specific_columns_data.push([temp_arr])
  }
  return specific_columns_data;
}

edited Jun 16 '22 at 14:07

answered Jun 16 '22 at 11:45

Arslan Arshad

91
2

While this code snippet may be the solution, [including an explanation](//meta.stackexchange.com/questions/114762/explaining-entirely-‌code-based-answers) really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. – TheMaster Jun 16 '22 at 11:52
@Arslan Arshad still my problem is not solved. What I want is that, let say data = [[1, 2, 3],[1, 2, 2],[1, 2, 3],[2, 1, 3]] and I want to check unique in column 1 so, unique data should be [2, 1 ,3] based on column 1 as 1 is repeating number in column 1. – Mohd Imran Jun 17 '22 at 05:45
@MohdImran just replace "unique_arr.push(data[i])" with unique_arr.push(specific_columns_data[i]); – Arslan Arshad Jun 20 '22 at 04:38

Yuri Khristich · Answer 3 · 2022-06-17T10:46:16.403

Just in case, here is my variant of the code. It can filter a 2D array by any number of its columns in any given sequence:

var data = [
    [1, 2, 3, 4],
    [1, 2, 2, 4],
    [2, 1, 3, 4],
    [3, 1, 2, 4],
    [4, 3, 3, 4],
    [5, 2, 5, 1],
] 

function getUniqueData(data, columns) {
    let arr = data.slice();

    columns.forEach(c => {
        let col = arr.map(x => x[c]);
        arr = arr.filter(x => col.indexOf(x[c]) == col.lastIndexOf(x[c]));
    });
    
    return arr;
}

// get unique rows by column 1
console.log(getUniqueData(data, [0]));

// get unique rows by column 1 and then by column 2
console.log(getUniqueData(data, [0,1]));

// get unique rows by column 1 and then by column 3
console.log(getUniqueData(data, [0,2]));

// get unique rows by column 1 and then by column 4
console.log(getUniqueData(data, [0,3]));

// get unique rows by column 3
console.log(getUniqueData(data, [2]));

// get unique rows by column 4
console.log(getUniqueData(data, [3]));

// get unique rows by column 2, then by column 3, then by column 4
console.log(getUniqueData(data, [1,2,3]));

// etc

Get Unique Value Based on Multiple Columns from Spreadsheet using Google Apps Script and JavaScript

3 Answers3

Solution:

Code sample:

Note:

Linked