How to use onEdit to copy the data to another google sheet file and overwrite the data if the ID is the same?

Hi everyone,

I want to copy the data from source sheet to destination sheet. When the data reached the destination sheet, the script able to loop through row 2 in destination sheet to see whether any same ID already existed. If the ID already existed in row 2, then it will overwrite the data in the column, if not, the script will find the last empty column based on row 2 and paste the data there.

So in the screenshot above, since there is no 1004 in destination sheet, then it will paste the data in column E.

This is my code:

function onEdit(e){
  var ss = SpreadsheetApp.getActiveSheet ();
  var targetfile = SpreadsheetApp.openById("11tpC8SNZ5XB35n7GON0St3ZQ37dIbM8UbXRjmkVAeJQ");
  var target_sheet = targetfile.getSheetByName("Sheet1");
  var target_range = target_sheet.getRange(3, ss.getLastColumn() + 1);
  
  if (e.range.columnStart == 3 && e.range.rowStart == 16){
    if (e.value == 'Submit'){
      var source_range = ss.getRange("C4:C14")
      source_range.copyTo(target_range);
      e.range.clearContent()
    } 
  } 
}

My current problems are:

• The script is not working when I triggered it in cell C16 (couldn't find the reason)
• I'm not sure how to add the checking for ID in destination sheet into my script.

This are my google files

Source sheet: https://docs.google.com/spreadsheets/d/12kKQKT2XSdPkJ46LSV9OiI167NKBdwKkpkOtOf_r_jI/edit#gid=0

Destination sheet: https://docs.google.com/spreadsheets/d/11tpC8SNZ5XB35n7GON0St3ZQ37dIbM8UbXRjmkVAeJQ/edit#gid=0

Hope to get some advice and help from expert. Any help will be greatly appreciated!