Google Script:如果其中的值存在于另一个工作表中，则删除该行 [英] Google Script: Delete row if a value in it exists in another sheet

问题描述

我有一个关于 Google 脚本的问题.我今年二月才开始使用 Google Script，所以我对它还没有很深入的了解.无论如何，我的问题是:

I have a question with Google Scripting. I just started using Google Script this February so I don't have deep understanding with it yet. Anyway my question is:

如果某行的某些值存在于另一个工作表中，我可以删除该行吗?我尝试了在互联网上找到的几个概念，但我无法想出一个有效的功能.

Can I delete a row if certain values of it exist in another sheet? I tried several concepts I found in the internet but I was not able to come up with a working function.

这是 Google 文档的详细视图.

例如，在工作表 1 中，我有两行:

For example, in Sheet 1 I have two rows:

姓名 |地点 |年龄

卡尔 |佛罗里达 |45

Carl | Florida | 45

迈克 |佛罗里达 |41

Mike | Florida | 41

在工作表 2 中:

姓名 |地点 |年龄

迈克 |佛罗里达 |41

Mike | Florida | 41

脚本应该删除工作表 1 中的 Mike Florida 行，因为它在工作表 2 中有重复的数据.所以基本上，如果工作表 2 中的数据存在于工作表 1 中，则应该在工作表 1 中删除它.但棘手的部分是，我只需要比较第 1 列和第 2 列.第 3 列并不重要.如果Sheet 2中的第1列和第2列在Sheet 1中具有相同的确切值，则应在Sheet 1中将其删除并保留Sheet 2中的数据.

The script should delete Mike Florida row in Sheet 1 since it has duplicate data in Sheet 2. So basically, if data in Sheet 2 exists in Sheet 1, it should be deleted in Sheet 1. But the tricky part is, I have to compare column 1 and column 2 only. Column 3 is not important. If column 1 and 2 in Sheet 2 have the same exact value in Sheet 1, it should be deleted in Sheet 1 and data in Sheet 2 should remain.

我在 Google 网站上尝试了这个教程，但仍然无法比较两张表并删除重复数据.

I tried this tutorial in Google site but still I was not able to compare the two sheets and remove the duplicate data.

非常感谢.任何帮助/想法/建议将不胜感激.:)

Thank you so much. Any help/idea/advice will be greatly appreciated. :)

++++++++++

我现在刚刚想出了一个代码并使用了二维数组库.

I just came up with a code now and used 2D Arrays Library.

function deleteRowInSheet1() { 
   var s1 = SpreadsheetApp.openById("COPY SPREADSHEET ID HERE").getSheetByName('Sheet1');
   var s2 = SpreadsheetApp.openById("COPY SPREADSHEET ID HERE").getSheetByName('Sheet2'); 
   var values1 = s1.getDataRange().getValues();
   var values2 = s2.getDataRange().getValues();
  // Check if Mike exists in SS1.
  if (ArrayLib.find(values2, 0, 'Mike') != -1) {
    for( var row = values1.length -1; row >= 0; --row ) {
      if (values1[row][0] == 'Mike')
      s1.deleteRow(parseInt(row)+1);
    }
  }
}

但是，此代码仅获取 Mike 的特定值.如果 Sheet 2 中存在 Mike 行，则 Sheet 1 中的 Mike 行也会被删除.我尝试更多地修改代码以比较来自两个不同工作表的两个值，但我似乎无法使其工作.

But, this code only gets specific value which is Mike. If row Mike exists in Sheet 2, row Mike in Sheet 1 will be deleted too. I tried modifying the code more to compare the two values from the two different sheets but I can't seem to make it work.

推荐答案

你使用的二维数组库的问题是 find 方法返回一个与其在数组中的位置相对应的整数，即在这种情况下，对您来说并不是真正有用的信息.我建议在两个数组中循环(这非常快)并使用布尔值来确定我们是否保留(或不保留)sheet1 中的值.这是我用于测试的代码，它似乎按预期工作.

The problem with the 2D array library you use is that the find method returns an integer corresponding to its position in the array, which is not really a helpful information for you in this case. I'd sugggest to loop in both arrays (which is very quick) and use a boolean to determine if we keep (or not) the values in sheet1. Here is the code I used for testing and it seems to work as expected.

function deleteRowInSheet1() { 
  var s1 = SpreadsheetApp.openById("1RxtFWZJRWxgW-zUM6S-dvZ0yFe-G2DGJFWI3gAt-1T0").getSheetByName('Sheet1');
  var s2 = SpreadsheetApp.openById("1RxtFWZJRWxgW-zUM6S-dvZ0yFe-G2DGJFWI3gAt-1T0").getSheetByName('Sheet2'); 
  var values1 = s1.getDataRange().getValues();
  var values2 = s2.getDataRange().getValues();
  var resultArray = [];
  for(var n in values1){
    var keep = true
    for(var p in values2){
      if( values1[n][0] == values2[p][0] && values1[n][1] == values2[p][1]){
        keep=false ; break ;
      }
    }
    if(keep){ resultArray.push(values1[n])};
  }
  s1.clear()
  s1.getRange(1,1,resultArray.length,resultArray[0].length).setValues(resultArray);
}

如您所见，我删除了整个工作表以使用新的数据数组重建它，但这样做会丢失任何可能存在的格式.如果这对您来说是个问题，那么您将不得不使用 clearContents() 代替.在此处查看文档但删除每个最后一行下方未使用的行……我猜不是很难实现.

As you can see I delete the whole sheet to rebuild it with a new data array but doing this I loose any formatting that might have been present. If that's an issue for you then you'll have to use clearContents() instead. See doc here but delete any formatting in every unused rows below the last row... not very hard to implement I guess.

这篇关于Google Script:如果其中的值存在于另一个工作表中，则删除该行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！