ChatGPT API to Google Sheets with Google Apps Script

In this tutorial, we will learn how to connect the ChatGPT API to Google Sheets using Apps Script, allowing you to create your own free version of GPT for Sheets.

Here is an example where we query the ChatGPT completion API inside Google Sheet.

Word of Caution

You should NEVER send your private data to OpenAI. So please don’t do that on your banking stuff or company private and sensitive data.

Requirements

You will need to have access to a OpenAI API key.

Create the Apps Script

To create your custom GPT function in Google Sheets, open Apps Script.

Go to Google Sheet > Extensions > Apps Script.

Inside the new window, add the following code to the App Script code UI. Make sure that you change the code to add your own Open AI API key.

Here I am using the gpt-4o-mini model, but you can choose your preferred model in the official doc.

Here is the code to a fantastic app script to help connect ChatGPT to Google Sheets created by Keith Mint

const SECRET_KEY = "<YOUR CHAT GPT API KEY>";
const MAX_TOKENS = 800;

function AI_ChatGPT(prompt, temperature = 0.9, model = "gpt-4o-mini") {
  const url = "https://api.openai.com/v1/chat/completions";
  const payload = {
    model: model,
    messages: [
      { role: "system", content: "You are a helpful assistant." },
      { role: "user", content: prompt },
    ],
    temperature: temperature,
    max_tokens: MAX_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.trim();
}

Deploy the Apps Script Function

To make your new function available, deploy it by clicking on Deploy > New Deployment.

Choose “Web App” for the type of configuration.

Add your information and click on “Deploy”.

Authorize the Access

Next Authorize the access by following the Google OAuth prompts.

Use the New Custom GPT Function

Go back to your sheet an use the AI_ChatGPT function like this:

=AI_ChatGPT("Give me the brand name inside the following string:"&A2&")")

This is it, you have created your own custom function to fetch the ChatGPT API in Google Sheets.