Query function to select all columns of an imported data set, starting from a specific column-CodePudding

I am looking to query specific columns of an imported dataset from a separate google sheet. Ideally, I would like to query all columns after the "product ID" column (refer to "Test Data Set"). The reason why I am doing this is that I need the product ID column to remain in a particular position in the "Final Destination Sheet" since the "Product ID"s will be used as a reference range for the index and match formulas on another tab of that sheet.

The formula I have so far to import and query the original dataset for the "Product ID" column is as so:

=QUERY({IMPORTRANGE("1aPpdSy5venMl97ayn7VwaOo_AcAtcoc6_l4a1loPhNI","Sheet1!A:D")}, "SELECT Col" &MATCH("Product ID", IMPORTRANGE("1aPpdSy5venMl97ayn7VwaOo_AcAtcoc6_l4a1loPhNI","Sheet1!A1:1"),0),1)

However, I am unsure how to make the formula understand to query up all the columns after the "Product ID" column. Do note, the test data set only has 4 columns and a set number of columns but the real data set I am working with will be dynamic so more columns can be added or deleted over time. (So I won't know what the finite column letter will be and if columns are deleted from the left of B then it might shift its position which is why I used the match function in my original formula to ID that specific column's location on the spreadsheet).

Thank you in advance!

Final Destination Sheet (where the data is imported to and where I am trying to query the original data for specific columns): link

Test Data Set (where the data is imported from): link

CodePudding user response：

if you know the headers you can do:

=TRANSPOSE(QUERY(TRANSPOSE(IMPORTRANGE("1aPpdSy5venMl97ayn7VwaOo_AcAtcoc6_l4a1loPhNI", 
 "Sheet1!A:D")), "where lower(Col1) matches 'product id|size'", 0))

update:

=IMPORTRANGE("1aPpdSy5venMl97ayn7VwaOo_AcAtcoc6_l4a1loPhNI", 
 "Sheet1!"&ADDRESS(1, MATCH("Product ID", 
 IMPORTRANGE("1aPpdSy5venMl97ayn7VwaOo_AcAtcoc6_l4a1loPhNI", 
 "Sheet1!1:1"), 0))&":1000")

assuming headers are in row 1 this will look for a column called product id and import it and all the columns to the right to it