Sending Duplicate Data To Array, Then Checking If Said Array Is Empty Or Not To Decide What Code To Run Next, If Statement Not Working Correctly

I'm trying to include a duplicate check in a script that uses a Google Sheet as an input form for data that sends it to another spreadsheet. I'm trying to set up the check to preve

Solution 1:

Issue:

It is finding duplicates because you are retrieving several empty cells, and two empty strings are duplicates from each other.

Answer :

At readData, you are providing lastRow as the third parameter of getRange(row, column, numRows). This refers to the number of rows in your range. Since the first row is 6, the last 6 rows in the retrieved range will be empty cells. You can fix that by making your third parameter var numRows = lastRow - firstRow + 1 instead.

Also, in order to make sure you have no empty cells in the column, you can remove them from your array via filter(String) (see filter).

Also, I'd suggest using flat() in order to get a 1D array, instead of the more complicated concat and apply process you're using.

Code snippet:

functionreadData() {
  var dataColumn = 3;
  var firstRow = 6;
  var lastRow = ssSheet.getLastRow();
  var numRows = lastRow - firstRow + 1;
  var columnRange = ssSheet.getRange(firstRow, dataColumn, numRows);
  var rangeArray = columnRange.getValues().flat().filter(String);
  return rangeArray;
}

Solution 2:

Wouldn't it also be possible to have a cell somewhere that checked =COUNTA() vs =COUNTUNIQUE() and then your script could simply reference whether the value in that cell was true or false before sending?