Integrating OpenAI’s powerful GPT models into Google Sheets can supercharge your spreadsheets with AI capabilities. There are existing sheet extensions (Add-ins) on the Google Marketplace but they cost on top of the API cost. 

In this how-to tutorial, we’ll walk through embedding a basic GPT functionality in Google Sheets using Google Apps Script and OpenAI's API. 

By the end, you’ll have a custom =GPT() function that calls the Open AI API directly from any cell with a formula. 

Don’t worry if you’ve never coded before – we’ll keep things friendly and simple. 

Key Objectives

• Obtain an OpenAI API key
• Use the provided code for a custom `=GPT()` function in App Script of a Google Sheet
• Use the function to automate GPT in your spreadsheet

Let’s get started!

Step 1: Get your OpenAI API key

This tutorial demonstrates how to call OpenAI’s GPT-4o—currently among the fastest and most knowledgeable AIs — but the same method also works with GPT-3.5-turbo or any newer OpenAI Chat model.
To use GPT-4o via API, you need a secret API key from OpenAI. Here’s how to obtain one:

Log in to OpenAI: Go to OpenAI’s platform and sign in with your account (create one if you haven’t).

Navigate to API Keys:

① ➡ Click on the settings icon (top-right corner) and then ② ➡ select “API keys” from the left menu. This will take you to the API keys management page.

Create a new key: On the API Keys page, ③ ➡ click the button “Create new secret key”. The below dialog will appear.

Enter any name and select the default project. Keep Permissions “All” and click the button “Create secret key” which will bring you to the next screen.

It will show your new API key (a very long string starting with "sk-..."). Copy this key now and save it somewhere secure.‍

Important: You won’t be able to see it again once you close the dialog!

With your API key in hand, you’re ready for the next step and connect Google Sheets to GPT-4o.

Step 2: Open the Google Sheets Apps Script editor

Next, we’ll create a Google Sheet and open the script editor to write our custom function:

Create a new Google Sheet: Go to Google Sheets and open a new blank spreadsheet.

Open Apps Script: In the Google Sheet, click on the menu Extensions > Apps Script. This will launch the Google Apps Script editor in a new tab.

When the Apps Script editor opens, you should see a web IDE interface with a file named Code.gs by default.

Prepare the editor: If there’s any starter code in the editor (an empty “function myFunction()” might be there by default), feel free to delete it. We’ll replace it with our own code.

Step 3: Write the GPT function in Apps Script

In this step, we’ll add a custom function called GPT(prompt, temperature) that calls the OpenAI API. The function will take a text prompt and a temperature value, send the prompt to GPT-4o, and return the generated response right into your spreadsheet cell.

Paste the code: Copy the script below and paste it into the Code.gs script file, replacing any existing content:

// Custom function to call GPT-4 API from Google Sheets
const API_KEY = "sk-XXXXXXXX";  // <-- Paste your OpenAI API key here


function GPT(prompt, temperature) {


  // Prepare the API request payload for GPT-4
  const url = "https://api.openai.com/v1/chat/completions";
  const data = {
    model: "gpt-4o",
    messages: [ { role: "user", content: prompt } ],
    temperature: temperature || 0.7  // default to 0.7 if not provided
  };


  // Set up the request options, including the API key for authorization
  const options = {
    method: "post",
    contentType: "application/json",
    headers: { "Authorization": "Bearer " + API_KEY },
    payload: JSON.stringify(data)
  };


  // Make the HTTP request to OpenAI
  const response = UrlFetchApp.fetch(url, options);
  const result = JSON.parse(response.getContentText());


  // Return the text of the first answer choice
  return result.choices[0].message.content;
}

‍

What this code does

This code creates a function called GPT that you’ll use in your sheet. It:

• Takes a prompt (the text you want GPT to respond to) and a temperature (a number between 0 and 1 that controls creativity—lower is more predictable, higher is more random).
• Sends your prompt to OpenAI’s GPT-4o model using your API key.
• Gets the response and puts it in your spreadsheet.

Code Deep Dive (optional)

• Lines starting with // are only comment lines and not needed.
• We define a constant API_KEY and set it to your OpenAI API key (replace the placeholder with the key you copied in Step 1).
• The GPT function takes two parameters: prompt (the text prompt or question) and temperature (a number between 0 and 1 controlling the creativity of GPT’s response).
• Inside the function, we create a data object following OpenAI’s Chat Completions API format, specifying the model ("gpt-4o"), the user message (our prompt), and the temperature.
• We then set up options for the HTTP request, including the authorization header with our API key and the JSON payload.
• UrlFetchApp.fetch() is a Google Apps Script service that lets us call external URLs (in this case, OpenAI’s API endpoint). We POST our request to the chat completions API.
• We parse the response and extract result.choices[0].message.content, which is the text of GPT’s answer. This value is returned by our function, so it will appear in the Google Sheet cell that called =GPT().

