I have a query that runs on Google Sheets against a table where the user has separate input that can modify values in logic checks to a TRUE FALSE result on that table. It is possible that the user has a selection that returns all FALSE for a where statement within the query.
The output I get is #N/A, I want to be able to create an exception where I can identify that it's returning #N/A, and change the output to blank or "No results found".
My users are not very tech savvy, so I need it to be legible to someone without coding knowledge or experience.
Normally when I'm returned an error from a function in Google Sheets, or Excel for that matter, I use the =Iferror() function to control the output of an error exception. This does not seem to work, either because the function iferror was made with a scope that falls outside of =query, or because of some other behind the scenes structural problem.
FALSE, PIG, $5.50
FALSE, APPLE, $0.50
FALSE, CAR, $25.00
FALSE, HORSE, $20.00
I thought then maybe I could add an if() statement, and put the query in a logic statement checking for the value "N/A":
if(query(A:B,"select B where A =TRUE")="#N/A","No results found",(A:B,"select B where A = TRUE"))
This did not work. The value that was returned was "#N/A" still. So I thought maybe the value type of the exception wasn't technically a string value, so it wouldn't accept the value of a non-string #N/A to equal "#N/A", so I wrapped the first query in a concatenate() in an attempt to force it into a singular variable/cell value. I know this works in turning the query it runs into a singular string if it has results, but doing this:
if(concatenate(query(A:B,"select B where A =TRUE"))="#N/A","No results found",(A:B,"select B where A = TRUE"))
This still returns "#N/A"
I thought maybe the actual value it's throwing back is a NULL type value, and it has some clever behind the scenes shenanigans to make it display "#N/A" when the where function filters out all results. so I changed the parameter it's checking against in the if statement from "#N/A" to "" as follows:
=if(concatenate(query(lookup!Z2:AN,"select Z where AN = TRUE"))="","No results found",query(lookup!Z2:AN,"select Z where AN = TRUE"))
This still returns "#N/A"
I thought maybe the isblank() function would have some mechanism in it to find that it's blank, changing it to:
=if(ISBLANK(concatenate(query(lookup!Z2:AN,"select Z where AN = TRUE"))),"No results found",query(lookup!Z2:AN,"select Z where AN = TRUE"))
This also still returns "#N/A"
I'm at a loss. I'm expecting it to throw me the exception I'm qualifying: "No results found" but this never comes. Obviously, I don't understand the nature of how the =query() function is handling data on the back end of things. It must be doing something on a separate layer of operations or something, or have a totally different scope, but how am I supposed to figure out what that variable/process is?
Not just IFERROR?:
=iferror(query(A:B,"select B where A =TRUE"),"No results found")