Question

0

210

Views

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

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.

about 4 years ago · Juan Pablo Isaza

3 answers

Answer question

0

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;
}

about 4 years ago · Juan Pablo Isaza Report

0

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

about 4 years ago · Juan Pablo Isaza Report

Answer question

Find remote jobs

Accepted Answer · 2022-06-24T18:40:10.051Z

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.

0

210