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

Question

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

Answer 1

If you get the data returned as an array, you can check for existing values with the JavaScript indexOf() method.

• Get the data from the spreadsheet
• Use the value to be written to check for existing value
• If value exists inform you.

Get the Data

Google Documentation - Get Values

// The code below will get the values for the range C2:G8
// in the active spreadsheet.  Note that this will be a javascript array.
var values = SpreadsheetApp.getActiveSheet().getRange(2, 3, 6, 4).getValues();
Logger.log(values[0][0]);

Check For Duplicate:

var returnFromIndexOf = values.indexOf(valueToChk);

Inform you:

if (values.indexOf(valueToChk) != -1) {
  //Send me an email
// Send an email with two attachments: a file from Google Drive (as a PDF) and an HTML file.
 var file = DriveApp.getFileById('1234567890abcdefghijklmnopqrstuvwxyz');
 var blob = Utilities.newBlob('Insert any HTML content here', 'text/html', 'my_document.html');
 MailApp.sendEmail('mike@example.com', 'Attachment example', 'Two files are attached.', {
     name: 'Automatic Emailer Script',
     attachments: [file.getAs(MimeType.PDF), blob]
 });
};

Google Documentation - Send Email

You need to install Google Forms to your Google Drive in order to create a new form.

Google Site - Google Forms

There are ways to run some code when the form is submitted. You can run an event from either your spreadsheet or the form attached to the spreadsheet when the form is submitted.

In the Apps Script Code editor, open the Resources menu and add a trigger to run a function with the form is submitted.

Answer 2

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:

1. 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.

2. 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.