Importrange & query import order is random and not below each other

Question

I have some problems, I am trying to resolve this now since a while but I am always ad a dead end.

I have multiply taps in my DATA Sheet, each tap has 5000+ rows (in this example now way less), some rows have the same content and I want to find specific rows in my ArtikleFinder Sheet, while entering a specific number and the matching numbers 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.

Let's say, I have 5000 articles spread in 5 tabs on Sheet A, someone is ordering 20 articles, so I just want to scan the a certain number or any other criteria 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.

Till now I resolved this with Importrange and query, however it is limited. I can only search for 3 criteria within 1 query, and also the data appears randomly.

I had to make 2 tabs & codes with 2 different query but same Importrange to be able to find all the matching data.

PKG-ID & BOL Finder (Column 2 & 3)

=QUERY({
IMPORTRANGE("1EO2dTznbgaWu1CCWl3OyAw54Zd3jtkhDuOgnal671KI", "MERGE 17.02. - 20.02!A:J");
IMPORTRANGE("1EO2dTznbgaWu1CCWl3OyAw54Zd3jtkhDuOgnal671KI", "MERGE 21.02 - 27.02!A:J");
IMPORTRANGE("1EO2dTznbgaWu1CCWl3OyAw54Zd3jtkhDuOgnal671KI", "MERGE 28.02 - 06.03!A:J")
}, "WHERE Col2 = '"&TEXTJOIN("' OR Col2 = '", TRUE, A:A)&"' OR Col3 like '"&TEXTJOIN("' OR Col3 like '", TRUE, A:A)&"'", 0)

ASIN, EAN, LPN Finder (Column 5 - 6 - 7)

NOTE: LPN(Col7) needs to be CONTAINS and not LIKE, since I often have multiple LPN numbers in one cell and it cant be found otherwise

=QUERY({
IMPORTRANGE("1EO2dTznbgaWu1CCWl3OyAw54Zd3jtkhDuOgnal671KI", "MERGE 17.02. - 20.02!A:J");
IMPORTRANGE("1EO2dTznbgaWu1CCWl3OyAw54Zd3jtkhDuOgnal671KI", "MERGE 21.02 - 27.02!A:J");
IMPORTRANGE("1EO2dTznbgaWu1CCWl3OyAw54Zd3jtkhDuOgnal671KI", "MERGE 28.02 - 06.03!A:J")
}, "WHERE Col5 = '"&TEXTJOIN("' OR Col5 = '", TRUE, A:A)&"' OR Col6 like '"&TEXTJOIN("' OR Col6 like '", TRUE, A:A)&"' OR Col7 contains '"&TEXTJOIN("' OR Col7 contains '", TRUE, A:A)&"'", 0)

What I can't figure out are the following things:

1. When scanning the PKG-ID (for example), the Article appears. In the next scan, the article appears as well BUT either below, above or in the middle. I want everything to appear below each other, so I don't have to search the new scan to see, what the number is all about.

2. I would prefer to only have one tap instead of two for the scans. But Query seems to be limited, is there a workaround?

3. Importrange would also work easier if It see's automatically the whole sheet with all the tabs instead for each tab I have to put a new "Importrange"

4. related to this solution, I also need another option, where only specific rows will be imported, this also can be solved with query, I just would like to know If I can combine this with the problems above too?, "select Col1,Col2,Col6,Col7,Col8,Col15,Col16,Col17 where Col2 matches '"&TEXTJOIN("|", 1, A2:A)&"'", 0)

Is there maybe only a some numbers I am missing with Importrange/Query?

Thank you

Answer 1

#1

For question n°1, it will be difficult to obtain the same order between this scan and the result of the query since importrange and query will take the order of the database.

I also expect some rows to have already been picked up by a previous scan.

What you can do if you want to highlight the last scan entered is to top level a conditional formatting with the formula like

=A1=index($A:$A,match("zzz",$A:$A,1))

#3

To help you adding new tabs in your formula, you can refresh it by a little script, example for PKG-ID, BOL:

function myFunction() {
  var id = '1EO2dTznbgaWu1CCWl3OyAw54Zd3jtkhDuOgnal671KI'
  var ss = SpreadsheetApp.openById(id)
  var rng = []
  ss.getSheets().forEach(sh => rng.push(`IMPORTRANGE("${id}", "${sh.getName()}!A:J")`))
  var formula = `=QUERY({${rng.join(';')}}, "WHERE Col2 = '"&TEXTJOIN("' OR Col2 = '", TRUE, A:A)&"' OR Col3 like '"&TEXTJOIN("' OR Col3 like '", TRUE, A:A)&"'", 0)`
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName('PKG-ID, BOL').getRange('B3').setFormula(formula)
}

edit

by script

function artikleFinder() {
  var id = '1EO2dTznbgaWu1CCWl3OyAw54Zd3jtkhDuOgnal671KI'
  var source = SpreadsheetApp.openById(id)
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('TEST (script)')
  sh.getRange(3,2,sh.getLastRow(),sh.getLastColumn()).clearContent()
  var list = sh.getRange('A3:A').getValues().filter(r => r[0] != '').join().split(',')
  var data = []
  list.forEach(function (crit) {
    source.getSheets().forEach(function (sh) {
      var blankRow = Array.from({ length: 1 }, () => Array.from({ length: 10 }, () => ''))
      var prov = sh.getRange(1,1,sh.getLastRow(),10).getValues().filter(
        r => (r[1] == crit || r[2] == crit || r[4] == crit || r[5] == crit || r[6] == crit)
      )
      if (prov.length){
        data.push(prov)
        data.push(blankRow)
      }
    })
  })
  result = data.flat()
  sh.getRange(3,2,result.length,result[0].length).setValues(result)
}

The result will be in the same order as the list in column A. However, if a row matches different criteria, that row will be duplicated