Search a spreadsheet column for duplicate entries

Question

I have some customer data in a Google Spreadsheet as shown below. New entries are added regularly, and I would like to use a script to search the contact number column to check if the contact number for a new entry already exists. Also, if possible, I'd like the script to give some kind of notification that indicates the row number of the existing entry. For example, the notification could be a new column where it would just write the "row number" or "no entries found".

Data Available:

Contact Number   Email ID          Venue Address     Service Date
1234567890       Test1@gmail.com   cypress           21/04/2016
0123456789       Test2@gmail.com   river run drive   22/04/2016

What I am looking for:

Contact Number   Email ID          Venue Address     Service Date   Duplicate entry in
1234567890       Test1@gmail.com   cypress           21/04/2016     row1
0123456789       Test2@gmail.com   river run drive   22/04/2016     no entries found

Answer 1

So, your description implies that you want to search for entry before it's input. Rather than searching for duplicates, which means there is already duplicate rows in your data. I have constructed a method for both for you.

This shows an example of both, this will either find the duplicates or it will find an entry by phone number. The remainder of the logic and customization is up to you.

Here is the example sheet for this: Example Spreadsheet

Edit: Added column finding logic, and added a button you can press to see the duplicates in the sheet. NOTE: You must be signed into a google account for the script to run

//Entry Point
function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet(); //Get the current sheet
  var dataRange = sheet.getDataRange();
  var valuesRange = dataRange.getValues(); //The array of data on the sheet
  var columns = GetColumns(valuesRange, dataRange.getNumColumns(), 0);

  var duplicates = SearchForDuplicates(valuesRange, columns.columns['Contact Number'].index); //Search for existing duplicates
  var elementIndex = SearchForValue(valuesRange, columns.columns['Contact Number'].index, 123456789); //Search for a phone number of 123456789

  if(duplicates.length > 0){ //If there are duplicates
    var isDuplicateColumn = columns.columns['Is Duplicate'].index;
    for(var  i = 0; i < duplicates.length; i++){
      valuesRange[duplicates[i].index][isDuplicateColumn] = 'Yes'; //Assign Yes to the appropriate row
    }
    dataRange.setValues(valuesRange); //Set the spreadsheets values to that of the modified valuesRange
  }  
  Logger.log(duplicates);
  Logger.log(elementIndex);
}

//Searches an array for duplicate entries
function SearchForDuplicates(array, columnIndex){
  var uniqueElements = {};
  var duplicates = [];
  for(var  i = 0; i < array.length; i++){
    if(typeof uniqueElements[array[i][columnIndex]] === 'undefined'){ 
      uniqueElements[array[i][columnIndex]] = 0;  //If the element does not yet exist in this object, add it
    } else {
      duplicates.push({row: array[i], index: i}); //If the element does exist, it's a duplicate
    }
  }
  return duplicates;
}

//Searches an array for a value
function SearchForValue(array, columnIndex, value){
  for(var  i = 0; i < array.length; i++){
    if(array[i][columnIndex] == value){
      return i;  //Element found, return index
    }
  }
  return -1; //No element found, return -1
}

//Gets a columns object for the sheet for easy indexing
function GetColumns(valuesRange, columnCount, rowIndex)
{
  var columns = {
    columns: {},
    length: 0
  }

  Logger.log("Populating columns...");
  for(var i = 0; i < columnCount; i++)
  {
    if(valuesRange[0][i] !== ''){
      columns.columns[valuesRange[0][i]] = {index: i ,value: valuesRange[0][i]};
      columns.length++;      
    }
  }  
  return columns;
}