Google Sheets + ChatGPT

I’ve been using ChatGPT for several weeks. It’s pretty amazing. One of my favorite uses of ChatGPT is asking it to draft various Google Sheets functions and formulas based on my needs.

While I could figure most of these out for myself, ChatGPT generally does it faster – even when the suggested solutions aren’t perfect. I’m finding it helpful. Helpful enough that I would miss ChatGPT if went away.

Is anyone else using ChatCPT to help solve their spreadsheet riddles?

2 Likes

Edward:

Yes. I’ve been experimenting with ChatGPT for help with complicated formulas and even Apps Script for Google Sheets. Like you, I’ve been impressed. My challenge is wording the request properly but that’s a good thing because it forces me to think it through.

In one case, it gave me the wrong code for an Apps Script. So, I went back to the same conversation and explained that the code failed and asked it to explain why. Believe it or not, ChatGPT’s answer started with, “I’m so sorry …” Then it proceeded to explain why it failed. Then it gave me the code that worked.

Pretty amazing.

1 Like

I’ve used it, or tried to use it for Excel but it was really wrong and getting it to the right answer was like pulling teeth. I think it’s good to give you an idea of what to use but you still need to make sure it gives you the right formula. You should always give it sample data and tell it what you want the result to look like.

1 Like

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.

3 Likes

Wow! That is very cool! Can’t wait to see how Microsoft deploys this for Excel. MS is exploring using OpenAI-based tools for Word, Powerpoint, and Outlook. I also can’t wait to see how Google responds.

I didn’t but I will now.