Copy one of the 2 duplicates from 2 different sheets into the 3rd one-CodePudding

I have 1 spreadsheet with multiple sheets. The 1st and the 2nd sheets sometimes have similar data in the rows (duplicates). Also, each sheet has a column (8) with a checkbox.

Task: I need to move one of the duplicates to the 3rd sheet when the checkboxes in both sheets (1st and 2nd) are checked.

Here is the code that moves the row when it's checked in a single sheet.

Can someone help me modify it to complete my task?

function onEdit(e) {
let range = e.range;
let col = range.getColumn();
let row = range.getRow();
let val = range.getValue();
let source = e.source.getActiveSheet();
let ss = SpreadsheetApp.getActiveSpreadsheet();
let sourceSheet = ss.getSheetByName(source.getName());


if (sourceSheet.getName() == 'SHEET1' && col == 8 && val == true){

  let data = sourceSheet.getRange(row,1,1,7).getValues();
  let targetSheet = ss.getSheetByName('SHEET2');
  targetSheet.appendRow(data[0]);
  sourceSheet.deleteRow(row);
}

if (sourceSheet.getName() == 'SHEET2' && col == 8 && val == true){

  let data = sourceSheet.getRange(row,1,1,7).getValues();
  let targetSheet = ss.getSheetByName('SHEET3');
  targetSheet.appendRow(data[0]);
  sourceSheet.deleteRow(row);
}
}

CodePudding user response：

Would that do what you want ?

function copyDuplicateRows() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet1 = spreadsheet.getSheetByName("Sheet1");
  var sheet2 = spreadsheet.getSheetByName("Sheet2");
  var sheet3 = spreadsheet.getSheetByName("Sheet3"); // destination sheet
  
  var data1 = sheet1.getDataRange().getValues();
  var data2 = sheet2.getDataRange().getValues();
  
  // Use a JavaScript object to keep track of duplicate rows
  var seen = {};
  
  // Iterate through data1 and copy unique rows to sheet3
  for (var i = 0; i < data1.length; i  ) {
    var row = data1[i];
    var key = row.join(); // create a unique key for each row
    if (!seen[key]) {
      seen[key] = true;
      sheet3.appendRow(row);
    }
  }
  
  // Iterate through data2 and copy unique rows to sheet3
  for (var i = 0; i < data2.length; i  ) {
    var row = data2[i];
    var key = row.join();
    if (!seen[key]) {
      seen[key] = true;
      sheet3.appendRow(row);
    }
  }
}

CodePudding user response：

Apply filter()

You can add the filter() method (combined with the JSON.stringify() method) to your script to determine if a row is a duplicate of another from the other sheet. The modified script should somehow look like this:

function onEdit(e) {
  var range = e.range;
  var col = range.getColumn();
  var row = range.getRow();
  var val = range.getValue();
  var source = e.source.getActiveSheet();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName(source.getName());
  var targetSheet = ss.getSheetByName('Sheet3');
  
  //If Sheet1 checkbox is triggered
  if (sourceSheet.getName() == 'Sheet1' && col == 8 && val == true) {
    var rowArr = ss.getSheetByName('Sheet1').getRange(row, 1, 1, 8).getValues();
    var sheet2Values = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet2').getDataRange().getValues();
    var count = sheet2Values.filter(x => {
      return (JSON.stringify(x) === JSON.stringify(rowArr[0]))
    });
    if (count.length > 0) {
      targetSheet.appendRow(rowArr[0]);
      sourceSheet.deleteRow(row);
    }
  }

  //If Sheet2 checkbox is triggered
  if (sourceSheet.getName() == 'Sheet2' && col == 8 && val == true) {
    var rowArr = ss.getSheetByName('Sheet2').getRange(row, 1, 1, 8).getValues();
    var sheet1Values = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1').getDataRange().getValues();
    var count = sheet1Values.filter(x => {
      return (JSON.stringify(x) === JSON.stringify(rowArr[0]))
    });
    if (count.length > 0) {
      targetSheet.appendRow(rowArr[0]);
      sourceSheet.deleteRow(row);
    }
  }

}

Output

Since you have not shared a sample spreadsheet, I have created a test case to apply the script as shown below:

Apply filter()

Output

Reference