Is there a way to check if query has completed?

Question

Is there a way to have an if statement wait for query to complete before finishing?

I have the following formula in google sheets cell C3. In Cell B3 I have a search box.

=iferror(if($B$3="","",if(query(importrange("https://docs.google.com/spreadsheets/d/ID/edit#gid=1009875416","'Responses'!AD3:AL")," select Count(Col1) where lower(Col9) contains '"&lower(B3)&"' label count(Col1) ''",0)=1,"1 Result Found",query(importrange("https://docs.google.com/spreadsheets/d/ID/edit#gid=1009875416","' Responses'!AD3:AL")," select Count(Col1) where lower(Col9) contains '"&lower(B3)&"' label count(Col1) ''",0)&" Results Found")),"No Results Found")

When B3 is blank, all the results from the table Responses are there. When I type in something to search in B3 and hit enter, the table emptys with no results there and in C3 it says No Results Found, and then a couple seconds later comes up with either 1 Result Found or X Results Found and populates the table.

The question is why does it show No Results Found at first, and then change? The only thing I can think of is it runs before the query is finished, so wondering if there is a way to prevent the if from completing until the query completes.

Thanks Doc

Answer 1

1. Every time you change value in B3 the spreadsheet has to refetch data. Even if there is some caching, it takes some seconds. To avoid it, you could import all data in a separate sheet and feed it to QUERY functions in your formula, instead of using IMPORTRANGE.

2. It looks like you have an extra space in "' Responses'!AD3:AL" which makes the range invalid and probably triggers IFERROR's fallback to "No Results Found".

3. Important: the sheet in the formula is publicly accessible which is usually not safe. I do not know if it is intentional. You can read more security issues with access via a link here (disclosure: it is my website).

Answer 2

So here is an update. I found a solution. I removed the iferror to see what was happening. The "first" No Results Found was a "loading..." error. The "second" No Results Found was an #N/A error. So I changed the iferror to ifna and then wrapped the entire function in an iferror and gave it a value_if_error value of "Searching...". So now, when I do a search, and it throws the "loading" error, it says Searching... and if it finds no results, it says No Results.

Here is the modified formula (and per dataful.tech, I changed the importrange values in this and original post to a "ID")

=iferror(ifna(if($B$3="","",if(query(importrange("https://docs.google.com/spreadsheets/d/ID/edit#gid=1009875416","'JGOOT Responses'!AD3:AL")," select Count(Col1) where lower(Col9) contains '"&lower(B3)&"' label count(Col1) ''",0)=1,"1 Result Found",query(importrange("https://docs.google.com/spreadsheets/d/ID/edit#gid=1009875416","'JGOOT Responses'!AD3:AL")," select Count(Col1) where lower(Col9) contains '"&lower(B3)&"' label count(Col1) ''",0)&" Results Found")),"No Results Found"),"Searching...")