FREE GPT 4 Sheets Alternative

July 2024 update: Added GPT-4o and temperature controls to the script.

 

The popular Google Sheets Extension, GPT 4 Sheets, recently changed its business model.

You used to be able to use it for free just by plugging in your API key.

Now you need to pay a monthly subscription on top of that.

That’s why I created this script. It replaces the basic GPT 4 Sheets function, allowing you to run prompts within Google Sheets for free. (Minus API credit costs)

All you need to do is plug in the script and your OpenAI API key and you’re good to go.

Credit and disclaimer:

Thanks to the talented E-commerce consultant Daniel Kassman for getting the ball rolling on this. He created the original version of this script and I helped him get it up and running.

Also, I want to say I don’t think there is anything wrong with GPT 4 Sheets wanting to charge a subscription fee. While their free version was super useful, they are a business at the end of the day and they need to bring in revenue to grow.

 

Step 1: Create a new Google Sheet and go to Extensions –> Apps Script

This Apps Script menu will open a new tab where you will see a blank script ready for customization. Name your script “GPT Script” or similar.

 

Step 2: Copy/paste this script into the Apps Script editor.

Replace the existing text with this script:

 

function GPT(Input) {
  const GPT_API = "AAAAAAAAAAAAA";  // Replace with your actual API key
  const BASE_URL = "https://api.openai.com/v1/chat/completions";

  const headers = {
    "Content-Type": "application/json",
    "Authorization": `Bearer ${GPT_API}`
  };

  const options = {
    headers,
    method: "POST",
    muteHttpExceptions: true,
    payload: JSON.stringify({
      "model": "gpt-4o",
      "messages": [
        {
          "role": "system",
          "content": "You are a helpful assistant powered by OpenAI's GPT-4o model."
        },
        {
          "role": "user",
          "content": Input
        }
      ],
      "temperature": 0.8,
      "top_p": 1,
      "frequency_penalty": 0,
      "presence_penalty": 0
    })
  };

  try {
    const response = UrlFetchApp.fetch(BASE_URL, options);
    const responseJson = JSON.parse(response.getContentText());

    Logger.log(responseJson);  // Log the entire response for debugging

    if (responseJson.error) {
      Logger.log("Error from OpenAI: " + responseJson.error.message);
      return "Error: " + responseJson.error.message;
    } else {
      // Check the model used in the response
      if (responseJson.model && responseJson.model.startsWith("gpt-4")) {
        return responseJson.choices && responseJson.choices.length > 0 ? responseJson.choices[0].message.content : "No response";
      } else {
        return "Error: The response is not from the GPT-4 model.";
      }
    }
  } catch (e) {
    Logger.log("Error: " + e.message);
    return "Error: " + e.message;
  }
}

Be sure to replace the API key with your actual OpenAI API key. Here’s a guide on how to find and create an OpenAI API key.

 

How to use ChatGPT / OpenAI Prompts within Google Sheets

To use your new GPT formula, enter =GPT(“prompt”) in a cell and replace it with your prompt.

You can also call in other cells to create more complex outputs like this: =GPT(“what does this say? “&B4)

You can also chain multiple cell values like this: =GPT(“what does this say? “&B4&”And what does this say?” &B5)

 

Customization options for the script:

You can change the chat model from GPT 3.5 turbo to GPT 4 turbo or any available OpenAI Chat Model.

Simply change line 15 where it says “model”: “gpt-3.5-turbo-0125” to “model”: “gpt-4-0125-preview “.

Here’s a list of currently available OpenAI Chat models you can use.

You can also change the system message on line 19 from the default. Just change “You are a helpful assistant” to whatever you like.

Be sure to hit the little save icon to ensure your changes work. Then you can close the Apps Script tab and return to your sheet to run your prompts.

 

Get more Scripts, apps, and tools

This script originated from our community in the AI SEO Academy. Where I show people how to build and leverage AI tools for SEO.

Join the academy to get more cool tools, scripts, and resources just like this one.