比较工作表1和工作表2，并输出到工作表3.Google工作表.的JavaScript [英] Compare sheet 1 to sheet 2 and output to sheet 3. Google sheets. JavaScript

问题描述

以下代码可以成功地将工作表2的A列与工作表1的B列进行比较，任何匹配项都会将整个行复制到工作表3.但是，我需要对该代码进行非常细微的更改以比较工作表2的A列到工作表1的N列而不是B列.有人可以帮助我更改此代码吗? 这是上一篇文章的链接针对Google Apps脚本的Java脚本优化

The following code works successfully to compare column A of sheet 2 with Column B of sheet 1, any matches will copy the entire row to sheet 3. However im needing a very slight change of this code that compares column A of sheet 2 to column N of sheet 1 instead of column B. Could someone help me with this code change? Here is the link to the previous post Java script optimization for a google apps script

 function copyRowtoSheet3() {
  var spreadsheetId = "1Aw11LiKzyezfrTQIuTsJPhUFtz8RPqLCc8FlIiy0ZlE";
  var ss = SpreadsheetApp.openById(spreadsheetId);
  var s1 = ss.getSheetByName('Sheet1');
  var s2 = ss.getSheetByName('Sheet2');
  // 1. Retrieve values from "Sheet1" and "Sheet2",
  var values1 = s1.getDataRange().getValues();
  var values2 = s2.getRange(1, 1, s2.getLastRow(), 1).getValues();

  // 2. Create an object using values2.
  var obj = values2.reduce((o, [e]) => {
    o[e] = null;
    return o;
  }, {});

  // 3. Create resultArray using values1 and obj.
  var resultArray = values1.filter(([,b]) => b in obj);

  // 4. Put resultArray to Sheet3.
  Sheets.Spreadsheets.Values.update({values: resultArray}, spreadsheetId, "Sheet3", {valueInputOption: "USER_ENTERED"});
}

我尝试的是:

var resultArray = values1.filter(([,n]) => n in obj);

但是那没有用.有什么想法吗?

However that did not work. Any ideas?

推荐答案

• 您要检索"Sheet1"和"Sheet2"的值.
• 您要比较"Sheet1"的"N"列和"Sheet2"的"A"列.当"Sheet1"的"N"列和"Sheet2"的"A"列的值相同时，您要检索"Sheet1"的行并放入"Sheet3".
• 您想通过修改脚本来实现.

如果我的理解是正确的，那么这个答案如何?请认为这只是几个可能的答案之一.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

• var resultArray = values1.filter(([,b]) => b in obj);被修改.在当前脚本中，将比较[,b]中的"B"列.
• var resultArray = values1.filter(([,b]) => b in obj); is modified. In your current script, the column "B" from [,b] is compared.

修改脚本后，请进行以下修改.

When your script is modified, please modify as follows.

var resultArray = values1.filter(([,b]) => b in obj);

到:

var resultArray = values1.filter(b => b[13] in obj);

如果我误解了您的问题，而这不是您想要的结果，我深表歉意.

If I misunderstood your question and this was not the result you want, I apologize.

这篇关于比较工作表1和工作表2，并输出到工作表3.Google工作表.的JavaScript的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！