I believe your goal is as follows.
- You want to convert from the upper 2 images in your question to the bottom image in your question.
- You want to achieve this using Google Apps Script.
In this case, how about the following sample script?
### Sample script:
In this sample script, the value of columns "B" is the key for searching values. Using this key, the array is created using the retrieved values from column "B" of Sheet2. And, the created array is put to Sheet2.
```javascript
function myFunction() {
// 1. Retrieve sheets.
const ss = SpreadsheetApp.getActiveSpreadsheet();
const [sheet1, sheet2] = ["Sheet1", "Sheet2"].map(s => ss.getSheetByName(s));
// 2. Retrieve values from Sheet1 and create an object for searching values.
const obj = sheet1.getRange("A2:D" + sheet1.getLastRow()).getValues().reduce((o, r) => Object.assign(o, {[r[1]]: r}), {});
// 3. Retrieve values from Sheet2 and create an array for putting to Sheet2.
const values = sheet2.getRange("A2:D" + sheet2.getLastRow()).getValues().flatMap(r => [obj[r[1]] || r]);
// 4. Put the array to Sheet2.
sheet2.getRange(2, 1, values.length, values[0].length).setValues(values);
}
```
- When this script is run, the values are retrieved from Sheet1 and put to the new values to the Sheet2 by searching the values from Sheet1 and Sheet2. By this, your goal can be achieved.
### References:
- [reduce()](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/reduce)
- [map()](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/map)
I believe your goal is as follows.
- You want to convert from upper 2 images in your question to the bottom image in your question.
- You want to achieve this using Google Apps Script.
In this case, how about the following sample script?
### Sample script:
In this sample script, the values of columns "A" to "C" are the key for searching values. For example, when you want to use the key of column "A", please modify `o[a + b + c]` to `o[a]`.
```javascript
function myFunction() {
// 1. Retrieve sheets.
const ss = SpreadsheetApp.getActiveSpreadsheet();
const [sheet1, sheet2] = ["Sheet1", "Sheet2"].map(s => ss.getSheetByName(s));
// 2. Retrieve values from Sheet1 and create an object for searching values.
const obj = sheet1.getRange("A2:D" + sheet1.getLastRow()).getValues().reduce((o, [a,b,c,d]) => (o[a + b + c] = d, o), {});
// 3. Retrieve values from Sheet2 and create an array for putting to the column "D" of Sheet2.
const values = sheet2.getRange("A2:C" + sheet2.getLastRow()).getValues().map(([a,b,c]) => [obj[a + b + c] || ""]);
// 4. Put the array to the column "D" of Sheet2.
sheet2.getRange(2, 4, values.length, 1).setValues(values);
}
```
- When this script is run, the values are retrieved from Sheet1 and put to the values to the column "D" of Sheet2 by searching the values from Sheet1 and Sheet2. By this, your goal can be achieved.
### References:
- [reduce()](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/reduce)
- [map()](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/map)
