比较2个不同的Google表格中的4个不同的列 [英] Compare 4 different columns in 2 different Google Sheets
问题描述
我正在尝试比较2个Google表格中的数据.每个工作表都有一列作为标识符(sheet1:H和sheet2:C)，如果它们匹配，那么我想将sheet1:I更改为sheet2:E中的值.我正在运行此代码，但没有任何错误.虽然没有用.
I am trying to compare the data from 2 google sheets. Each sheet has a column that is the identifier (sheet1:H and sheet2:C), if these match then I want to change sheet1:I to the value in sheet2:E. I'm running this code, but get no errors. It's not working though.
我试图看到类似的帖子来解决这个问题，但是它们似乎都缺少比较我正在使用的不同列方法的方法.
I tried to see similar posts this issue but they all seem to be lacking the compare a different column method I am using.
function changestatus() {
// gets spreadsheet A and the range of data
ssA = SpreadsheetApp.openById('IDHERE');
sheetA = ssA.getSheetByName('Sheet1');
dataA = sheetA.getRange('H2:H').getValues();
dataD = sheetA.getRange('I2:I').getValues();
// gets spreadsheet B and the range of data
ssB = SpreadsheetApp.openById('IDHERE');
sheetB = ssB.getSheetByName('responses');
dataB = sheetB.getRange('C2:C').getValues();
dataC = sheetB.getRange('E2:E').getValues();
for (var i = 0; i > sheetA.getLastRow(); i++) {
if (dataA[1][i] == dataB[1][i] && dataC[1][i] != dataD[1][i]){
var value = sheetA.getRange(i+1, 2).getValue(dataD);
sheetB.getRange(i+1, 2).setValue(value);
} // end if
} // end i
工作表文件的起始结果如下:
Starting results of sheets files would be something like:
第1张
H:(ID)1 I :(等级)通过
Sheet 1
H:(ID) 1 I:(grade) pass
表2
C:(ID)1 E :(等级)失败
Sheet 2
C:(ID) 1 E:(grade) fail
后功能:
第1张
H:(ID)1 I :(等级)失败
Sheet 1
H:(ID) 1 I:(grade) fail
推荐答案
@tehhowch是正确的；您需要查看JavaScript比较运算符，循环语法，Range#getValues返回的对象格式以及如何访问JavaScript数组索引.所有这些都会导致您的代码问题，但是我们可以在此过程中为您提供更多帮助是很合理的.
@tehhowch is quite right; you need to review JavaScript comparison operators, for loop syntax, the format of object returned by Range#getValues, and how to access JavaScript array indices. Each of these contributes to your code problems, but it's reasonable that that we help you along the road a little more.
循环语法
这是个简单的.代替"i> sheetA.getLastRow()"，它应显示为i < sheetA.getLastRow(). i从零开始，在每个循环结束时其值增加一；因此，您希望循环处理所有小于最后一行值的i值.
Loop syntax
This is an easy one. Instead of "i > sheetA.getLastRow()", it should read i < sheetA.getLastRow(). i starts with a value of zero, and its value increases by one at the end of each loop; so you want the loop to process all the values of i that are less than the value of the last row.
数组值
getValues返回一个二维数组，但是IF语句失败，因为数组值是从前到后的.
Array values
getValues returns a two-dimensional array but the IF statement fails because the array values are back to front.
例如，它应该是dataA[i][0]，而不是"dataA [1] [i]".这里有两个更改:
1-"i"移到数组值的前半部分(行"值)；和
2-数组值的后半部分为[0](不是"[1]").这是因为每个变量只有一列宽.例如，dataA仅返回列H的值； dataB，dataC和dataD同样如此-它们都只返回一列的值.
For example, instead of "dataA[1][i]", it should be dataA[i][0]. There are two changes here:
1 - "i" moves to the first half of the array value (the 'row' value); and
2 - the second half of the array value is [0] (not "[1]"). This is because each variable is only one column wide. For example, dataA only returns the value of column H; same is true for dataB, dataC and dataD - they all return the value of just one column.
问题排查
您如何判断IF语句是否有问题?它看起来"确定.一种方法是显示(或记录)要返回的值.
我使用Logger.log()(还有其他选项)在脚本编辑器中的查看，日志"下显示信息.每次运行脚本时，"Logger"语句都会更新，您可以检查其值.
Troubleshooting
How could you tell whether the IF statement was a problem? It "looks" OK. One way is to display (or log) the values being returned.
I use Logger.log() (there are other options) to display information in the script editor under "View, Logs". Each time the script is run, the "Logger" statements are updated and you can check their value.
例如，您可以在第13行(循环之前)插入此代码，以检查数据变量的某些值.
Logger.log("dataA[1][0] = "+dataA[1][0]);
该行将显示:"dataA [1] [0] = 2".这是一个有效的结果，但是您可能会注意到它报告的ID = 2，但是，例如，您期望的是ID = 1的结果.
For example, you could insert this code at line 13 (before the loop) to check some values of the data variables.
Logger.log("dataA[1][0] = "+dataA[1][0]);
That line will show: "dataA[1][0] = 2". That's a valid result but you might notice that it is reporting ID=2 but, say, you were expecting a result of ID=1.
因此将行更改为:
Logger.log("dataA[1][1] = "+dataA[1][1]);
此行显示"dataA [1] [1] =未定义".好，肯定有问题.
So change the line to:
Logger.log("dataA[1][1] = "+dataA[1][1]);
This line shows "dataA[1][1] = undefined". OK, something definitely wrong.
因此，让我们尝试:
Logger.log("dataA[0][0] = "+dataA[0][0]);
该行显示"dataA [0] [0] = 1".现在更像它了.
So, let's try:
Logger.log("dataA[0][0] = "+dataA[0][0]);
This line shows "dataA[0][0] = 1". Now that's more like it.
您可以使Logger变长或变短；例如，您可能需要一行评估变量的结果.因此，记录器可能看起来像这样:
Logger.log("dataA[0][0] = "+dataA[0][0]+", dataB[0][0] = "+dataB[0][0]+", dataC[0][0] = "+dataC[0][0]+", dataD[0][0] = "+dataD[0][0]);
它会返回:
数据A [0] [0] = 1，数据B [0] [0] = 1，数据C [0] [0] =失败，数据D [0] [0] =通过".
这可能确认您走在正确的轨道上，或者您需要进一步调试
You can make Logger long or short; for example, you might want to evaluate the results of of the variables in one line. So the Logger might look like this:
Logger.log("dataA[0][0] = "+dataA[0][0]+", dataB[0][0] = "+dataB[0][0]+", dataC[0][0] = "+dataC[0][0]+", dataD[0][0] = "+dataD[0][0]);
And it would return:
"dataA[0][0] = 1, dataB[0][0] = 1, dataC[0][0] = Fail, dataD[0][0] = Pass".
This might confirm that you are on the right track, or that you need to debug further
失败的IF语句
原始行=(dataA [1] [i] == dataB [1] [i]& dataC [1] [i]！= dataD [1] [i])"
更正的行= (dataA[i][0] == dataB[i][0] && dataC[i][0] != dataD[i][0])
The Failing IF statement
Original line = "(dataA[1][i] == dataB[1][i] && dataC[1][i] != dataD[1][i])"
Corrected line = (dataA[i][0] == dataB[i][0] && dataC[i][0] != dataD[i][0])
更新工作表1上的结果
这里的代码是:
var value = sheetA.getRange(i+1, 2).getValue(dataD);
sheetB.getRange(i+1, 2).setValue(value);
Updating the results on Sheet 1
The code here is:
var value = sheetA.getRange(i+1, 2).getValue(dataD);
sheetB.getRange(i+1, 2).setValue(value);
这令人困惑，并使两件事变得复杂.
1-值只需要是"sheet2:E中的值-这在IF语句中:dataC[i][0].所以value = dataC[i][0]
2-目标是将sheet1:I更改为sheet2:E中的值".您已经拥有了价值，因此现在将重点放在sheet1:I上.
有时，定义范围更简单，然后在第二行中更新该范围的值.
This is confusing and complicates a couple of things.
1 - the value just needs to be "the value in sheet2:E - this was in the IF statement: dataC[i][0]. So value = dataC[i][0]
2 - The goal is "change sheet1:I to the value in sheet2:E". You've already got the value, so focus now on sheet1:I.
Some times it is more simple to define the range and then, on a second line, update the value for that range.
- 目标工作表为sheetA；
- 目标行是:i + 1(正确)；
- 目标列是:I(或第9列).
所以var range = sheetA.getRange(i+2, 9);
您可以使用记录器"进行检查:
Logger.log("range = "+range.getA1Notation());可能返回范围= I2".
So, var range = sheetA.getRange(i+2, 9);
You could check this with "Logger":
Logger.log("range = "+range.getA1Notation()); might return "range = I2".
然后更新值:
range.setValue(value);
有意义的变量名
帮助(很多)使用有意义的变量名.例如，原始代码使用:
"dataA" = Sheet1，H列(包含ID)；因此可能是"data1_H"甚至是"targetID".
"dataD" = Sheet1，第I栏(包含等级)；因此可能是"data1_I"或targetGrade.
"dataB" = Sheet2，C列(包含ID)，因此可能是"data2_C"或sourceID.
"dataC" = Sheet2，E列(包含等级)；因此，可能是"data2_E"或sourceGrade.
Meaningful variable names
It helps (a LOT) to use meaningful variable names. For example, the original code uses:
"dataA" = Sheet1, Column H (contains ID); so maybe this could be "data1_H" or even "targetID.
"dataD" = Sheet1, Column I (contains grade); so maybe this could be "data1_I" or targetGrade.
"dataB" = Sheet2, Column C (contains ID), so maybe this could be "data2_C" or sourceID.
"dataC" = Sheet2, Column E (contains grade); so maybe this could be "data2_E" or sourceGrade.
更改摘要
function so_changestatus() {
// gets spreadsheet A and the range of data
ssA = SpreadsheetApp.openById('IDHERE');
sheetA = ssA.getSheetByName('Sheet1');
dataA = sheetA.getRange('H2:H').getValues();
dataD = sheetA.getRange('I2:I').getValues();
// gets spreadsheet B and the range of data
ssB = SpreadsheetApp.openById('IDHERE');
sheetB = ssB.getSheetByName('responses');
dataB = sheetB.getRange('C2:C').getValues();
dataC = sheetB.getRange('E2:E').getValues();
for (var i = 0; i < sheetA.getLastRow(); i++) {
if (dataA[i][0] == dataB[i][0] && dataC[i][0] != dataD[i][0]){
var value = dataC[i][0];
var range = sheetA.getRange(i+2, 9);
range.setValue(value);
} // end if
}
}
更新-2019年4月1日
SheetA与SheetB上的ID逐行不匹配
原始代码是根据ID在逐行匹配的基础上编写的.不是这种情况.因此，需要对代码进行更改，以测试SheetA上的ID是否存在于SheetB上，然后测试相应的状态.
UPDATE - 1 April 2019
ID on SheetA vs SheetB does NOT match row-by-row
The original code was written on the basis that the ID matched on a row-by-row basis. This is not the case. So a variation in the code is needed to test whether the ID on SheetA exists on SheetB, and then test the respective status.
通过[indexof] 文档参考.
在这段代码中，我还借此机会使数据范围的变量名更有意义.
In this code, I also took the opportunity to make the variable names of the data ranges more meaningful.
还请注意:当i小于最后一行减一"i<(lastrow-1);"时，循环继续.这是必需的，因为第一行是标题，并且数据范围从第2行开始，因此数据行的数量将为最后一行减一"(以允许出现标题行).
Note also: the loop continues while i is less than the lastrow minus one "i < (lastrow-1);". This is necessary because the first row are headers and the data range starts on row 2, so the number of data rows will be the "lastrow minus one" (to a allow for the header row).
function ejb2so_changestatus() {
// gets spreadsheet A and the range of data
// ssA = SpreadsheetApp.openById('IDHERE');
ssA = SpreadsheetApp.getActive();
sheetA = ssA.getSheetByName('Sheet1');
dataA_ID = sheetA.getRange('H2:H').getValues();
data_Status = sheetA.getRange('I2:I').getValues();
//Logger.log("DEBUG: H3 = "+dataA_ID[4][0]+", I3 = "+data_Status[4][0]);//DEBUG
// gets spreadsheet B and the range of data
//ssB = SpreadsheetApp.openById('IDHERE');
ssB = SpreadsheetApp.getActive();
sheetB = ssB.getSheetByName('Responses');
dataB_ID = sheetB.getRange('C2:C').getValues();
dataB_Status = sheetB.getRange('E2:E').getValues();
// Logger.log("DEBUG: C3 = "+dataB_ID[0][0]+", E3 = "+dataB_Status[0][0]);//DEBUG
var lastrow = sheetA.getLastRow()
// Logger.log("DEBUG: sheetA last row = "+lastrow);//DEBUG
// Flatten the array
var dataB_IDFlat = dataB_ID.map(function(row) {
return row[0];
});
//Loop through values on sheetA; check if they exist on sheetB
for (var i = 0; i < (lastrow - 1); i++) {
var A_ID = dataA_ID[i][0];
// Logger.log("DEBUG: id = "+A_ID);//DEBUG
// assign variable to return value index
var result = dataB_IDFlat.indexOf(A_ID);
if (result != -1) {
// it's there
// Logger.log("DEBUG: i: "+i+", ID: "+A_ID+", it's there"+", result#: "+result);//DEBUG
// Logger.log("DEBUG: Sheet1 status: "+data_Status[i][0]+" Vs Sheet2 status = "+dataB_Status[result][0]);//DEBUG
// compare status from sheetsA to sheetB
if (data_Status[i][0] != dataB_Status[result][0]) {
// Logger.log("DEBUG: status change to: "+dataB_Status[result][0]);//DEBUG
var range = sheetA.getRange(i + 2, 9);
//Logger.log("DEBUG: value = "+value);//DEBUG
//Logger.log("DEBUG: range = "+range.getA1Notation());//DEBUG
range.setValue(dataB_Status[result][0]);
}
} else {
// it's not there
// Logger.log("DEBUG: i: "+i+", ID: "+A_ID+", it's not there");//DEBUG
}
}
}
// Credit: Flatten array: https://stackoverflow.com/a/49354635/1330560
这篇关于比较2个不同的Google表格中的4个不同的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
