Here’s an example showing success with ChatGPT helping with a Query in Google Sheets that I was having trouble with.
I’m not an expert on the Query function and I can easily spend a lot of time trying find a syntax error that turns out to be a misplaced comma, parenthesis, etc. I’ve turned to ChatGPT several times recently and it’s helped in every case. This is one example.
I’m building a query in Google sheets to filter data from the transactions tab based on the Category, Account, Group, Type, and Tag. I want the query to return results from the Transactions tab based on a combination of values from one or more of these 5 fields / columns.
The values for each are in consecutive rows in column B. Checkboxes are next to each in column C.
The query will filter the results by matching the values in column B if the checkbox next to each is checked (True). This technique is described in this video:
This is what my Query looks like so far:
=QUERY(Transactions!$B$1:$K,"Select B,C,D,E where D = '"&B2&"' and '"&C2&"' = 'TRUE'",1)
Note that in this case, the Where clause will match if the Category in the Transactions sheet “equals” the value in B2 of the Query tab.
By changing the Where clause from “Where =” to “Where contains”, the query will match if the category in the Transactions sheet contains the value in B2.
But I had to manually edit the formula – replacing = with contains and I wondered if I could create another dropdown containing two values, “Contains” or the “=” sign. I modified my Query to reference the value in this dropdown but I couldn’t get it to work.
I put the question to ChatGPT. See below. This is the exact wording of my question and ChatGPT’s answer. It worked. I waited about 3-5 seconds for the answer.
Q: How can I modify this query in Google Sheets such that the first value in the Where clause refers to the contents in cell F3 in the same sheet rather than the literal value “contains.” :=QUERY(Transactions!$B$1:$K,“Select B,C,D,E where D contains '”&B2&“’ and '”&C2&“’ = ‘TRUE’”,1)
A: You can use the INDIRECT function to reference the contents of cell F3 in the Where clause of your QUERY function. The modified query would look like this:
=QUERY(Transactions!$B$1:$K,“Select B,C,D,E where D “&INDIRECT(“F3”)&” '”&B2&“’ and '”&C2&“’ = ‘TRUE’”,1)
I copied and pasted this into my sheet and it worked perfectly.
Note: At this point, the formula refers only to one of the 5 columns … Category. I believe I can add the other four fields by adding an OR clause (4) and shifting the row references in columns B and C down by 1.