Google脚本检查一个工作表中某一列中的数据是否与另一工作表中另一列中的数据相同 [英] Google Script checking that the data within one column on one sheet is the same as another column in another sheet

问题描述

我正在尝试创建一个Google脚本，以检查一张纸上某一列中的数据是否与另一张纸中另一列中的数据相同，我有什么想法吗?

I am trying to create a Google Script to check that the data within one column on one sheet is the same as another column in another sheet, any ideas where I am going wrong?

谢谢:)下面的代码:

function myFunction() {
  //data from Salesworks
  var sheet = SpreadsheetApp.getActive().getSheetByName("Sales Works TEST");
  var data_salesworks = sheet.getRange('D3:D121').getValues();

  //data from Google Form
  var sheet = SpreadsheetApp.getActive().getSheetByName("V3 New Form Responses");
  var data_sheet = sheet.getRange('N3:N121').getValues();


  if (data_salesworks = data_sheet){
    Browser.msgBox('Date Range Correct');
  }
    else{
      Browser.msgBox('Date Range Incorrect');
}
}

推荐答案

使用 getValues()检索值时，该值为二维数组.不幸的是，这样的数组不能直接比较.因此，需要修改比较部分.对于这种情况，我想提出以下两种模式.

When the value is retrieved using getValues(), the value is 2 dimensional array. Unfortunately, such arrays cannot be directly compared. So it requires to modify the comparison part. I would like to propose following 2 patterns for this situation.

在这种模式下，每个数组都将转换为字符串并进行比较.

In this pattern, each array is converted to string and compared them.

function myFunction() {
  //data from Salesworks
  var sheet = SpreadsheetApp.getActive().getSheetByName("Sales Works TEST");
  var data_salesworks = sheet.getRange('D3:D121').getValues();
  //data from Google Form
  var sheet = SpreadsheetApp.getActive().getSheetByName("V3 New Form Responses");
  var data_sheet = sheet.getRange('N3:N121').getValues();
  if (data_salesworks.toString() == data_sheet.toString()) {
    Browser.msgBox('Date Range Correct');
  } else {
    Browser.msgBox('Date Range Incorrect');
  }
}

模式2:

在此模式下，使用"for循环"比较每个数组中的所有元素.

Pattern 2 :

In this pattern, all elements in each array are compared using "for loop".

function myFunction() {
  //data from Salesworks
  var sheet = SpreadsheetApp.getActive().getSheetByName("Sales Works TEST");
  var data_salesworks = sheet.getRange('D3:D121').getValues();
  //data from Google Form
  var sheet = SpreadsheetApp.getActive().getSheetByName("V3 New Form Responses");
  var data_sheet = sheet.getRange('N3:N121').getValues();
  var f = true;
  for (var i=0; i<data_salesworks.length; i++) {
    for (var j=0; j<data_salesworks[i].length; j++) {
      if (data_salesworks[i][j] != data_sheet[i][j]) {
        f = false;
      }
    }
  }
  if (f) {
    Browser.msgBox('Date Range Correct');
  } else {
    Browser.msgBox('Date Range Incorrect');
  }
}

注意:

这些样品必须满足以下条件.

Note :

These samples are required to satisfy the following conditions.

• 两个比较数据的行数相同.
• 两个比较数据的列数相同.

• getValues()

如果我对您的问题的理解是错误的，请告诉我.我想修改它.

If my understanding for your question is wrong, please tell me. I would like to modify it.

这篇关于Google脚本检查一个工作表中某一列中的数据是否与另一工作表中另一列中的数据相同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！