Merge and Update Google Sheets using Google App Script

Question

I update my question so it will more understandable :

• "Sheet1" will get row data from "Sheet2" if value of column A in "Sheet1" is not found in "Sheet2" and will not get row data if there is duplicate value found in column A "Sheet1" (DONE)

• Sheet1" will update row data from "Sheet2" if value of column A in "Sheet2" is duplicate value found in column A "Sheet1" (help me with this)

• "Sheet1" will update value column B from oldValues into "OK" if
  value of column A in "Sheet1" is not found in "Sheet2" and will not
  get row data if there is duplicate value found in column A "Sheet1"
  (help me with this)

I already try find the logic here, and to be honest I already lost my logic. Is there someone could help me with this?

Thank you so much. Help would be appreciate.

Here is my code :

function mergeUpdate() {
  var ss = SpreadsheetApp.getActive();
  var data = ss.getSheetByName("Sheet2").getDataRange().getValues();
  var addData = ss.getSheetByName("Sheet1");
  var saveData = addData.getDataRange().getValues();
  function duplicates(i) { //checking duplicate loop
    var value = data[i][0]; // data in column must be same 
    var statusValue = data[i][1];
    for(var j in saveData) {
      if(isNaN(data[i][0]) || data[i][0] === '') {
        return true;
      } else if (saveData[j][0] === value) {
        return true;
      }
    }
    return false;
  }
  //Logger.log(data);
  for (i in data) {
    if(!duplicates(i)) { addData.appendRow(data[i]);
    } else {
      var toEdit = addData.getRange(duplicates(i), 2, 1, data[i].length).getValues().toString();
      Logger.log(toEdit);
      toEdit.setValues(data[i][1]);
    }
  }
}

here is link to sample spreadsheet

Answer 1

function mergeTwoSheets() {
  const ss=SpreadsheetApp.getActive();
  const sh1=ss.getSheetByName('Sheet1');
  const sh2=ss.getSheetByName('Sheet2');
  const sh2sr=2;//sheet2 start row
  const vs2=sh.getRange(sh2sr,1,sh2.getLastRow()-sh2sr+1,sh2.getLastColumn()).getValues();
  sh1.getRange(sh1.getLastRow()+1,1,vs2.length,vs2[0].length).setValues(vs2);
}