How to Connect ChatGPT to Google Sheets

With so many different tech innovations, it’s rare for a new technology to stop everyone in their tracks. But that’s exactly what ChatGPT did when it was introduced in November 2022.

Since its release, ChatGPT has become a household name, and has set off an AI arms race among the biggest names in tech. The chatbot has ushered in a new golden age of AI.

Although the ChatGPT is the hottest new thing, it also integrates perfectly with a technology that’s been around forever: spreadsheets.

Spreadsheets and ChatGPT make perfect sense together. These technologies improve productivity, and when they’re combined, they can supercharge business efficiencies.

But how can you connect ChatGPT to your spreadsheet? In this blog, we’ll identify two ways to connect ChatGPT to Google Sheets:

1. Coefficient – A free solution that automatically connects ChatGPT to Google Sheets, so you can use it on your business data.
2. Google Apps Script – Create your own pay-as-you-go DIY solution with Google Apps Script and your OpenAI API secret key.

Read the following blog for full step-by-step walkthroughs of each method!

   Video Walkthrough: How to Use ChatGPT in Google Sheets

Coefficient: Connect ChatGPT to Google Sheets in a Single Click

Coefficient is a free solution that automatically connects ChatGPT to Google Sheets without requiring an API key. Here’s a guide on how to connect ChatGPT to Google Sheets using Coefficient.

Open a new Google spreadsheet. Click Extensions on the Google Sheets menu. Choose Add-ons -> Get add-ons.

This will display the Google Workspace Marketplace. Search for “Coefficient”. Click on the Coefficient app in the search results.

Coefficient will ask you for permissions on your Google account. Accept the permissions to install.

Once installation is finished, return to Extensions on the Google Sheets menu. Coefficient will appear as an add-on.

Now launch the app. Coefficient will run on the sidebar of your Google Sheet.

To access ChatGPT functionality in your spreadsheet, all you need to do is leverage Coefficient’s GTPX functions.

Within the Coefficient sidebar, you can get an overview of the GPTX functions by selecting the GPT Copilot tab.  

Then select Use GPT from sheet.

This will present a list of all the GPT functions now available in your spreadsheet.

Note: this tab explains the functions. You do not need to open this tab to use the GPTX functions.

You can start using ChatGPT in your spreadsheet right away with these GTPX functions. Here’s a look at some of the GTPX functions.

GPTX Function

Video Example: Watch the GPTX Function in Action

GPTX_LIST Function

GPTX_TABLE Function

GPTX_FILL Function

GPTX_MAP Function

Video Example: Watch the GPTX_MAP Function in Action

GPTX_EDIT Function

Here are some of the other GPTX functions you can leverage in Google Sheets with Coefficient:

• GPTX_FORMAT – Converts input values into the specified format
• GPTX_TAG – Applies one or more tags matching a piece of text
• GPTX_CLASSIFY – Classifies text according to a given set of labels/categories
• GPTX_EXTRACT – Extracts desired information from the input text
• GPTX_SUMMARIZE – Summarizes the input text according to the given format
• GPTX_TRANSLATE – Translates the input text into the specified language
• GPTX_CONVERT – Converts the input text into the specified structured format
• GPTX_CODE – Generates code which performs the specified task

Once you install Coefficient, you can start implementing these GPTX functions right away. Try them for yourself!

Google Apps Script: Build a DIY Solution

You can also opt for do-it-yourself, and connect ChatGPT to Google Sheets manually via Google App Script

This method will require an API secret key, and a paid OpenAI account, as well as several additional steps. Check out the step-by-step guide below to learn how.

First, create a new Google spreadsheet. Then navigate to Extensions -> Apps Script.

Google Apps Script is a JavaScript-based scripting platform that enables light-weight app development in Google Sheets.

Free AI-Powered Tools Right Within Your Spreadsheet

Supercharge your spreadsheets with GPT-powered AI tools for building formulas, charts, pivots, SQL and more. Simple prompts for automatic generation.

Rename the project before continuing. Now copy-and-paste the following code into your Apps Script project:

const SECRET_KEY = 'key';

/**
 * Send prompt to GPT-3.5 to get an response from the AI engine
 *
 * @param {string} prompt Prompt to submit
 * @param {number} temperature (Optional) Sampling temperature (0-2), with 0 being more focused and deterministic, while 2 is more creative and random. Defaults to 1.
 * @param {number} tokens (Optional) Max tokens to use for request (max of 4096). This will determine both the usage and the length of the response.
 * @return Completion returned by GPT-3.5
 * @customfunction
 */

function GPTPROMPT(prompt, temperature = 1, tokens = 2000) {
  const url = "https://api.openai.com/v1/chat/completions";
  
  const payload = {
    model: "gpt-3.5-turbo",
    messages: [{"role":"user",'content': prompt}],
    temperature: temperature,
    max_tokens: 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]['message']['content'];
}

 Once you’ve copy-and-pasted the code, your project should look like this:

To access ChatGPT functionality, you’ll need to insert the Secret Key from your OpenAI account into the SECRET_KEY variable at the top of your Apps Script code.  

Head over to your OpenAI account. If you don’t have an OpenAI account, make sure to register one (here’s how). Once the registration process is complete, navigate to your account’s API keys page.

Click ‘Create new secret key’.

Name your secret key. Then OpenAI will generate a new secret key.

You will not be able to view the secret key again, so make sure to record it. Copy the secret key and click ‘Done’ to save the key.

Now paste secret key in the SECRET_KEY variable.

Save your project when you’re finished.

To use the secret key, you must sign up for a paid OpenAI API account. Go to your OpenAI account and navigate to Billing -> Overview. 

Select Set up paid account and provide your credit card.

Return to Google Sheets. You can now use the custom GPTPROMPT function, defined in the Apps Script code, in your spreadsheet.

Let’s test out some sample prompts:

Write a GPTPROMPT formula referencing one of the prompts. GPT’s answer will appear within the cell.

Now drag the formula down the rest of the column to generate the remaining answers.

However, keep in mind that this Apps Script method has a number of limitations:

• The Apps Script function can only answer basic prompts, and does not include any of the other GPTX functions bundled with Coefficient.
• You’ll have to keep updating and maintaining the script to ensure it continues to work.
• And the worst part: each prompt that you answer in your spreadsheet is charged to your credit card.

That’s why, although many DIY ChatGPT spreadsheet connections have emerged, Coefficient remains your best bet.

Coefficient allows you to access ChatGPT functions in your spreadsheet for free. You can install Coefficient in just a single click. And there’s absolutely no coding required.  

ChatGPT in Google Sheets: Easily Access Generative AI in Your Spreadsheet with Coefficient

Coefficient allows you access the full functionality of ChatGPT in Google Sheets for free. Install Coefficient to start leveraging the full potential of generative AI in your spreadsheet right now.

Get started with Coefficient for no cost and bring the ChatGPT revolution to your Google Sheets today.