查询 2 组表格数据并识别差异

Question

我正在尝试使用应用程序脚本查询 2 个数据集并比较它们之间的某些列。 我希望...

a(识别缺失的 ID 值;

b(当 ID 值匹配时，协调其他字段中的差异。

输入：具有 2 个选项卡
(选项卡 1、选项卡 2(的电子表格。
每个 B 列中的密钥 ID(Btab1、Btab2(

• 我想确定唯一值 (B( 在一个数据集中但不在另一个数据集中的实例(行顺序不同(

• 运行函数并推送到输出选项卡，如果 Btab1 不在选项卡 2 中 ||Btab2 不在选项卡 1 中

• 当两个选项卡中都有 B 的值时(大多数情况下(，我想在几列中识别数据差异的实例......

• 对于 B 的所有实例，如果...

  • 选项卡 1 中的列 M 与选项卡 2 中的列 E 不匹配
  • 选项卡 1 中的列 P <>选项卡 2 中的列 F
  • 列 AN 选项卡 1 <> 列 G 选项卡 2

输出： 显示数据集中问题区域的选项卡。
第一列是 ID 密钥。
第二列通过文本字符串解释问题

同样，这里的挑战是值的排序不同，并且总 # 行可能会略有不同

function compare() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
ss.insertSheet(1);
ss.getActiveSheet().setName('output');
var sheet1 = ss.getSheetByName('sheet1');
var sheet2 = ss.getSheetByName('sheet2');
var sheet_output = ss.getSheetByName('output');

var range1 = sheet1.getRange(1,1,sheet1.getLastRow(),sheet1.getLastColumn()).getValues(); 
var output1 = [];
var a1;
var b1;
var h1;
var i1;
var j1;
var m1;
var o1;
var p1;
var an1;
var ao1;
var x;
var range2 = sheet2.getRange(1,1,sheet2.getLastRow(),sheet2.getLastColumn()).getValues(); 
var output2 = [];
var a2;
var b2;
var c2;
var d2;
var e2;
var f2;
var g2;
var h2;
var y;
/// can i do for(x in range1; y in range2) { all in one function??  If so, what is the proper syntax?
for(x in range1, y in range2) {
a1 = range1[x][0];
b1 = range1[x][1];
h1 = range1[x][7];
i1 = range1[x][8];
j1 = range1[x][9];
m1 = range1[x][12];
o1 = range1[x][14];
p1 = range1[x][15];
an1 = range1[x][39];
ao1 = range1[x][40];
a2 = range2[y][0];
b2 = range2[y][1];
c2 = range2[y][2];
d2 = range2[y][3];
e2 = range2[y][4];
f2 = range2[y][5];
g2 = range2[y][6];
h2 = range2[y][7];
if (
(b1 != b2) ||
(m1 != e2) // etc etc etc
)
{
//push to output
}}

Answer 1

虽然您的语法for(x in range1, y in range2)不会返回错误，但如果行的顺序不同，它也不会为您提供所需的结果

原因：

在每次迭代期间，x和y都会发生变化，例如，如果var range1 = [1,2,3]和var range2 = [4,5,6]，您的循环将迭代 3 次，示例循环迭代中的值将为：

1. 迭 代

range1[x] = 1和range2[y] = 4

2. 迭代

range1[x] = 2和range2[y] = 5

3. 迭代

range1[x] = 3和range2[y] = 6

在这种情况下，您将不会检索组合

range1[x] = 1和range2[y] = 4

或

range1[x] = 2和range2[y] = 6

等等。

相反，您需要使用两个嵌套的 for 循环，这将遍历x和y的所有可能组合：

for(x in range1) {
for(y in range2){
...
} 
}

旁注：

即使您的行顺序相同，您仍然需要小心。因为for(x in range1)与for(x = 0; x < range1.length; i++)相反，您无法控制循环将在哪个文件夹中遍历范围。

现在到重复项查询

以不太复杂的方式实现该功能的可能方法如下：

• 定义布尔变量并使用它来检查每个x是否有重复
项
• 如果发现重复项(对于 B 列( - 将评估进一步的标准
• 如果所有条件都匹配两行，则内部循环将以break退出，函数将跳转到下一个x
• 如果发现具有相同键 ID 但其他列中存在差异的行 - 两行都将被推送到工作表output中以进行比较(这比指定确切的差异更容易实现(
• 在此之后，内部循环也将退出
• 在上述情况下，duplicate将设置为true
• 如果在工作表 1 中找到唯一的 Id(重复 = 假( - 它将立即被推入output

样本

function compare() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
ss.insertSheet(1);
ss.getActiveSheet().setName('output');
var sheet1 = ss.getSheetByName('sheet1');
var sheet2 = ss.getSheetByName('sheet2');
var sheet_output = ss.getSheetByName('output');

var range1 = sheet1.getRange(1,1,sheet1.getLastRow(),sheet1.getLastColumn()).getValues(); 
var output1 = [];
var b1;
var m1;
var p1;
var an1;
var x;
var range2 = sheet2.getRange(1,1,sheet2.getLastRow(),sheet2.getLastColumn()).getValues(); 
var output2 = [];
var b2;
var e2;
var f2;
var g2;
var y;
var array = [];
for(x in range1) {
var duplicate = false;
for(y in range2){
b1 = range1[x][1];
m1 = range1[x][12];
p1 = range1[x][15];
an1 = range1[x][39];    
b2 = range2[y][1];
e2 = range2[y][4];
f2 = range2[y][5];
g2 = range2[y][6];
if (        
(b1 == b2)
)
{
Logger.log("found");
duplicate = true;
if((m1 != e2)||
(p1 != f2) ||
(an1 != g2)){
array.push(range1[x]);
array.push(range2[y]);
}
break;          
}
} 
if (duplicate == false){
Logger.log("duplicate false");
array.push(range1[x]);
}
}
//push to output
if(array[0]){
sheet_output.getRange(sheet_output.getLastRow()+1, 1, array.length, array[0].length).setValues(array);
}
}