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?
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.
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.
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.
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 took a test drive of ChatGPT and WOW! it was amazing. As a test I just asked it to create an apps script function that would sort a sheet in my budget system. I gave it the parameters (in non technical language!) and it whipped one up for me that worked! I also asked it some medical questions about drug side effects in cats. Amazing, it gave me info that was correct. It even gave me advice on the best way to give a pill to a cat.
ChatGPT helped me identify an almost invisible bug in a query that I would have gone crazy trying to find otherwise.
A couple of weeks ago, I posted an Apps Script that creates a custom function that displays a Table of Contents with hyperlinks to all tabs in a workbook.
Even though I copied the code directly from the Script Editor in Google Sheets, people reported that it wasn’t working. They had to replace double quotes with single quotes in two lines of the code I posted. I checked and double-checked to make sure I was copying the App Script correctly. I couldn’t figure out what was wrong.
In a totally unrelated matter, I wrote a query to find data between two dates. Here’s the query:
=QUERY($A:$C, “Select * Where C>= date ’” &Text(H3,’”YYYY-MM-DD”)&”’ and C <= date '"&Text (I3, "YYYY-MM-DD”)&”’”,1)
I asked ChatGPT if the syntax was correct. It said that it wasn’t and it showed me the correct syntax which looked exactly like mine. I asked it to explain. Here’s what it said:
SELECT and WHERE keywords are double quotes (") instead of curved quotes (“ and ”).date keyword and the single quotes (').TEXT function is corrected to use double quotes (") instead of curved quotes (“ and ”)Looking at the code on the screen, it was impossible to see the differences between the double quotes. I still can’t but apparently, they’re different. Somehow, the double quotes in the script that worked in Google Sheets were converted to curly quotes when I copied and pasted into my post about creating a Table of Contents with hyperlinks.
Perhaps the quotes wouldn’t have been converted if I pasted into a Blockquote. ?? If anyone knows, I’d like to know how to paste code copied from Apps Script without any conversions.
In the meantime, I think I’ll check scripts using ChatGPT to identify syntax conversions and correct them manually when I post.
Here’s a YT video from Leila Gharani abut ChatGPT and Excel. She brought it into Excel and you can easily use it within Excel to help formula writing.
Bring ChatGPT INSIDE Excel to Solve ANY Problem Lightning FAST - YouTube
ChatGPT can help deconstruct and explain complicated formulas in Sheets.
Sometimes (often) I have trouble deconstructing Sheets formulas containing multiple nested functions.
I asked ChatGPT to explain a sample formula and to return its answer in a form I could copy and paste into another document for me to study.
Sample formula: =ArrayFormula(INDEX(A1:A20,MODE(MATCH(A1:A20,A1:A20,0))))
This is the result.
This is the Prompt I used to get the result. It’s crude and I’m sure it could be simplified but it worked.
Can you separate this Google Sheets formula into separate functions and return your answer in the form of two columns? The first column should contain the function. The second should contain your explanation. Here’s the formula: =ArrayFormula(INDEX(A1:A20,MODE(MATCH(A1:A20,A1:A20,0))))
Using one of the ChatGPT extensions for Sheets or Excel, I’m sure this Prompt could be standardized to refer to a formula in a cell rather than entering the formula in the prompt.
Thank you for sharing this!
Are you using a particular ChatGPT extension? I’ve played around with GPTForWork. Pretty nifty.
I have GPT for Sheets and Docs which adds a menu item, but I also use custom functions to play around:
`const SECRET_KEY = “”;
const MAX_TOKENS = 50;
const gpt_model = “text-davinci-003”
function OpenAI(prompt, temperature = 0.7, model = gpt_model) {
const url = “https://api.openai.com/v1/completions”;
const payload = {
model: model,
prompt: prompt,
temperature: temperature,
max_tokens: MAX_TOKENS,
};
const options = {
contentType: “application/json”,
headers: { Authorization: "Bearer " + SECRET_KEY },
payload: JSON.stringify(payload),
};
const res = JSON.parse(UrlFetchApp.fetch(url, options).getContentText());
return res.choices[0].text.trim();
}`
function AIAnalyze() {
const SECRET_KEY = “”;
const MAX_TOKENS = 50;
const model = “text-davinci-003”;
// Get the active sheet
var sheet = SpreadsheetApp.getActiveSheet();
// Get the data range
var range = sheet.getRange(“A12:G30”);
// Get the data as a 2D array
var data = range.getValues();
const url = “https://api.openai.com/v1/completions”;
const payload = {
model: model,
prompt: "Analyze my data: " + data,
temperature: .5,
max_tokens: MAX_TOKENS,
};
const options = {
contentType: “application/json”,
headers: { Authorization: "Bearer " + SECRET_KEY },
payload: JSON.stringify(payload),
};
const res = JSON.parse(UrlFetchApp.fetch(url, options).getContentText());
SpreadsheetApp.getUi().alert(res);
// Write the result to a new sheet
var resultSheet = SpreadsheetApp.getActive().getSheetByName(“Result”) || SpreadsheetApp.getActive().insertSheet(“Result”);
resultSheet.clearContents();
resultSheet.appendRow([“Generated text”]);
resultSheet.appendRow([text]);
}
For people curious about using ChatGPT in Google Sheets, here are some resources for safely getting started: 7 Ways To Easily Connect ChatGPT To Google Sheets - Tiller
FYI … I received a notice from OpenAI that they are recommending a model named “gpt-3.5-turbo” (no quotes) effective March 23rd. They are also deprecating the following models:
This info is from an email received March 20th, 2023 from OpenAI. Their email starts with:
“On March 23rd, we will discontinue support for the Codex API. All customers will have to transition to a different model.”
ChatGPT might also be able to help standardizing and categorizing bank and credit card descriptions.
Steps:
Copy and paste just your transaction descriptions column into a new spreadsheet.
Upload that new spreadsheet into ChatGPT
Create a prompt: Analyze the uploaded file and add two new columns to it 1) one column that standardizes the description so that similar descriptions all have the same description 2) second column that categorizes the description to match your reporting needs. Instead of just putting “other” and “miscellaneous” when you can’t seem to identify the description, do your best to extract the appropriate value from the description to use in the standardized description, but ensure that all similar descriptions have the same standardized description. Do web searches where you are not sure too.
Then you can download the updated file and use lookups to get the standardized and categorized columns into your reporting.
Provide examples of what you want eg:
UBER *TRIP => Category: Uber, Type: Ridshare Transportation
UBER *TRIP => Category: Uber, Type: Ridshare Transportation
AMTRAK .CO1870616516360 => Amtrak, Type: Train Transportation
AMTRAK .CO1860733553529 => Amtrak, Type: Train Transportation
MCDONALD’S #8975 C_5814 => McDonalds, Type: Dining Out Fast Food
MCDONALD’S (BGV) => McDonalds, Type: Dining Out Fast Food
You could also paste in a list of categories and ask it to choose from that list.
It might not be perfect but works surprisingly well.
ChatGPT 4 will be better but 3.5 might still be useful
