Resolved: 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.

Answer:

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.

If you have better answer, please add a comment about this, thank you!

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

Resolved: Why reference in pointer array doesn’t have data?

Resolved: EntityFramework creates/runs migrations using parameterless DataContext instance

Resolved: Visual Studio 2022 crashes when using breakpoints

Leave A Reply