Google Sheets - Compare two sheet columns and append the unmatched values on the first column

Question

In one Google sheet workbook, lets say I have Sheet1 with 5 rows as
Sheet1

And I have Sheet2 as Sheet2

I am looking for something that will take the non-matching rows (rows which are in Sheet2 but not in Sheet1) and append those cells below the cells in Sheet1.

Expected result in Sheet1: Expected Result

Answer 1

In your situation, how about the following sample script?

Sample script:

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const [sheet1, sheet2] = ["Sheet1", "Sheet2"].map(s => ss.getSheetByName(s));
  const lastRowSheet1 = sheet1.getLastRow();
  const sheet1Obj = sheet1.getRange("A2:B" + lastRowSheet1).getValues().reduce((o, [a]) => (o[a] = true, o), {});
  const sheet2Values = sheet2.getRange("A1:A" + sheet2.getLastRow()).getValues();
  const values = sheet2Values.filter(([a]) => !sheet1Obj[a]);
  if (values.length == 0) return;
  sheet1.getRange(lastRowSheet1 + 1, 1, values.length, 1).setValues(values);
}

• When this script is run, the values of "Sheet1" and "Sheet2" are checked. When the values of column "A" of "Sheet2" are not included in the values of column "A" of "Sheet1", those values are appended to column "A" of "Sheet1". I thought that this is the goal you expect.

Note:

• If you change the sheet name, please modify ["Sheet1", "Sheet2"].

References:

• getValues()
• setValues(values)
• reduce()