Google Sheets and performing a Query with large data

Question

I think I have read every forum online, and haven't been able to find a solution.

I am trying to query data in a different Google Workbook. My data has approximately 127,000 rows and has 5 columns.

It seems that importrange only works up to about 5,000 rows. Is there any other way to query data of this size? The results would be up to 100 rows, but it is necessary to search all 127,000 rows.

Answer 1

divide your QUERY / IMPORTRANGE into smaller chunks and wrap them into array constellation:

={QUERY(IMPORTRANGE("ID", "A1:A5000"), "select Col1", 0); 
  QUERY(IMPORTRANGE("ID", "A5001:A10000"), "select Col1", 0); 
  QUERY(IMPORTRANGE("ID", "A10001:A15000"), "select Col1", 0)}