谷歌脚本：删除行，如果其中的一个值存在于另一个表中 [英] Google Script: Delete row if a value in it exists in another sheet
问题描述
我对Google脚本有疑问。今年2月我刚刚开始使用Google Script，因此我对此尚无深入了解。无论如何，我的问题是：
如果某行的某些值存在于另一张表中，我可以删除该行吗？我尝试了几个我在互联网上找到的概念，但我无法想出一个工作函数。
以下是 Google文档了解详情。
例如，例如在Sheet 1中，我有两行：
NAME | PLACE | AGE
Carl |佛罗里达| 45
Mike |佛罗里达| 41
在图表2中：
NAME | PLACE | AGE
Mike |佛罗里达| 41
脚本应该在Sheet 1中删除Mike Florida行，因为它在Sheet 2中有重复的数据。工作表2中的数据存在于工作表1中，应该在工作表1中删除。但棘手的部分是，我必须仅比较第1列和第2列。第3栏并不重要。如果工作表2中的第1列和第2列在工作表1中具有相同的确切值，则应在工作表1中删除它，并保留工作表2中的数据。
我试过这个在Google网站中教程，但仍然无法比较两张图片并删除重复的数据。
非常感谢。任何帮助/想法/建议将不胜感激。 ：）
+++++++++
现在我只想出一个代码，使用2D阵列库。
pre $ function deleteRowInSheet1（）{
var s1 = SpreadsheetApp.openById（COPY SPREADSHEET ID HERE）。getSheetByName 'Sheet 1中'）;
var s2 = SpreadsheetApp.openById（COPY SPREADSHEET ID HERE）。getSheetByName（'Sheet2'）;
var values1 = s1.getDataRange（）。getValues（）;
var values2 = s2.getDataRange（）。getValues（）;
//检查Mike是否存在于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的具体价值。如果行2中存在行Mike，则表1中的行Mike也将被删除。我尝试修改代码更多来比较两个不同的工作表中的两个值，但我似乎无法使其工作。
您使用的二维数组库的问题是 find 方法返回一个与它在数组中的位置相对应的整数，在这种情况下这对你来说并不是一个有用的信息。
我建议在两个数组中循环（这非常快），并使用布尔值来确定我们是否保留（或不）在sheet1中的值。
这里是我用于测试的代码，它似乎按预期工作。
函数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;休息;
}
}
if（keep）{resultArray.push（values1 [n]）};
}
s1.clear（）
s1.getRange（1,1，resultArray.length，resultArray [0] .length）.setValues（resultArray）;
$ b 正如你所看到的，我删除了整个工作表以重新构建一个新数据数组，但是这样做会丢失可能存在的任何格式。如果这是你的问题，那么你将不得不使用 clearContents（）来代替。 在此处查看文档，但在每个位置删除任何格式没有使用的行在最后一行下面......我不是很难实现我猜。
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.
Here's the Google Doc for a detailed view.
For example, in Sheet 1 I have two rows:
NAME | PLACE | AGE
Carl | Florida | 45
Mike | Florida | 41
And in Sheet 2:
NAME | PLACE | AGE
Mike | Florida | 41
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.
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);
}
}
}
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.
解决方案 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);
}
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.
这篇关于谷歌脚本：删除行，如果其中的一个值存在于另一个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
