How to copy and paste values (non-adjacent columns, preserving values in target range)

So, do you want to keep all the values in column C of the target sheet intact?

It can be done pretty easy with the three additional lines in the function appendSheet1ToSheet2():

function appendSheet1ToSheet2() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const source = ss.getRange('Sheet1!A1:C');
  const target = ss.getRange('Sheet2!A1:D');
  const col_c = ss.getRange('Sheet2!C1:C');  // <--- get the column
  const col_c_values = col_c.getValues();    // <--- get the old values
  appendUniquesToRange_(source, target);
  col_c.setValues(col_c_values);             // <--- put the old values back
}

But I'm not sure if it makes sense. Because it's hard to tell what the columns A, B and D will contain after append the new data from the source sheet.