Do you want to incorporate the artificial intelligence tool Chat GPT into Google Sheets? While there is no official Chat GPT for Sheets add-on, plenty of third-party apps will allow you to use it in your spreadsheets. However, you’ll need to install one of these add-ons and add a unique GPT API. But don’t worry; this guide will show you how to do it step-by-step.
Table of Contents
How To Set Up Chat GPT for Sheets and Docs
OpenAI hasn’t created an official Chat GPT Google Sheets extension as this is written. However, multiple third-party Chat GPT integration extension tools exist for Google Sheets and Docs, allowing users to use the GPT API within the Google platforms.
Setting up Chat GPT for Google Sheets is simple and doesn’t require coding knowledge. However, it does require a few steps, which I will cover below. Before I head to the OpenAI website and grab the GPT API key, let’s look at how to set up the ChatGPT Google Sheets extension.
For this article, I will use the “GPT for Sheets and Docs” Chrome extension from the Google Workspace Marketplace.
How To Install Chat GPT for Sheets and Docs
Both Google Docs and Sheets share the same UI, meaning the process to install the extension is the same for both applications. Follow the instructions below:
- Log into your Google account.
- After logging in, open a Google spreadsheet.
- Click the “Extensions” button in the main toolbar to open a drop-down menu.
- Click on “Add-ons” > “Get add-ons.”
- This will open a new window in Google Sheets where I will click on the search bar towards the top right part of the screen.
- Type “GPT for Sheets and Docs” and press “Enter.”
- For this example, I used the GPT for Sheets™ and Docs™ extension by Talarian.
- On the extension’s page, click the blue “Install” button.
- A smaller window will open with a prompt asking to install the Google Sheets ChatGPT integration extension.
- Click the “Continue” button.
- Select the Google account that you want to install the extension.
- Select the correct Google account to review the permissions.
- Then, click the “Allow” button.
- The extension will be installed, and you can close the window.
Related: Google Sheets vs Excel – Which Is Better?
How To Create and Setup an OpenAI API Key
The Chatbot Google Sheets extension won’t work unless I provide it with an OpenAI API key linked to my account.
Here’s how to create a new API key and link it to the GPT for Sheets and Docs extension:
- Head to the OpenAI API keys page.
- If you already have an account, log in.
- Alternatively, you can sign up for a new account.
- Click on the “API keys” section in the sidebar on the left side of the screen.
- Click on the “Create new secret key” button, and enter a name for the secret key.
- After writing the new secret key name, click the green “Create secret key” button to confirm the name change.
- After a quick human verification, the API key will be displayed.
- NOTE: I can only copy this key once. Afterward, I won’t be able to reaccess it from the OpenAI website. Therefore, saving the key is a good idea.
- Just click on the “Copy” button.
- Open Google Sheets and click the “Extensions” button in the main toolbar.
- Click on GPT for Sheets™ and Docs™.
- Then, click “Open,” and a new sidebar will appear on the right.
- In the “Enter your OpenAI API key” textbox — enter the API key in the window.
- Click the green “Next” button.
- Click on “Extensions,” > “GPT for Sheets™ and Docs™” > “Enable GPT functions.”
- The Chat GPT Google Sheets extension is now enabled.
Related: A Tutorial for Google Sheets IF Then Calculations
Looking for a quick and easy way to use ChatGPT for Sheets and Docs? Watch this 2-minute video to learn more!
Commands in ChatGPT Google Sheets Function
Once you have enabled your ChatGPT for Sheets extensions, it is also worth learning the common commands that can be used in all sorts of tasks with the Google Sheets ChatGPT plugin.
I have highlighted the most common commands below:
- GPT – To be used to get the result from a prompt in a single cell
- GPT_TRANSLATE – This can be used to translate content in a specific cell
- GPT_CLASSIFY – This can be used to classify content from a spreadsheet into a specified category
- GPT_EXTRACT – To be used to extract entities from the spreadsheet data
- GPT_SUMMARIZE – To be used to summarize the spreadsheet content
- GPT_FORMAT – This can be used to format specific data from one format to another
- GPT_EDIT – To be used to edit content from a spreadsheet
- GPT_TAG – This can be used to add tags or taglines to the worksheet content
- GPT_MATCH – This can be used to match the values from two separate columns
- GPT_SPLIT – To be used to split the text into sections, paragraphs, or sentences
- GPT_LIST – To be used to get multiple results in one column
- GPT_TABLE – This can be used to get a table of items from one prompt
- GPT_FILL – To be used to fill a range of data from examples, such as a list of names
Related: Top 22 Google Sheets Tips and Tricks to Learn
How To Use GPT for Sheets Extension
Now that we have examined the different functions of the Google Sheets ChatGPT extension, I will walk you through how to use the extension by outlining some examples of using ChatGPT in Google Sheets.
Text Generation
The Chat GPT for Sheets extension can generate responses from ChatGPT directly into the spreadsheet. To do this, use the following formula:
=GPT(prompt)
In this example, I have a question in cell A2: “How many letters are there in the alphabet?”
I want to use this question and turn it into a prompt in cell C2. Therefore, I will use =GPT(A2) in cell C2.
After a few seconds, the output will be displayed in the cell. I can also directly write the Prompt question in the GPT formula. However, I must ensure that the question prompt is surrounded by Quotation Marks ( “ ), as shown in the screenshot below.
If I want to have the output in different cells, I can use the “GPT_LIST” function. For example, if I want to have the names of five fruits in a column. When I press “Enter,” the names of the fruits will be shown in the five cells in the “Output” column.
Text Translation
The ChatGPT plugin can be used to translate text as well, which can be done using the “GPT_TRANSLATE” function. To use this function, use the following formula format:
=GPT_TRANSLATE(text, target-language, source-language)
For this example, I want to translate text from English to Spanish. All of the parameters for this example are stored in different cells. Therefore, I can reference them in the formula and press “Enter” to execute it. Once executed, the ChatGPT for Sheets function will display the translated text in the cell with the formula.
Text Classification
I love using the “GPT_CLASSIFY” function, which allows me to label or classify my data in Google Sheets using AI. This works in various scenarios, like labeling items and differentiating between male and female names, animals, and currencies.
To do this, use the following format:
=GPT_CLASSIFY(text, labels)
I want to classify different sentences according to their sentiments in this example. To do this, I will create a cell with the types of sentiments. Then, I will reference the cell in the formula and the cell with the sentiments.
When the formula is executed, ChatGPT will analyze all the sentences and classify them according to the sentiments, making this a simple and efficient function.
Generating Tables
Another great thing about using ChatGPT is that it can generate full-fledged tables based on its last received training data.
Keeping this in mind, I use the “GPT_TABLE” function to create a table based on a Chat GPT prompt using the following formula:
=GPT_TABLE(prompt)
For the example above, let’s say I want to create a table that contains the names of the top five movies, their IMDB scores, and their theme. To do this, I will write the prompt as “5 of the best Hollywood TV shows, their IMDB rating, and one word to describe their theme.”
Then, I can write the formula as:
=GPT_TABLE(“5 of the best Hollywood TV shows, their IMDB rating and one word to describe their theme”)
Alternatively, I can reference the prompt from a cell. After I press “Enter,” Google Sheets will automatically create the table with the top-left part of the table starting at the formula’s location.
Data Extraction
This is my favorite function because it is easily one of the most valuable functions for the ChatGPT plugin for Google Sheets, especially for people who work with data cleanup.
The “GPT_EXTRACT” function allows me to extract a specific data type from the cells. This means that I can paste the data from a CSV file into Sheets and use the function to extract valuable data such as email addresses, cities, countries, genders, and other information.
In such cases, I would use the following formula can be used:
=GPT_EXTRACT(source, type)
In the example above, I have a data set that contains unorganized data with email addresses and cities in the same sentence. I want to extract specific text data, including the email, location, and names.
To do this, I will use the formula and reference the cells containing the relevant data in the parameters. Once I execute the formula, the data will be extracted and sorted into different cells.
Best Tips for ChatGPT in Google Sheets
Here are a few things to note when using the GPT in the Google Sheets extension:
- It’s best not to use the ChatGPT Google Sheets plugin in spreadsheets containing sensitive data, as OpenAI processes it. Therefore, I always ensure the prompt does not contain any personal or company data.
- The updates by OpenAI or Google can break the functionality of the plugin. For this reason, I always enable automatic updates so I can have access to the latest security fixes and new features.
- The ChatGPT app is trained on real-world data, which may have biases. However, OpenAI has significantly reduced biases in newer updates.
- The ChatGPT responses aren’t always factually accurate, so I always check outputs with the Google Sheets Chat GPT plugin.
- The Chatbot doesn’t have any advanced training on industry-specific data, so it won’t be able to understand advanced concepts. Therefore, I recommend that you use easy-to-understand prompts in simple language.
Related: 10 Incredible Google Forms Add-Ons
Frequently Asked Questions
Can ChatGPT Work With Google Sheets?
Although OpenAI or Google hasn’t created an official extension that allows ChatGPT to work in Google Sheets, multiple third-party extensions allow users to use ChatGPT in Sheets. The extension works by using an API key to integrate the functions of ChatGPT into Sheets.
How Do I Enable ChatGPT in Sheets?
ChatGPT can be enabled by using an extension. I recommend using the GPT for Sheets™ and Docs™ extension by Talarian, which can be installed from the Google Workspace Marketplace.
I click the “Install” button on the extension’s page and grant the required permissions using my Google account to install the extension in Sheets. Then, I will provide my OpenAI API key and ChatGPT to enable the extension in Sheets.
How Can I Create an OpenAI API Key?
To generate an OpenAI API key, follow these simple steps:
- Head over to OpenAI’s website and the “API keys” section.
- Click on the “Create new secret key” button and choose a name for the key.
- Click the “Create secret key” button to display the API key.
- Click the “Copy” button and head to the Google Sheets window.
- Click the “Extensions” button in the main toolbar
- Click on “GPT for Sheets™ and Docs™” to open the new sidebar to the right.
- In the window, “Enter your OpenAI API key” in the textbox.
- Click “Next” to confirm.
Wrapping Up
Chat GPT for Sheets can make life much easier when working on your spreadsheets. You can also check out some spreadsheet templates we’ve created to save time and effort. Check out the SpreadSheet Point store and use the promo code “SSP” to get 50% off all templates.
Related:
