slow Sort / Filter Function for Google Sheets with Importrange

1
votes

For our Company Database I have created a working sort/filter function that grabs data from another sheet via Importrange. The search can be narrowed down by certain searching parameters.

Currently the database is still on the same sheet, so having 20.000 rows and 18 columns of information are still working fine and fast.

However I recently sourced out to another sheet and thus I am forced to use either importrange or queries. The solution with importrange produces a very high workload on the sheet and I am getting lag spikes every few minutes.

I have tried using a query solution as well, but there I can not grasp how I can use a possible "and/or" function (i.e. search for all orders of User C with Remark 1 only. If no parameter is given, show everything.)

query(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1ea47tHXZ5QfSGQ2F9VN0RLXbP9HHg-7pdpZ4_9QTKY4/edit";"Sheet!A11:D");"
SELECT * WHERE 
Col1 = '"&B1&"'  
Col2 = '"&B2&"'
ORDER BY Col3 DESC
";0)

For better understanding I have issued two sheets that show the formula on a very small scale.

Sheet 1 (here you can put in the search parameters): https://docs.google.com/spreadsheets/d/18BUz7Qm0TPMB2xte4iJ31P5aYTQcZNlbiW_agxAfb4U/edit#gid=0

Sheet 2 (here are the database entries):
https://docs.google.com/spreadsheets/d/1ea47tHXZ5QfSGQ2F9VN0RLXbP9HHg-7pdpZ4_9QTKY4/edit#gid=0

Your help is much appreciated. :)

1
if-statementgoogle-sheetsgoogle-sheets-queryimportrangetextjoin

1 Answers

2
votes

try:

=QUERY(IMPORTRANGE("1bb6reEx6QG8aAqiPHKrQAdNJKbBYNnGceFjgRAPpeo4"; "Sheet!F6:I"); 
 "where "&TEXTJOIN(" and "; 1; "1=1";
 IF(B1="";;"Col1 contains '"&A1&" "&B1&"'");
 IF(B2="";;"Col2 = '"&A2&" "&B2&"'");
 IF(B3="";;"Col3 = date '"&TEXT(B3; "yyyy-mm-dd")&"'");
 IF(B4="";;"Col4 = '"&A4&" "&B4&"'")); 1)

0

demo spreadsheet