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!

CodePudding user response：

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.