I have a new google sheet set up to query my database via a connected sheet.
The query returns a list of our shops and their sales per year. Each shop has an ID.
I am able to set Cell A1 in another, reference sheet, to be a parameter in the query. This way the connected query only returns results for that particular store ID.
When using this, I really want to put an IN
function into my query. The connected query would then look something like.
SELECT * FROM shops where shops.id in (@RANGE)
And @RANGE would be A2:A as an array.
I've had success naming each cell as a new parameter and then:
SELECT * FROM shops where shops.id in (@REFERENCE1, @REFERENCE2)
Is there a more elegant solution?
This might work for you.
This filters a column of store IDs based on which ones have been selected, and coverts that into a text string similar to the query you have been using. Producing something like
"A1|A3|A7"
.The query then just points to that result for the
contains
criteria.Note that if your range of store IDs to report on is built in some other fashion, you just need to point to its range, instead of using the filter I have.
See a sample sheet here. This also shows a merged example of the two formulas, to produce the report all from one formula.
https://docs.google.com/spreadsheets/d/11uMa7CNcTXBnnpWGSIC_WvGSa-P2GTLQ2T7GvTgY4oM/edit?usp=sharing
Let us know if this helps you.