google-apps-script google-sheets unique-values

generating all the non-unique values from one column in spreadsheet

I have this form of spreadsheet

 A
ABC
DEF
GHI
FOO
BAR
BAZ
ABC

Of-course that the table is much much longer.

I want to insert more values, but I want to validate their uniqueness first. In another words, whenever I insert a value that already exist in the table i want the spread sheet to inform me.

I was also wondering if there is a way to insert data to the spread-sheet from a form window that will inform and will not insert my data when I am trying to insert non-unique values.

Thanks in advance

Solution

I want to insert more values, but I want to validate their uniqueness first. In another words, whenever I insert a value that already exist in the table i want the spread sheet to inform me.

If you mean you are entering values directly into the sheet and would like to perform a uniqueness check on enter/edit of data in a cell, the you have 2 ways to do it:

Using Data Validation.

If the column you want to have unique values is column A, then select the whole column by clicking on its header, select menu Data->Validation..., then under Criteria select "Custom formula is" option and enter the following formula:
```
=IF(ROW(A1)=1, ISNA(MATCH(A1, $A$2:$A, 0)), IF(ROW(A1)=ROWS($A:$A), ISNA(MATCH(A1, INDIRECT("$A$1:$A$"&(ROWS($A:$A)-1)), 0)), AND(ISNA(MATCH(A1, INDIRECT("$A$1:$A$"&(ROW(A1)-1)), 0)), ISNA(MATCH(A1, INDIRECT("$A$"&(ROW(A1)+1)&":$A"), 0)))))
```
This will check the value you entered against all other values in column A to make sure it is unique. Existing non-unique cells will get a comment inserted into them notifying you that the value is non-unique. Edit the formula accordingly if your unique column is not column A.

Using onEdit() trigger. You can add an onEdit() trigger to your sheet like the one below, which will check edited cell(s), alert you if edited values are non-unique to the column, and remove them. [UPDATE 2014-10-05: The code has been re-written to work correctly with pasted values, even multi-column pasting. Change columnToCheck variable value to the column you want monitored for uniqueness. See comments in code for more details.]

function onEdit(e) {
  var r = e.range; // reference to edited range of cells
  var columnToCheck = 2; // which column should be monitored for uniqueness? 1=A, 2=B, ... Change as necessary.
  var isMultiColRange = (r.getNumColumns()>1); // check if edited range has single column or multiple columns
  if (isMultiColRange) {
    // if range has multiple columns, check that it includes our monitored column
    var monitoredColumnIsInRange = 0;
    for ( var i=1; i<=r.getNumColumns(); ++i) {
      if (r.getCell(1, i).getColumn()==columnToCheck) {
        monitoredColumnIsInRange = i;
        break;
      }
    }
  }
  else {
    // if edited range is single column, check that that is the monitored column
    var monitoredColumnIsInRange = (r.getColumn()==columnToCheck) ? 1 : 0;
  }
  if (monitoredColumnIsInRange) { // only proceed if monitored column was edited
    var monitoredColValues = r.getValues().map(function(el){return el[monitoredColumnIsInRange-1];}); // store edited/pasted values of monitored column in array
    if (monitoredColValues.join("")!="") { // only proceed if non-blank values were entered
      var ss = SpreadsheetApp.getActiveSheet(),
          numRows = ss.getMaxRows(),
          rangeFirstRow = r.getRow(), 
          rangeLastRow = rangeFirstRow+r.getNumRows()-1;
      var values; // will hold an array of current monitored column values
      // get all values in monitored column except currently edited cell's value
      if ( rangeFirstRow==1 ) { // data was entered/edited/pasted into first row in monitored column
        values = ss.getRange(rangeLastRow+1, columnToCheck, numRows-rangeLastRow).getValues();
      }
      else if (rangeLastRow==numRows) { // data was entered/edited/pasted into the last cell in monitored column
        values = ss.getRange(1, columnToCheck, numRows-r.getNumRows()).getValues();
      }
      else { // data was entered/edited/pasted into some other cell in monitored column
        values = ss.getRange(1, columnToCheck, rangeFirstRow-1).getValues().concat(ss.getRange(rangeLastRow+1, columnToCheck, numRows-rangeLastRow).getValues());
      }
      values = values.join().split(","); // convert current values of monitored column into a 1-D array
      var arrDuplicates = []; // will hold non-unique edited/pasted values for alert prompt
      // loop over edited/pasted values and check each for uniqueness
      for ( var j=0, lenEditedValues=monitoredColValues.length; j<lenEditedValues; j++ ) {
        var val = monitoredColValues[j].toString(); // need .toString(), otherwise numbers become decimal values, which 
        if ( values.indexOf(val)>-1 ) { // this value is NOT unique
          arrDuplicates.push(val); // save it in arrDuplicates for reporting
          r.getCell(j+1, monitoredColumnIsInRange).clear(); // clear the cell value
        }
        else { // this value is unique => add it to values array so that it is used in further uniqueness checks (to prevent pasting multiple same values) 
          values.push(val);
        }
      }
      if ( arrDuplicates.length ) {
        SpreadsheetApp.getUi().alert("You entered "+arrDuplicates.length+" values ("+arrDuplicates.join(', ')+") that are NOT unique to the column.\nThese values will be removed.");
      }
    }
  }
};

The above is an example - adapt to your own needs.

I was also wondering if there is a way to insert data to the spread-sheet from a form window that will inform and will not insert my data when I am trying to insert non-unique values.

This is definitely possible. You could create and publish a webapp using HTMLService, for example, with an HTML form that allows you to enter data, checks it for uniqueness, and inserts it into your spreadsheet if all is OK, or shows an error if data is non-unique. Sandy Good has given you some starting points in his answer.