For more information including what temperature means you can consult the OpenAI API Docs here: https://platform.openai.com/docs/api-reference/chat

Like mentioned our example uses the GPT 4o model. More models can be found here: https://platform.openai.com/docs/models but be reminded that some future models might require different code or other parameters.

Save the project: Click the floppy disk Save icon (💾) or press Ctrl+S (Cmd+S on Mac) to always save all changes in your project script files. Optionally you can also give the script file and the whole App Script (which can contain multiple script files) a name (e.g., “GPT-4 Sheet Integration”).

The screenshot above shows the Apps Script already pasted into the editor ① ➡. We named the App script project “GPT-4 Sheet Integration” and we need to click save icon ② ➡. We kept the script file named Code.gs, but you can rename it if needed—just make sure to keep the .gs extension.

‍Authorize the script (Not always needed): We do not need to run the script in the App Script editor. We wil just use our new formula in Step 4 right inside sheets, Google may prompt you to authorize the script to connect to an external service (since it’s calling the OpenAI API). Follow the prompts to allow access.

That’s it for the coding part! The script is short and straightforward by design – we haven’t added error handling or advanced features, to keep it easy to understand. Now, let’s use our new function in the spreadsheet.

Step 4: Use the =GPT() function in your Google Sheet

Return to your Google Sheet. We will now use the custom function GPT just like any built-in spreadsheet formula:

‍Basic usage: In a cell, type an equals sign = and start typing GPT. You should see GPT appear in the formula suggestions. For example, try:

=GPT("Hello, GPT-4! How are you today?", 0.5)  

and press Enter.

Wait for a response: You’ll see a “Loading…” indicator in the cell for a moment. Then, the cell should populate with GPT-4’s answer to your prompt.
Congrats – you just brought AI into your sheet!

The formula syntax is =GPT(prompt, temperature).

You can supply the prompt as a text string in quotes or by referencing another cell, and temperature is optional (if omitted, our code defaulted it to 0.7).

Using cell references for batch prompts

One of the best features of having GPT in Sheets is the ability to fill down the formula for multiple prompts. To batch process multiple prompts, you can put different prompts in a column and use the GPT function in an adjacent column. For example:

‍Concatenate your prompt: Concatenate your prompt from columns A, B, and C by defining the AI’s role, task, and specific details for each row. Then, ① ➡ in cell D2, combine them within our GPT function, like so:

=GPT(A2&B2&C2)

Drag the formula down: Finally, drag down to batch process GPT calls by ② ➡ hovering your mouse over the bottom-right corner of cell D2 until a crosshair appears, and then dragging it down to cover the next two rows.

Watch the magic: After a brief moment, D3 and D4 will also have replies by the AI. Google Sheets will call the GPT function for each row, and you’ll see answers appearing in column B for each prompt.
‍

You’ve now created automated GPT responses!

Step 5: The benefits – plus three ways to use GPT in bulk

By embedding your GPT prompts directly in Sheets, you 

• eliminate repetitive copy-paste tasks from the Browser ChatGPT
• keep prompts neatly organized and
• unlock countless automation possibilities.
  
  

This straightforward approach can spark fresh ideas for real-world workflows in nearly any domain.

‍

Bulk Translation with more control

Imagine you have a list of phrases or product names in English (column A) and you need German translations in column B but using informal “Du” instead of “Sie”.

Formula:

=GPT("Translate to German but use informal Du instead of formal Sie: " & A2, 0.3)  

‍

Sentiment classification at scale

Suppose you have hundreds of customer reviews or feedback comments in column A, and you want to quickly judge each as Positive or Negative. GPT-4o can categorize sentiment for each comment:

Formula:

=GPT("Is this review positive or negative? Review: " & A2, 0)  

Setting temperature to 0 makes the output deterministic (GPT will be more likely to give a consistent answer).

‍

Text rephrasing or formalizing at scale

If you have a list of sentences in column A that need to be rewritten – say turning informal phrases into professional tone – the GPT function can do this for every entry:

Formula:

=GPT("Rephrase in a polite, formal tone: " & A2, 0.7) 

‍

Keep in mind token limits (very long prompts or outputs might hit API limits) and be aware of your OpenAI usage to manage costs. And of course, handle your API key carefully to keep it secure.

Wrapping Up

In this guide, we built a custom Google Sheets function that taps into OpenAI’s GPT-4o model to generate content on the fly. You learned how to obtain an API key, write a minimal Apps Script function, and use it just like any other spreadsheet formula – but with the power of AI. We also explored 3 use cases where batch-processing with GPT can save you tons of time, whether you’re translating text, analyzing sentiment, or text rephrasing.

Congratulations on embedding GPT-4o into Google Sheets! 🎉 You’ve bridged the gap between spreadsheet data and AI, opening up endless possibilities for automation and insight. 

Happy prompting, and enjoy your new AI-augmented workflow!