Replace data in google sheet based on a mapping table

Question

Sheet1 is the sheet that's changing-

Sheet2 is directing the change

I am looking to replace the data in column A of Sheet1 with the Column B of Sheet2. Basically similar to VLOOKUP however, if I use VLOOKUP I will need to create additional column to use that formula, which is not the goal.

Expected Result Sheet1:

Answer 1

In your situation, how about the following sample script?

Sample script:

Please set the sheet names for sheet1 and sheet2. In this case, sheet1 and sheet2 are Sheet1 is the sheet that's changing- and Sheet2 is directing the change in your question, respectively.

function myFunction() {
  const sheet1 = "Sheet1"; // Please set sheet name.
  const sheet2 = "Sheet2"; // Please set sheet name.

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const [s1, s2] = [sheet1, sheet2].map(s => ss.getSheetByName(s));
  const s1Range = s1.getRange("A1:A" + s1.getLastRow());
  const s2Obj = s2.getRange("A1:B" + s2.getLastRow()).getValues().reduce((o, [a, b]) => (o[a] = b, o), {});
  const res = s1Range.getValues().map(([a]) => [s2Obj[a] || a]);
  s1Range.setValues(res);
}

• When this script is run, the column "A" of sheet1" is modified using the values of columns "A" and "B" of sheet2`.

References:

• reduce()
• map()