How to use ChatGPT inside Google Sheets

As technology continues to advance, the use of artificial intelligence (AI) is becoming more and more prevalent in our everyday lives. Chatbots recently gained popularity over the internet after the release of OpenAI’s Generative Text Bot ChatGPT. These programs use natural language processing (NLP) to communicate with users and provide assistance or answer questions. ChatGPT is based on the GPT-3.5 architecture, and it can be used within Google Sheets to generate text.

In this tutorial, we will walk through how to use ChatGPT in Google Sheets. We will start by setting up our Google Sheet and integrating the ChatGPT code. Then we will demonstrate how to use ChatGPT to generate text based on different prompts.

Step 1: Setting up the Google Sheet

The first step is to create a new Google Sheet. Once you have opened the sheet, go to “Extensions” in the menu bar and select “App Script.” This will open a new window where you can write and edit scripts.

In the script editor remove the default myFunction() code and paste the following code:

const SECRET_KEY = "YOUR-API-KEY"; // Replace with your OpenAI's API Key
const MAX_TOKENS = 100; // Shows output from ChatGPT upto 100 characters, increase if needed
const MODEL_NAME = "gpt-3.5-turbo"; // Using ChatGPT 3.5 Turbo Model
const MODEL_TEMP = 0.5; // Set the temperature of GPT for relevant output

function GPT(prompt, temperature = MODEL_TEMP, model = MODEL_NAME) {
  const url = "https://api.openai.com/v1/chat/completions";
  const payload = {
    model: model,
    messages: [
      { role: "system", content: "" },
      { 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();
}

This code sets up the parameters for the ChatGPT chatbot, including the model name, the maximum number of tokens, and the temperature. It also includes the function GPT, which takes a prompt as input and generates a response using the ChatGPT model.

Step 2: Get an API Key from OpenAI

To use ChatGPT in Google Sheets, you will need an API Key from OpenAI. Follow these steps to get your API Key:

1. Go to the OpenAI website and create an account if you haven’t already done so. (Click Here)
2. Once you’re logged in, and by chance system didn’t redirected you to the API Keys page Click Here to go to the API page.
3. Choose the API plan that you want to use and follow the instructions to set up your payment method.
4. After you’ve completed the payment setup, you will be taken to the API dashboard where you can view your API Key.
5. If it didn’t created API key automatically just press the button “Create new secret key” and generate your API key from there. Make sure to keep it saved in a safe place as it will be displayed once.
6. Copy the API Key and paste it into the SECRET_KEY variable in the ChatGPT code and save it.

Once you have your API Key set up, you’ll be able to use ChatGPT to generate text in Google Sheets.

Step 3: Use ChatGPT to generate text

Now that we have set up our Google Sheet and integrated the ChatGPT code, we can start using it to generate text. To do this, we simply need to enter a prompt into a cell and call the GPT function.

To call the function, go to the cell where you want the output of your prompt from ChatGPT and call the ChatGPT function using following formula.

=GPT("YOUR CELL NUMBER OR PROMPT")

For example, if you want to get prompt value from the cell A2, we will call this formula as follows.

=GPT(A2)

If you don’t want to use prompt from any particular cell and just want the ChatGPT output via manual prompt, replace the cell name with your prompt, for example enter the prompt “What is the meaning of life?” into the formula.

=GPT("What is the meaning of life?")

It will call the function ChatGPT which will send the request to OpenAI’s GPT 3.5 api and in return will put the output value into the cell.

Step 4: Adjusting the temperature

One of the parameters we set up in our ChatGPT code is the temperature. This parameter controls how “creative” the generated text is. A lower temperature generates text that is more predictable and similar to the training data, while a higher temperature generates text that is more creative and unpredictable.

To adjust the temperature, we simply need to change the value of the MODEL_TEMP variable in the code. For example, if we set MODEL_TEMP to 1.0, the generated text will be more creative and unpredictable.

const MODEL_TEMP = 1.0; // Set the temperature of GPT for relevant output

Step 5: Generating longer text

By default, the maximum number of tokens in the generated text is set to 100. This corresponds to around 100 characters, but we can increase this value to generate longer text.

To do this, we simply need to change the value of the MAX_TOKENS variable in the code. For example, if we set MAX_TOKENS to 4000, the generated text will be longer and more detailed.

const MAX_TOKENS = 4000; // Shows output from ChatGPT upto 100 characters, increase if needed

Note: The longer output you will get, the more $ you will be spending, use with caution.

Conclusion

In this tutorial, we have demonstrated how to use ChatGPT in Google Sheets to generate text based on different prompts. We started by setting up our Google Sheet and integrating the ChatGPT code, and then demonstrated how to use ChatGPT to generate text based on different prompts. We also showed how to adjust the temperature and generate longer text. With this tutorial, you can start using ChatGPT to generate text for a wide range of use cases inside Google Sheets.