如果其他工作表中存在行值，则删除多个工作表中的行值

Question

下面的代码来自这篇文章中关于将行值复制到新工作表（如果它存在于另一个工作表中）的答案。

现在，如果不是将重复值复制到第3页，而是要将它们从第1页和第2页中删除（如果在第3页中存在）。使用相同的电子表格，我有3张表格。将在前两张图纸上进行比较的唯一值是第一列“ID编号”。

给定所有3张表中存在的值，即784 | John Stiple | I.T Department，应删除表1和表2中的相同行值，并保留表3中的相同值。

function copyRowtoSheet3() { 
  var s1 = SpreadsheetApp.openById("ID").getSheetByName('Sheet1');
  var s2 = SpreadsheetApp.openById("ID").getSheetByName('Sheet2'); 
  var s3 = SpreadsheetApp.openById("ID").getSheetByName('Sheet3'); 
  var values1 = s1.getDataRange().getValues();
  var values2 = s2.getDataRange().getValues();
  var resultArray = [];
  for(var n=0; n < values1.length ; n++){
    var keep = false;
    for(var p=0; p < values2.length ; p++){
      Logger.log(values1[n][0]+' =? '+values2[p][0]);
      if( values1[n][0] == values2[p][0] && values1[n][3] == values2[p][4]){
        resultArray.push(values1[n]);
        Logger.log('true');
        break ;// remove this if values are not unique and you want to keep all occurrences...
      }
    }
  }  
  s3.getRange(+1,1,resultArray.length,resultArray[0].length).setValues(resultArray);
}

似乎找不到正确的解决方案。尝试了几个脚本，但都失败了。

谢谢你的建议。

Answer 1

表一

ID NUMBER   NAME        DEPARTMENT
784         John Steep  I.T.
901         Liz Green   H.R.

表2

ID NUMBER   NAME        DEPARTMENT
784         John Steep  I.T.
653         Bo Gore     Marketing

表3

ID NUMBER   NAME        DEPARTMENT
784         John Steep  I.T.
999         Frank White Sales
121         Abid Jones  Engineering
901         Liz Green   H.R.

剧本

function main() {
  var ss = SpreadsheetApp.openById("ID");

  var s1 = ss.getSheetByName("Sheet1");  
  var s2 = ss.getSheetByName("Sheet2");  
  var s3 = ss.getSheetByName("Sheet3");  

  var idCol = 1;  // Assuming location of ID column is same in all sheets.

  var s1RowCount = s1.getLastRow();  

  for (var i = 2; i <= s1RowCount; i++) {  // Start at var i = 2 to skip the 
                                           // first row containing the header.
    var id = s1.getRange(i, idCol, 1, 1).getValue();
    deleteDuplicates(s2, id);
    deleteDuplicates(s3, id);
  }
}

function deleteDuplicates(sheet, id) {
  var idCol = 1; // Assuming location of ID column is same in all sheets.
  var rowCount = sheet.getLastRow();
  for (var i = 2; i <= rowCount; i++) {
    var data = sheet.getRange(i, idCol, 1, 1).getValue();
    if (data === id) {
      // Use this to test out the function.
      Logger.log("Duplicate of ID " + id + " in sheet " + 
          sheet.getSheetName() + " at row " + i);
      // Uncomment the next line when ready.
      // sheet.deleteRow(i);
    }
  }
}

测井输出

[14-11-04 09:16:04:551 PST] Duplicate of ID 784 in sheet Sheet2 at row 2
[14-11-04 09:16:04:587 PST] Duplicate of ID 784 in sheet Sheet3 at row 2
[14-11-04 09:16:04:727 PST] Duplicate of ID 901 in sheet Sheet3 at row 5

Answer 2

虽然另一个答案有效（我没有测试，但我想是的），但它使用了很多电子表格应用程序调用，如果你有很多数据，可能会很慢。

仅使用数组（如果不需要保留工作表格式和/或公式）即可获得此结果。

这种方法略有不同，因为保存数据比删除数据更容易。

当然有很多可能的解决方案，下面是我尝试过的一个：我创建了一个特殊的数组，它只包含sheet3的第一列，以简化重复搜索。

function removeDupsInOtherSheets() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s1 = ss.getSheetByName("Sheet1").getDataRange().getValues();  
  var s2 = ss.getSheetByName("Sheet2").getDataRange().getValues(); 
  var s3 = ss.getSheetByName("Sheet3").getDataRange().getValues();  
  // iterate s3 and check in s1 & s2 if duplicate values exist
  var nS1 = [];
  var nS2 = [];
  var s3Col1 = [];// data in column1 of sheet3
  for(var n in s3){
    s3Col1.push(s3[n][0]);
  }
  for(var n in s1){ // iterate sheet1 and test col 1 vs col 1 in sheet3
    var noDup1 = checkForDup(s1[n],s3Col1)
    if(noDup1){nS1.push(noDup1)};// if not present in sheet3 then keep
  } 
  for(var n in s2){  // iterate sheet2 and test col 1 vs col 1 in sheet3
    var noDup2 = checkForDup(s2[n],s3Col1)
    if(noDup2){nS2.push(noDup2)};// if not present in sheet3 then keep
  }
  Logger.log(nS1);// view result
  Logger.log(nS2);
  ss.getSheetByName("Sheet1").getDataRange().clear();// clear and update sheets
  ss.getSheetByName("Sheet2").getDataRange().clear();
  ss.getSheetByName("Sheet1").getRange(1,1,nS1.length,nS1[0].length).setValues(nS1);
  ss.getSheetByName("Sheet2").getRange(1,1,nS2.length,nS2[0].length).setValues(nS2);
}

function checkForDup(item,s){
  Logger.log(s+' = '+item[0]+'  ?')
    if(s.indexOf(item[0])>-1){
      return null;
    }
  return item;
}