CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
scollins
scollins

Reputation: 43

Find Duplicates and Set Value to True

I have a script that sets the value to True for only one of the matching rows. There are two sheets within the same spreadsheet. I have the script check the one sheet against the other for matching value. The problem is it only puts the True next to the last matching value, not all of the different matching values. How do I have it find all possible duplicates and put True next to each one. The values it's looking for are actually registration codes; if someone unregisters they put in their reg code and it will match their registration line in the registration sheet.

function findDuplicates() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetR = ss.getSheetByName("Registration");
  var sheetCR = ss.getSheetByName("Cancel Registration")
  var dataR = sheetR.getDataRange().getValues();
  var dataCR = sheetCR.getDataRange().getValues();
  var newData = new Array();
  var headerRow = 1;
  for (var i = 1; i < dataR.length && i < dataCR.length; ++i) {
      var rowR = dataR[i];
      var rowCR = dataCR[i];
      var duplicate = false;
          if (rowR[9] === rowCR[8]) {
              duplicate = true;
          }
  }
  if (duplicate = true) {
      sheetR.getRange(headerRow + i, 11).setValue("True");
  }
}

Updated Code:

function findDuplicates() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetR = ss.getSheetByName("Registration");
  var sheetCR = ss.getSheetByName("Cancel Registration")
  var dataR = sheetR.getDataRange().getValues();
  var dataCR = sheetCR.getDataRange().getValues();
  var headerRow = 1;
  for (var i = 1; i < dataR.length && i < dataCR.length; i++) {
      var rowR = dataR[i];
      var rowCR = dataCR[i];
      var duplicate = false;
          if (rowR[9] === rowCR[8]) {
              duplicate = true;
          }
          if (duplicate === true) {
              sheetR.getRange(headerRow + i, 11).setValue("True");
          }
  }
}

Update:

function findDuplicates() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetR = ss.getSheetByName("Registration");
  var sheetCR = ss.getSheetByName("Cancel Registration")
  var dataR = sheetR.getDataRange().getValues();
  var dataCR = sheetCR.getDataRange().getValues();
  var headerRow = 1;
  for (var i = 1; i < dataR.length; i++) {
      for (var j = 1; j < dataCR.length; j++) {
          var rowR = dataR[i];
          var rowCR = dataCR[j];
          var duplicate = false;
              if (rowR[9] === rowCR[8]) {
                  duplicate = true;
              }
              if (duplicate === true) {
                  sheetR.getRange(headerRow + i, 11).setValue("Y");
              }
    }
  }
}

Upvotes: 0

Views: 827

Answers (1)

pointNclick
pointNclick

Reputation: 1632

Your if (duplicate = true) is outside the for loop. Since it's calculating the row based off of the value of i, it only changes the value for the last value of i which is the last duplicate element found in the sheets. So, I would advise you to move it into the for loop.

Tested the code with the above mentioned changes and everything works as desired. Also, your code currently runs 11 row over the limit. That is, if your row 12 is the last row with data and 13 is empty, it setting the value of column in row 13 as TRUE as well. Changing ++i to i++ in the for loop, takes care of this problem as well.

And last but not the least, your declaration of if statement requires to be duplicate == true and not duplicate = true since the first one is checking whether the value of duplicate is true or not, the second one is actually assigning the value of duplicate to be true.

Upvotes: 1

Related Questions