Is there a way in google sheets to select a list of cells as an array to be used as a parameter in a connected sheet?

Question

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?

Answer 1

Maybe a little late, but the easiest way I found was to convert to regex.

select (@POSTCODES) as test, postcode
  from `postcode.au_towns`
  where  regexp_contains(@postcodes,safe_cast(postcode as string))

Where @POSTCODES is a gsheet string using a formula like join("|",UNIQUE(Sheet1!D2:D)).

Just make sure to remove the extra "|" generated using something like left(B2,len(B2)-1)