查询2组工作表数据&识别差异

Question

我试图使用Apps脚本来查询2个数据集，并比较它们之间的某些列。我希望..。

a)识别缺少的ID值；

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

投入：

带有两个选项卡的电子表格(tab1，tab2)。

B列中的键ID (Btab1、Btab2)

columns...

• For

• 运行一个函数&如果Btab1不在Btab1中，则在Btab1不在tab2的情况下推送到输出选项卡中(当B的值在两个选项卡中时)(大多数情况下)，我希望识别几个Btab1中数据差异的实例--所有B、push B和下面的相关列都在输出选项卡中，如果.

- Column M in tab1 doesn't match column E in tab2
- Column P in tab1 <> column F in tab2
- Column AN tab1 <> Column G tab2

​

输出:在数据集中显示问题区域的选项卡。

第一列是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. iteration

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

1. iteration

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

1. iteration

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){
      ...
    } 
  }

Sidenote:

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

现在开始查询重复的。

以一种不太复杂的方式实现该功能的一种可能方法是：

x

• If

• 定义布尔变量，并使用它检查每个x是否有重复的

• (对于B列)--如果两行与所有条件匹配，则进一步的条件将被计算为

• ，内环将与break一起退出，函数将跳到具有相同密钥If的下一个x行，但是发现了其他列中的差异--为了比较目的，两行都将被推入工作表output (这比指定什么是true

• If更容易实现--内环也将退出，在duplicate以上的情况下，

• 将被设置为true

• If，在sheet1中找到唯一的Id (重复= false) --它将立即被推入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);
  }
}