CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Victoria
Victoria

Reputation: 15

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

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

and this is sheet2
enter image description here

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.
enter image description here

Upvotes: 0

Views: 266

Answers (1)

Tanaike
Tanaike

Reputation: 201388

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.

References:

Upvotes: 2

Related Questions