CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-sheets-api
Garrett Kidd
Garrett Kidd

Reputation: 49

Compare sheet 1 to sheet 2 and output to sheet 3. Google sheets. JavaScript

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"});
}

What I have attempted is: 

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

However that did not work. Any ideas?

Upvotes: 0

Views: 143

Answers (1)

Tanaike
Tanaike

Reputation: 201503

  • You want to retrieve the values of "Sheet1" and "Sheet2".
  • You want to compare the column "N" of "Sheet1" and the column "A" of "Sheet2". When the values of the column "N" of "Sheet1" and the column "A" of "Sheet2" are the same, you want to retrieve the row of "Sheet1" and put to "Sheet3".
  • You want to achieve this by modifying your script.

Modification point:

  • var resultArray = values1.filter(([,b]) => b in obj); is modified. In your current script, the column "B" from [,b] is compared.

Modified script:

When your script is modified, please modify as follows.

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

Upvotes: 1

Related Questions