CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Lazmo88
Lazmo88

Reputation: 1

Checking Duplicates Script

I want to display duplicates found from the sheet in a Browser.Msg box and send the duplicate strings via email.

Additionally extra column could be written to that row where status "DUPLICATE - YES" would be written. However just to get it via email / in a popup would be enough.

I have tried logging the data. I have tried setting variables.

function checkDuplicates() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var dataRange = sheet.getRange("DATA!F2:F"); // Set Any Range
  // "A:A" is for Column A
  // And if you want to check duplicates for whole sheet then try this:
  // var dataRange = sheet.getDataRange();
  var data = dataRange.getValues();
  var numRows = data.length;
  var numColumns = data[0].length;
  var dupes = false;
  var okdupes0 = 0;
  var nodupes0 = 0;
  var totbookings0 = 0;
  
  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';
          var dupes = true;
        }
      }
          var okdupes = okdupes0++;
    }
          var totbookings = totbookings0++;
  }
  
    if (dupes) {
//    var okdupes = okdupes -1;
    var nodupes = totbookings - okdupes;
    var emailAddress = "[email protected]";  // First column
    var message = + nodupes + " Duplicate voucher(s) has been found from the system. Duplicate vouchers has been marked with red color.";       // Second column
    var subject = "System: " + nodupes + " Duplicate Voucher(s) Found!";
    MailApp.sendEmail(emailAddress, subject, message);
    Browser.msgBox('Warning!', ''+ nodupes +' Possible duplicate voucher(s) has been found and colored red! Please contact the rep who has made the sale. '+ totbookings +' bookings has been scanned through for duplicates.', Browser.Buttons.OK);
  } else {
    Browser.msgBox('All good!', 'No duplicate vouchers found.', Browser.Buttons.OK);
  }
    
  dataRange.setBackgroundColors(formats);
}

Upvotes: 0

Views: 905

Answers (1)

Alan Wells
Alan Wells

Reputation: 31300

You could convert the array of values to a string, then use match to count occurrences.

This code works to find duplicates, even from a two dimensional array. It doesn't determine what cell the duplicate came from. The values of all the duplicates are put into an array.

function findDups() {
  var testArray = [['one','two','three'],['three','four','five']];
  var allDataAsString = testArray.toString();
  Logger.log('allDataAsString: ' + allDataAsString);

  //Create one Dimensional array of all values
  var allDataInArray = allDataAsString.split(",");
  var pattern;
  var arrayOfDups = [];

  for (var i = 0;i<allDataInArray.length;i++) {
    var tempStr = allDataInArray[i];

    // the g in the regular expression says to search the whole string 
    // rather than just find the first occurrence
    var regExp = new RegExp(tempStr, "g");
    var count = (allDataAsString.match(regExp) || []).length;

    Logger.log('count matches: ' + count);

    if (count > 1 && arrayOfDups.indexOf(tempStr) === -1) {
      arrayOfDups.push(tempStr);
    };
  };

  Logger.log('arrayOfDups: ' + arrayOfDups);
  Browser.msgBox('Thest are the duplicate values: ' + arrayOfDups);

  //To Do - Send Email
};

The above example code has a hard coded two dimensional array for testing purposes. There are two occurrences of an element with the value of 'three'.

Upvotes: 1

Related Questions