I have an Apps Script pulling every tab name in my Google Sheet into a list. I'm then using a Query to pull all unique Names (from a Name column) from each of those tabs, with the Indirect function.
There are currently hidden rows in each of the tabs (there are 15 tabs total). The Query I have is currently pulling the Names in those hidden tabs.
How do I tell the Query to pull all unique data EXCEPT the hidden rows? Here is the existing Query formula I have which is including hidden row data (I'm only including 2 tabs of data here to shorten the formula for this example):
=UNIQUE(QUERY({IF(B2="", {"","","",""}, INDIRECT(B2));IF(B3="", {"","","",""}, INDIRECT(B3))},"Select Col4 where Col4 is not null order by Col4 label Col4 'Names'",1))
The {"","","",""} is because I have INDIRECT formulas for future anticipated tabs which don't yet exist in the list of tabs, so I won't have to update the formula every time a new tab is added. For excluding the hidden data, I've looked into the SUBTOTAL formula but I'm having trouble applying it to this situation.