Automating Analysis with ChatGPT in Spreadsheets

The launch of ChatGPT opened up a whole new world of possibilities. ChatGPT has proven impressive in tasks like text generation, translation, summarization, cleaning up data, and a range of text-based tasks that are typically tedious to complete within spreadsheet applications.

MUO VIDEO OF THE DAY

SCROLL TO CONTINUE WITH CONTENT

So, what if we could invoke the powers of ChatGPT within a spreadsheet? Rather than thinking up complex formulas, what if you could provide instructions in English and let ChatGPT do the heavy lifting?

Sounds good, right? Here’s how to integrate GPT technology into Google Sheets and get your spreadsheet done effortlessly.

Disclaimer: This post includes affiliate links

If you click on a link and make a purchase, I may receive a commission at no extra cost to you.

How to Use the GPT for Sheets and Docs Extension

Although OpenAI does not have a ChatGPT extension for spreadsheet apps, you can still enjoy GPT technology within Google Sheets using third-party applications that interface with OpenAI’s GPT API. One such third-party application is the “GPT for Sheets and Docs” extension. It is not a standalone app but a Google Sheets extension that makes it possible to use ChatGPT-like prompts wrapped in functions within your spreadsheets.

No, it doesn’t require any programming knowledge to set up. The process isn’t too technical—it is relatively easy to complete. All you’ll need to get started is to grab an OpenAI API key and add the “GPT for Sheets and Docs” extension to your Google Sheets account. But how do you get an OpenAI API key?

How to Add the GPT for Sheets and Docs Extension to Google Sheets

To add the GPT for Sheets and Docs extension to your Google Sheets:

1. Open a new Google Sheets spreadsheet on your computer.
2. Locate and click on Extensions > Add-ons > Get Add-ons.
   
3. Up next, you should be taken to the Google Workspace Marketplace. Locate the search bar in the top right corner of the screen and search for GPT for Sheets and Docs.

4. Click on the first extension, as seen in the screenshot above. On the next page, click on Install and then Continue.

5. Select the Google account associated with your Google Sheets account and then tap Allow on the permission prompt to grant the extension access it needs to modify your spreadsheets.

6. After the installation, click Next and tap Done on the post-installation screen to complete the installation process.

With that done, the extension will be added to your Google Sheets account. However, to use GPT-powered functions and formulas within Google Sheets, you’ll need an OpenAI API key to activate the extension within Google Sheets.

How to Create an OpenAI API Key

To use the GPT for Sheets and Docs extension, you’ll need an API key from the OpenAI website. Here’s how to create an OpenAI API key:

1. Visit OpenAI and sign up if you don’t already have an account.
2. Once you’ve created an account, log in, and navigate to OpenAI’s API keys page .
3. Up next, locate and click on the Create new Secret key button at the center of the page.
4. If all goes well, a pop-up with your API keys and an “API Key Generated” notification should appear on your screen. Next, tap the green copy button beside your API key to copy the key to your clipboard.
   

Please make sure to keep a secure copy of your API key, as it cannot be recovered if lost. Additionally, do not share your API keys with third parties, as this may result in restrictions being placed on your access to OpenAI’s GPT API in case of any misuse by malicious third parties.

Once you’ve grabbed a free API key, you must add it to the GPT for Sheets and Docs extension on your Google Sheets account.

1. Open a new spreadsheet or reload the currently opened spreadsheet file.
2. Navigate to Extensions > GPT for Sheets™ and Docs™ > Set API key.
3. Paste the previously generated API key into the API input field and tap on Check. If it is a valid API key, you should get a “Your OpenAI API key is valid!” notification.
   
4. Tap on the Save API key to proceed.

With those steps completed, you’re almost done with the setup process. The last step is to activate the GPT for Sheets and Docs extension. To do this, navigate to Extensions > GPT for Sheets and Docs > Enable GPT functions​​​​​​.

What Can You Do With GPT-Powered Google Sheets?

Are you done setting up the Google Sheets extension? What exciting things can you do with GPT-powered Google Sheets?

