时间戳

首页 > 解决方案 > 查询 2 组表格数据并找出差异

google-apps-script - 查询 2 组表格数据并找出差异

问题描述

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

a)识别缺失的 ID 值；

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

输入：
带有 2 个选项卡（tab1、tab2）的电子表格。
每个 B 列中的键 ID (Btab1, Btab2)

输出：显示数据集中问题区域的选项卡。
第一列是 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
  }}

标签： google-apps-script

解决方案

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

原因：

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

  1. 迭代

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

  1. 迭代

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

  1. 迭代

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
  • 如果在 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);
  }
}

推荐阅读