ChatGPT, since its launch in November 2022, has become one of the most popular tools. It has been used for tasks as diverse and varied as job searches and digital marketing, coding and cooking assistant.
It is also good at Excel and Google Sheets tasks. It can help with formulas, formatting data, creating dummy data, and even generating macros and scripts. But whereas previously we need to move the data to and from ChatGPT, now we can do it all from Google Sheets.
We can now integrate ChatGPT with Google Sheets and use ChatGPT in Google Sheets. No more going back and forth between ChatGPT and Google Sheets. We can just enter the ChatGPT prompt in Google Sheets and get the answer right there and then.
So, if you’re interested in learning how to integrate ChatGPT with Google Sheets and use ChatGPT from Google Sheets, read on.
Ways to use ChatGPT in Google Sheets
There are a bunch of extensions that let us integrate ChatGPT into Google Sheets and access ChatGPT from Google Sheets each with its own quirks and features.
The most popular and, in my opinion, the most versatile is “GPT for Sheets and Docs” developed by Talarian, the maker of “Yet Another Mail Merge”. This ChatGPT extension for Google Sheets comes with several inbuilt functions that make it easy to use ChatGPT in Google Sheets even with limited knowledge of Google Sheets or prompt proficiency.
And best of all, the add-on itself is entirely free, though we need to pay for OpenAI API. An added benefit of using your own API is that you don’t have to wait for the developers to upgrade to the latest model, and you can customise API usage and choose the model to use.
Some other extensions that enable us to use ChatGPT in Google Sheets include Numerous.ai, SheetGPT, and GPT to Work, to name a few.
How to use ChatGPT in Google Sheets
We cannot access ChatGPT directly from Google Sheets but we can use third-party tools to connect ChatGPT to Google Sheets using its API. We’ll use an aforementioned extension “GPT for Sheets and Docs” to integrate ChatGPT into Google Sheets and use ChatGPT in Google Sheets.
Install the GPT for Sheets and Docs add-on in Google Sheets
1. Open Google Sheets and click Extensions→Add-ons→Get add-ons.
2. Search for “GPT for Sheets and Docs” and install click “Install”.
3. Click “Continue” and select the account in which to install the add-on. Then click “Allow” to grant the necessary permissions.
4. Click “Next” and then “Done” to complete the installation and set up.
Get the OpenAI API key
The add-on uses our own API key instead of the developer’s. This means that we need only pay for the API and not pay any subscription fee for using the add-on.
If you don’t already have your API key handy, follow these steps to obtain your OpenAI API key.
1. Log in to OpenAI. Sign up if you don’t have an account yet.
2. Navigate to the API key page by clicking your account icon and then “View API keys”.
3. Click the “Crete new secret key” button to generate an API key.
4. Copy the key and keep it confidential. Note that the key will not be revealed again. So keep it somewhere safe and accessible.
Connect ChatGPT to Google Sheets
Now that we’ve got the add-on and the OpenAI API key, we can now connect ChatGPT to Google Sheets using the two.
1. Open any Google Sheets spreadsheet.
2. Click Extensions. Select “GPT for Sheets and Docs” and then “Set API key”.
3. Paste the OpenAI API key and click “Check” to verify if the key is valid or if it has any credits. Then click “Save API key”.
Having integrated ChatGPT into Google Sheets, we now start using ChatGPT in Google Sheets. But there’s just one more step we need to take, ie, enable the ChatGPT functions in Google Sheets.
To do so, click Extensions→GPT for Sheets and Docs→Enable GPT functions.
How to use ChatGPT in Google Sheets with GPT for Sheets and Docs
After we have enabled to functions, they will be recognised by Google Sheets and we can use them right away. We can use GPT for Sheets and Docs to generate text, summarise lengthy text, translate language, format data, and more.
GPT for Sheets and Docs comes with a handful of handy functions that allow us to perform specific tasks without the need to elaborately write the prompt.
GPT function
The GPT function allows us to use it as you use ChatGPT. This is the basic, barebone function but it is also the most flexible. We can use it to generate text, create mock data, summarise text, and so on.
The syntax of the function is:
=GPT(prompt, [value], [temperature], [max_tokens], [model])Of the parameters, all but prompt are optional. The prompt can be either a text string or a cell reference. Value is text in cell or cell range to apply to the prompt, temperature determines the creativity of ChatGPT and ranges from 0 to 1. Max tokens is the maximum API token to use and model is the GPT model to use.
For example, to use the function to generate text, enter the function and the prompt either as a text string or a cell reference.
=GPT(“Write a compelling tagline for a coffee shop”)GPT_LIST function
This function is used for generating a list of items. This function has the same parameters as the GPT function, with only the prompt parameter being mandatory.
=GPT_LIST(“Five subject lines for an email about stock clearance offer”)GPT_TABLE
Use this function to get data in tabular form. For example, you may want to get a list of groceries with their nutritional information, generate a schedule, or a list of countries with various statistics such as population, GDP, HDI, Gini coefficient, or whatnot.
=GPT_TABLE(“Give me a list of ten fruits with their nutritional value”)GPT_FORMAT
This function is used for changing the formats of numbers and texts from one to another. We can use it to format dates, names, currencies, or addresses.
The syntax is:
=GPT_FORMAT(input, target_format)For example, to change the date format:
=GPT_FORMAT(“23 April 2022”, “ISO”)But this is not very helpful if we input the values manually. We can instead use cell references.
=GPT_FORMAT(A2:A12, “ISO”)GPT_EXTRACT
This function is useful if we want to extract specific information from a text. We can use it to extract information such as names, email addresses, and dates.
The syntax is:
=GPT_EXTRACT(text, to_extract)Text is the data from which to extract information; it can either be a text or a cell reference. To_extract is the information to extract from the text input.
For example, to extract the email address from a text, the following formula would be used:
=GPT_EXTRACT(A1, “email”)A1 is the cell that holds the text from which to extract the information.
By bringing ChatGPT to Google Sheets, the GPT for Sheets and Docs enables us to make the best of both the power of generative AI and the usefulness of spreadsheets. What used to take hours can now be done in minutes.
And speaking of speeding up tasks, Amigo can help you automatically import data from various sources such as Facebook Ads, Shopify, Google Analytics, and MySQL to Google Sheets. You can then easily track critical metrics without a hassle; Amigo refreshes them regularly.
Recommended articles
