How to match and import data from other sheet by using apps script?-CodePudding

I just want to match and import the matching data from sheet1 to sheet2 with correct cell.
for example, this is sheet1

and this is sheet2

How to insert the matching data automatically by using Apps Script??
Cuz, I want to update it auto continually.
This is the result that I wanna get.

CodePudding user response：

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].

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.

Sample script:

References: