Automatically Copy cells from Sheet2 to Custom column on Sheet1 with rule

Question

Looking for help with adapting the following script (or show your version?) to copy cell(s) from Column B Sheet2 (where D contains "NOT_ON_WORKSHEET") (onEdit or not) to the last row of the 4th Column(D) of Sheet1 instead of current 1st Column(A). Tried to use setValue, but can't apply it without errors.

function onEdit(e) {
var ss = e.source,
    sheet = ss.getActiveSheet();
if (sheet.getName() !== 'Sheet2' || e.range.columnStart !== 4 || e.value !== 'NOT_ON_WORKSHEET') return;
e.source.getSheetByName('Sheet1')
  .appendRow(e.range.offset(0, -2, 1, 1)
  .getValues()[0]);
}

Visual explanation

SHEET 1:
    Column A | Column(s)…  | Column D
    ...      | ...         | #Name#
    =================================
    ...      |             | Qwe
             | ...         | Asd
    ...      | ...         | Zxc 
             |             | <"little bug" must be copied here (Not row, only one cell B)>
       
SHEET 2:
    Column A | Column B    | Column D
    ...      | #Name#      | #Is on Sheet1?#
    =================================
             | Qwe         | 
    ...      | Asd         |
             | Zxc         |
    ...      | little bug  | NOT_ON_WORKSHEET

Current script copies cells from B(Sheet2) only to Column A instead of D(Sheet1)(for ogirinal tnx JPV)

Answer 1

Okay I've finally made it. It's working well with topic's problem.

function onEdit(e) {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getActiveSheet();
 if (sheet.getName() !== '2' || e.range.getColumn() !== 4 || e.value !== 'Yes') return;
  var value = e.range.offset(0,-2,1,1).getValue();
  sheet = e.source.getSheetByName('1');
  var grd = sheet.getRange("D:D").getValues();
  var maxIndex = grd.reduce(function(maxIndex, row, index) {
  return row[0] === "" ? maxIndex : index;
  }, 0);
  sheet.getRange(maxIndex+2,e.range.getColumn()-2,1,1).setValue(value);
}