Friday, October 5, 2012

GOOGLE SPREADSHEET SCRIPT TO CHECK THE DUPLICATE ENTRIES IN THE SHEET

Note:

I have updated the script so that you can easily change the range, so now it can work to check duplicates in any column or any row or any range. And it is much more optimized:




Copy the following script in the "Script editor":

//=====================


function onOpen() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{name : "Check Duplicates",functionName : "checkDuplicates"}];
  sheet.addMenu("Scripts", entries);
};

function checkDuplicates() {
  
var sheet = SpreadsheetApp.getActiveSheet();
  
var dataRange = sheet.getDataRange();
  
var data = dataRange.getValues();
  
var numRows = data.length;
  
var numColumns = data[0].length;

  
var formats = [];
  
var values = [];
  
for (var i = 0; i < numRows; i++) {
    formats
[i] = [];
    
for (var j = 0; j < numColumns; j++) {
      formats
[i][j] = 'WHITE';
      
if (data[i][j] != '') {
        values
.push([data[i][j], i, j]);
      
}
    
}
  
}
  
var numValues = values.length;
 
  
for (var k = 0 ; k < numValues - 1; k++) {
    
if (formats[values[k][1]][values[k][2]] == 'WHITE') {
      
for (var l = k + 1; l < numValues; l++) {
        
if (values[k][0] == values[l][0]) {
          formats
[values[k][1]][values[k][2]] = 'RED';
          formats
[values[l][1]][values[l][2]] = 'RED';
        
}
      
}
    
}
  
}
   
  dataRange
.setBackgroundColors(formats);
}



//=====================

And If you are not much familiar with scripts then check out the following link:

http://igoogledrive.blogspot.in/2012/08/how-to-write-script-in-google.html 

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private or confidential projects:

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,
Kishan,

12 comments:

  1. what if I just wanted to search one column?. i assume it wouldn't be too difficult to add, however im not a proframmer.

    the script is running and its taking a loooong time, i have 17 columns and 2200 rows! :)

    ReplyDelete
    Replies
    1. Hi Ryan,

      I have mention in this post that the updated script is on the following link:
      http://igoogledrive.blogspot.com/2013/07/how-to-check-duplicates-in-any-range.html
      You'll be able to check for duplicates in any row or column or any range...

      Delete
    2. Thank you, It is very helpful..

      Delete
  2. Yes its working like charm able find the duplicate for particular column..

    In the same way can we also inject trim function into the same script to remove the space for the particular column... :)

    ReplyDelete
  3. Can the script be modified to only change the background color if there is a duplicate, i.e. it would change duplicates to red but not change non-duplicate values to white?

    ReplyDelete
    Replies
    1. @Eric W
      I Got the Same Question. Guys Please Let us know.. If it can be done.. Thank you

      Delete
  4. I copied and pasted it directly in and nothing is happening........HELP!

    ReplyDelete
  5. Eric, you create a new script project and save it. Then from the script editor you "run" it (menu item). That should ask for your authorization and you're good to go. It runs on your active spreadsheets. Worked like a charm for me.

    See the following links for further help:
    http://igoogledrive.blogspot.in/2012/08/how-to-write-script-in-google.html
    https://developers.google.com/apps-script/overview#set_it_up

    ReplyDelete
    Replies
    1. This is what I did and I'm having the same problem. It runs the script, but this doesn't do anything.

      Delete
  6. Is it possible to modify this script to place "Y" next to any row where it matches another row from a different sheet, but not based on row position (registration sheet would have all of the registrations, the cancel registration sheet would have cancelled registrations for any number of events and any number of cancelled registrations per event)? There are two columns - event id and email, that are the same in two different sheets (registration and cancel registration). So, two different sheets, with the same two columns - in different positions. Need to find duplicates - same event id and email, basically finding cancelled registrations to match against the registrations so to play a Y on the registration page next to respective row and then run a delete script.

    ReplyDelete
  7. But its not work like a excel, In a excel if i type same value in two cells , colour will change automatically in the cell. But here its finding duplicate cells if i run the script only.Please try to give solution for this.

    My query: If i give duplicate entry , same time cell color has to change.

    ReplyDelete
  8. Good Job mate thanks for the share

    ReplyDelete