Question

0

228

Views

Why isn't it removing the duplicates from a Sheets Column?

I'm retriving the values from a column, where we have many duplicates. The goal is to keep only the unique values, but none of the methods I've tried has worked.

Here's what I've tried:

function listaProdutos() {
  const ssBDCadProd = SpreadsheetApp.openById(CAD_PRODUTO);
  const sheetBDCadProd = ssBDCadProd.getSheetByName('CadProduto');
  let listaProd = sheetBDCadProd.getRange(2, 6, sheetBDCadProd.getLastRow(), 1).getValues();
  let uniques = [...new Set(listaProd)];
  Logger.log('Únicos: ' + uniques);
}

Here's another attempt:

function listaProdutos() {
  const ssBDCadProd = SpreadsheetApp.openById(CAD_PRODUTO);
  const sheetBDCadProd = ssBDCadProd.getSheetByName('CadProduto');
  let listaProd = sheetBDCadProd.getRange(2, 6, sheetBDCadProd.getLastRow(), 1).getValues();
  let uniques = removeDups(listaProd);
  Logger.log('Únicos: ' + uniques);
}

function removeDups(array) {
  var outArray = [];
  array.sort();
  outArray.push(array[0]);
  for (var n in array) {
    Logger.log(outArray[outArray.length - 1] + '  =  ' + array[n] + ' ?');
    if (outArray[outArray.length - 1] != array[n]) {
      outArray.push(array[n]);
    }
  }
  return outArray;
}

All of them log the values repeatedly and I'm not sure if I'm missing any implicit characteristics of how the data comes in with getValues()

Thank you!

about 4 years ago · Juan Pablo Isaza

1 answers

Answer question

0

Answer question

Find remote jobs

Accepted Answer · 2022-07-04T16:57:54.322Z

In your script, let listaProd = sheetBDCadProd.getRange(2, 6, sheetBDCadProd.getLastRow(), 1).getValues(); is the 2 dimensional array like [["f2"],["f3"],,,]. In this case, [...new Set(listaProd)] cannot remove the duplicated rows. When you want to remove the duplicated rows, how about the following modification?

Modified script:

From:

let uniques = [...new Set(listaProd)];

To:

let uniques = [...new Set(listaProd.map(([f]) => f))];

This returns 1-dimensional array. When you want to return the 2-dimensional array, please use the following modification.

let uniques = [...new Set(listaProd.map(([f]) => f))].map(f => [f]);

Note:

In the case of Logger.log('Únicos: ' + uniques);, even when uniques is a 2-dimensional array, the value of the comma-separated value like f2,f3,,,, is shown. Please be careful about this.

0

228