How to Connect ChatGPT to Google Sheets

Last Modified: July 21, 2023 - 7 min read

Hannah Recker

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.

Extension-1-1024x409

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

Pasted image 0

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

Untitled-24

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

Untitled-21-1024x394

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.  

Pasted image 0

Then select Use GPT from sheet.

Pasted image 0

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

Pasted image 0

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

Syntax =GPTX(prompt)
Example=GPTX(”Write a funny email subject line for job application”)
OutputThe answer to your prompt will return in a single cell
Screenshot 2023-04-12 at 3.03.11 pm

Video Example: Watch the GPTX Function in Action

GPTX_LIST Function

Syntax =GPTX_LIST (prompt)
Example=GPTX_LIST(”List 5 outrageous excuses for showing up late to a meeting”)
OutputThe number of responses you asked for in your prompt, listed with one response per cell
Screenshot 2023-04-12 at 3.17.23 pm

GPTX_TABLE Function

Syntax =GPTX_TABLE(prompt, [header_row])
Example=GPTX_TABLE(”Top 10 most populous countries”, B1:E1)
OutputReturns responses in a table format
Screenshot 2023-04-13 at 4.39.38 pm

GPTX_FILL Function

Syntax =GPTX_FILL(examples_rows, [incomplete_rows])
Example=GPTX_FILL(A2:B6, A7:A10)
OutputBased on the provided examples, the blank portion of the range will be populated
Screenshot 2023-04-14 at 3.54.04 pm

GPTX_MAP Function

Syntax =GPTX_MAP(search_keys, inputs)
Example=GPTX_MAP(A2:A8, B2:B10)
OutputReturns the most similar search key for each input value 
Screenshot 2023-04-13 at 5.01.51 pm

Video Example: Watch the GPTX_MAP Function in Action

GPTX_EDIT Function

Syntax =GPTX_EDIT(text, [task])
Example=GPTX_EDIT(”Ar they’re Ny errrs in thssi setnence”)
OutputEdited text returned in a single cell

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.

spreadsheet ai
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.

Pasted image 0

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:

Pasted image 0

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.  

Pasted image 0

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’.

Pasted image 0

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

Pasted image 0

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.

Pasted image 0

Save your project when you’re finished.

Pasted image 0

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. 

Pasted image 0

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:

Pasted image 0

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

Pasted image 0

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

Pasted image 0

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.  

Sync Live Data into Your Spreadsheet

Connect Google Sheets or Excel to your business systems, import your data, and set it on a refresh schedule.

Try the Spreadsheet Automation Tool Over 500,000 Professionals are Raving About

Tired of spending endless hours manually pushing and pulling data into Google Sheets? Say goodbye to repetitive tasks and hello to efficiency with Coefficient, the leading spreadsheet automation tool trusted by over 350,000 professionals worldwide.

Sync data from your CRM, database, ads platforms, and more into Google Sheets in just a few clicks. Set it on a refresh schedule. And, use AI to write formulas and SQL, or build charts and pivots.

Hannah Recker Growth Marketer
Hannah Recker was a data-driven growth marketer before partying in the data became a thing. In her 12 years experience, she's become fascinated with the way data enablement amongst teams can truly make or break a business. This fascination drove her to taking a deep dive into the data industry over the past 4 years in her work at StreamSets and Coefficient.
500,000+ happy users
Wait, there's more!
Connect any system to Google Sheets in just seconds.
Get Started Free

Trusted By Over 50,000 Companies