7 Ways to Easily Connect ChatGPT to Google Sheets
Along with many members of the Tiller Community, Tiller’s team is enthusiastically experimenting with ChatGPT in Google Sheets.
These experiments are isolated from our product development and experience to maintain Tiller’s strict privacy and security protocols.
However, we’re excited by the potential of generative AI.
Soon everyone will have easy, optional access to native AI tools in their spreadsheets, unleashing new ways to organize, visualize, and understand data.
Later this spring, Microsoft Copilot will bring OpenAI’s ChatGPT to Microsoft 365, including Excel.
And Google is rapidly bringing generative AI to its entire Workspace suite, starting with Docs and Gmail. Sheets is close behind.
How to Connect ChatGPT to Google Sheets
When Google brings generative AI to Google Sheets, it won’t be via OpenAI’s familiar ChatGPT. It may take the form of Bard, an AI chatbot based on LaMDA, which Google started working on back in 2015.
But even when Google has its own native AI tools available in Google Sheets, some people may prefer OpenAI’s ChatGPT. And if you want to start experimenting with natural language AI in your Google Sheets right now, ChatGPT is your best bet.
So here are a few extensions and workflows for connecting ChatGPT and your Google Sheets. But please note:
To protect your privacy, always exercise caution when connecting third-party extensions, code, and templates to Google Sheets. Even though you can roll back changes in Google Sheets, we don’t recommend experimenting with these workflows in your Tiller-powered sheets until you’re sure they’re safe, private, and effective.
1. Google Apps Script and ChatGPT API
Want to access ChatGPT from Google Sheets, but don’t want to use a third-party extension? Google Apps Script to the rescue!
This concise blog post provides example code and a written tutorial for deploying it.
Here’s another more detailed post about how to connect ChatGPT To Google Sheets using Google Apps Script.
And here’s a video on this topic from yet another source:
2. Import OpenAI (ChatGPT) Data to Google Sheets with the Mixed Analytics Extension
Mixed Analytics has a great tutorial for pulling data from the OpenAPI (ChatGPT) API directly into Google Sheets, using their free API Connector add-on for Sheets.
You can use this method to generate text content and images in response to prompts you enter in your spreadsheet.
3. Use a Zapier Connection Between OpenAI and Google Sheets
Read how Zapier can link OpenAI (GPT-3 & DALL·E) to Google Sheets.
4. GPT for Sheets and Docs
GPT for Sheets and Docs is currently the ChatGPT extension with the highest number of installs and reviews. It was created by Talarian, maker of the super-popular “Yet Another Mail Merge” extension for Google Sheets.
GPT for Sheets™ and Docs™ is an AI writer for Google Sheets™ and Google Docs™. It enables you to use ChatGPT directly in Google Sheets™ and Docs™. It is built on top OpenAI ChatGPT, GPT-3 and GPT-4 models. You can use it for all sorts of tasks on text: writing, editing, extracting, cleaning, translating, summarizing, outlining, explaining, etc
- Price: Free
- Learn more: Website and Google Workspace Marketplace
5. PromptLoop for Google Sheets
One formula to transform, extract, or summarize any text that works just like SUM or VLOOKUP and generates answers. Use powerful AI models like GPT-3 in Google Sheets.
This add-on lets you use a custom formula to automatically complete any text-based task, from categorizing and summarizing to formatting and translating. They also have a small gallery of ChatGPT templates.
- Pricing: Free to $49 or higher with an enterprise plan
- Learn more: Website and Google Workspace Marketplace
6. Flowshot – Google Sheets AI
Easy to use, powerful AI in Google Sheets. Autocomplete repetitive tasks, use ChatGPT, and train custom AI models with the all-in-one AI toolkit.
- Pricing: Free to $129/month
- Learn more: Website and Google Workspace Marketplace
7. GPTSheets Chrome Extention
To make GPTSheets work, you’ll need to install a Chrome extension and add a snippet of code to any spreadsheet you’d like GPTSheets to work on.
GPTSheets is a Chrome extension that brings the power of OpenAI’s GPT language model to your spreadsheets.
With GPTSheets, you can create spreadsheet formulas and functions simply by typing them out in plain English. It can also explain spreadsheet formulas/functions in plain English. Simply highlight a formula, and our tool will translate it into plain English, making it easier for you to understand and modify.
- Pricing: Try Free, then $15/month
- Learn more: Website and Google Chrome Store
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:
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 didn’t but I will now.