Well, the possibilities are endless.

1. Generate Text

With GPT-powered Google Sheets, you can use regular ChatGPT prompts and get similar replies to ChatGPT right within Google Sheets. For instance, if you want to populate cell C3 with a story about Snapchat, you could type the formula =GPT(“Write a short story about Snapchat”) right inside the cell.

Screenshot was taken by the author, Maxwell Timothy

Alternatively, you could type out the prompt on cell A1 and reference the cell in your formula using the formula =GPT(A1) to achieve a similar result.

Of course, we are dealing with a spreadsheet, so you probably need your results to span multiple cells. To do that, you can use the =GPT_LIST function. An example could be =GPT_LIST(“write five creative taglines for an ice-cream shop”) or =GPT_LIST(A1).

2. Translate Text

With the GPT for Sheets and Docs extension, you can use ChatGPT translation prompts within Google Sheets. You can achieve this using the GPT_TRANSLATE function or wrapping a typical ChatGPT translation prompt around the base GPT function. This could be in the form of =GPT(“ translate ‘Bonjour’ to English “) or GPT_TRANSLATE([text_to_translate], [target_language], [source_language]).

3. Summarize Text

To summarize text, you can either wrap a summarization prompt with the base =GPT function or use the =GPT_SUMMARIZE function. For instance, =GPT_SUMMARIZE(C44) will summarize the content of cell C44 into the active cell.

4. Classify Text

GPT for Sheets and Docs’s =GPT_CLASSIFY addsclassification and labeling functionality to Google Sheets. There are a lot of use cases for this function. Say, you have a list containing fruit names, female names, male names, animal names, and names of currencies; you can use the function to label each item automatically. You can also use it to perform sentiment analysis on a list of sentences.

For instance, =GPT_CLASSIFY(D6,E4) will use the category labels provided in cell E4 to classify the content of cell D6. In the example below, we used the red-colored text in cell E4 to label cells D6 to D8.

5. Generate Data Tables

Spreadsheets and tables go together like peas and carrots. The =GPT_TABLE function makes it incredibly easy to create tables in Google Sheets. Think of any data table you need, and just ask the function to create it–that’s it. So, say, you need a table of some of the best Korean TV shows, their theme, and IMDB rating. This is a dataset that would be time-consuming to put together.

However, =GPT_TABLE can produce the table in 3 seconds. You’d have to do something like =GPT_TABLE(“20 of the best Korean TV shows, their IMDB rating and three words to describe their theme”). We ran the function, and here’s what we got.

6. Copy Text Formatting

When working on a spreadsheet, some formatting is a bit too loosely defined or arbitrary for Google Sheets to replicate. For cases like this, the =GPT_FILL can be a lifesaver. To use the function, demonstrate a few examples of what you need to be done and invoke the =GPT_FILL to copy the formatting. Below is a video that demonstrates how to use the function.

7. Extract Data

If you are working with a lot of mixed-up data or text, you can extract certain data types into assigned cells using the =GPT_EXTRACT function. For instance, you could paste a CSV file in cell B23 and use =GPT_EXTRACT(B48, C48) to extract email addresses from the CSV text to the current cell. In the function, the C48 references a cell that contains the data type to extract, in this case, email addresses.

Boost Your Productivity With ChatGPT Prompts in Google Sheets

If you are a fan of ChatGPT and Google Sheets, you’ll find GPT for Sheets and Docs a useful addition to your productivity tools. With it, you can do the things you’d typically do with ChatGPT in your spreadsheets.

There’s no fixed list of what you can achieve with GPT for Sheets. What you do with it all boils down to how imaginative you can be with prompting and using the built-in GPT functions.

SCROLL TO CONTINUE WITH CONTENT

So, what if we could invoke the powers of ChatGPT within a spreadsheet? Rather than thinking up complex formulas, what if you could provide instructions in English and let ChatGPT do the heavy lifting?

Sounds good, right? Here’s how to integrate GPT technology into Google Sheets and get your spreadsheet done effortlessly.

Also read: