2

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?

General Grievance
  • 4,555
  • 31
  • 31
  • 45
Rory Buchanan
  • 21
  • 3
  • My sheet currently has two tabs. One is a connected sheet linked to our bigQuery database and the other has a list of Shop ID's. The connected sheet has a query built into it to get all shops. =Query could be part of the solution but I'm not sure – Rory Buchanan Oct 08 '20 at 15:45
  • could you share a sheet with dummy copy pasted values as an example ? and an example output please ? – nabais Oct 08 '20 at 15:48
  • 1
    Don't use `A2:A` as it return nulls from bottom of data down to Sheet.getMaxRows(). Use range(2,1,sh.getLastRow()... – Cooper Oct 08 '20 at 17:03

3 Answers3

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)

Faye
  • 53
  • 1
  • 1
  • 6
0

This might work for you.

=SUBSTITUTE(QUERY(FILTER(D3:D,D3:D<>"",E3:E),"WHERE Col1 <> ''",9^99)," ","|")

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.

enter image description here

kirkg13
  • 2,955
  • 1
  • 8
  • 12
0

=QUERY(IMPORTRANGE("Google_Sheet_ID_Can_Be_Find_In_URL", "Sheet_Name!Range(you want to query)"),"SELECT * ")

or

=QUERY(IMPORTRANGE("Google_Sheet_ID_Can_Be_Find_In_URL", "Sheet_Name!A2:A"),"SELECT * ")

or

=QUERY(IMPORTRANGE("Google_Sheet_ID_Can_Be_Find_In_URL", "Sheet_Name!Range"),"SELECT * WHERE Col2='shops.id'")")

IMPORTRANGE() method import data from another worksheet. In the parameter, you type google sheet id from the url with quotes, type the desired sheet name end with ! Then you type the range from that sheet you want to query. When you wrap it with the outer QUERY() method, you can query the data from that range such as A2:A by selecting specific columns including the column with the range or * from that sheet name

When you're using IMPORTRANGE() method, it's going to return an array. The selected columns have to label in numeric like "SELECT Col 1, Col 2, Col 3"

Jcbyte
  • 207
  • 1
  • 4
  • 11