need help matching data (tab vs master) in google apps script

Question

I need help to know how to match -with Google Apps Script-, data in Master Spreadsheet Sheet1 (key is columns A and B) with data in Spreadsheet Sheet2 (key is columns A and B).

Sheet1 (Master): key = A, B
___________________________
A        B          C
Process1 Risk1      rest1
Process2 Risk2      rest2
Process3 Risk3      rest3

Sheet2         : key = A, B
___________________________
A        B          C
Process1 Risk1      rest11
Process2 Risk22     rest22
Process3 Risk3      rest33
Process4 Risk44     rest44

Expected result: adding new column (D) telling if key in Sheet2 exists or not in master Sheet1 (matching criteria both columns A and B exists in master Sheet1).

Sheet2         
_______________________________________
A        B          C      D
Process1 Risk1      rest11 in master
ProcessA Risk2      rest22 no in master
Process3 Risk3      rest33 in master
Process4 RiskB      rest44 no in master

Answer 1

Explanation:

I used regular for loops and if conditions to illustrate the logic easier.

Iterate over both arrays (data A-C columns in Sheet1 and Sheet2 until the last row with content) and:

• if there is a match, stop searching and assign in master,

• if there is no match, assign no in master.

Code snippet:

function myFunction() {
  const ss = SpreadsheetApp.getActive();
  const sh1 = ss.getSheetByName('Sheet1');
  const sh2 = ss.getSheetByName('Sheet2');
  const vals1 = sh1.getRange('A1:C' + sh1.getLastRow()).getValues();
  const vals2 = sh2.getRange('A1:C' + sh2.getLastRow()).getValues();
  const result = [];
  let res;
  for (let i = 0 ; i < vals2.length ; i++){
    for (let j = 0 ; j < vals1.length ; j++){
      if(vals2[i][0]==vals1[j][0] && vals2[i][1]==vals1[j][1]){
         res = "in master";
         break;
      }
      else{
         res = "no in master";
      }
    }
    result.push([res]);
  }
  sh2.getRange(1,4,result.length,1).setValues(result);
}

Sheets used for the code snippet:

Sheet1:

Sheet2: