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:
CodePudding user response:
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`.