CODEWITHSUNDEEP
jquerygoogle-apps-scriptgoogle-sheets-formula
Guhlis
Guhlis

Reputation: 81

Sheets importrange matching data from multiple sheets

I have multiply taps in one sheet A, each tap has 5000+ rows, some rows have the same content and want to find specific rows, while entering content and copy those rows which are matching to a different sheet B and keep the formatting etc from sheet A.

Example: I need to make a shipping/order list I have 2 sheets Sheet A is full of information (my stockload), multiply tabs with a lot of rows in each tab. in Sheet B (new URL) I want to filter the information.

Lets say, I have 5000 articles spread in 5 tabs on Sheet A, someone is ordering 20 articles, so I just want to scan the EAN or any other code on that article to Sheet B, and all rows with that data, will be imported. Now I have a brand new list with only the 20 articles, which I can share to the client.

I have tried with script:

    function copyRowsWithCopyTo() {
  let spreadSheet = SpreadsheetApp.getActive();
  let sourceSheet = spreadSheet.getSheetByName('Sheet0');
  let sourceRange = sourceSheet.getDataRange();
  let targetSheet = spreadSheet.getSheetByName('Sheet1');
  sourceRange.copyTo(targetSheet.getRange(1, 1));
}

and also tryng with Importrange query, but I cant seem to figure out the keycode, for searching in all the tabs, and lookup/match the new text/code I always provide.

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1hAeUokEf-1DQjRYn2iQXCSCwAoRW-C083z5QYXpzq-Y", "1:1000"), "B2 ",0)

SheetA //// SheetB

Does anyone know what am I missing? Thank you

Upvotes: 2

Views: 299

Answers (1)

NightEye
NightEye

Reputation: 11184

You need to individually import the data per sheet and combine them into a single array, then put it in a query where you check the Col2 value if it equates to B2. See sample below:

EDIT:

  • Since you want to check multiple ID inputs for Col2 and Col3, then see the modification below:

Formula:

=QUERY({
IMPORTRANGE("https://docs.google.com/spreadsheets/d/1hAeUokEf-1DQjRYn2iQXCSCwAoRW-C083z5QYXpzq-Y", "Data1!A:P");
IMPORTRANGE("https://docs.google.com/spreadsheets/d/1hAeUokEf-1DQjRYn2iQXCSCwAoRW-C083z5QYXpzq-Y", "Data2!A:P");
IMPORTRANGE("https://docs.google.com/spreadsheets/d/1hAeUokEf-1DQjRYn2iQXCSCwAoRW-C083z5QYXpzq-Y", "Data3!A:P")
}, "WHERE Col2 = '"&TEXTJOIN("' OR Col2 = '", TRUE, Q:Q)&"' OR Col3 like '"&TEXTJOIN("' OR Col3 like '", TRUE, Q:Q)&"'", 0)

Output (A4):

output

Upvotes: 1

Related Questions