The easiest way to use ChatGPT is the chat interface. But there is another way, use ChatGPT API (application programming interface) .
Why?
This is your first step to automating AI usage. For example you can take 1000-line table, send the information to ChatGPT and get the response you want directly in your table. If you would just copy paste this into ChatGPT chat tool it would most likely break. OpenAI has made the ChatGPT API accessible to everyone and you can take some time to experiment with it.
Usually, developers create apps and send their information through ChatGPT API and get back the response they will show in their app.
For this you need to know how to write code. Or at least you would have to be willing to work with ChatGPT and tease out the code you can use in your application. This is time consuming, and, in many cases, you will not be able to create a working app.
But there is a middle ground.
It is possible to connect your Google Sheets to the ChatGPT API. This way you can send information from your spreadsheet to ChatGPT and get the result back in your table.
To do that you will need to create a function in Google Sheets. For this purpose, there’s a way to write code for Google Sheets, it’s called App Script. In App Script you need to write a bit of code to connect to GPT API.
If you want to work with other AI tools check out my AI tools I use every day.
Access ChatGPT API in Google Sheets
Don’t worry, I created a Google Sheet you can copy and use the API connection without ever taking a look at the code. You can use this spreadsheet as any other, you just have a new function that can access GPT and answer your questions. The only thing you need is an OpenAI API key.
Getting access to ChatGPT API and GPT API key
First, you need to create an account to access the ChatGPT API. You can do it here.
Next, you need to go here and create your API key for accessing GPT language models. You can have as many API keys as you want. For example, different keys for various projects. But once you create the key you have to save it somewhere. If you misplace your API key, you need to make a new one.
Click this Google Sheet link, make a copy, and follow the instructions in the file.
The function works as follows: gpt4api ( prompt , data , temperature , model )
- Prompt is the question you ask from ChatGPT
- Data is any additional information you want to pass to the AI (optional)
- Temperature is the creativity of the model from 0 to 1
- Max tokens will limit the length of the answer, max depends on the model, check values here.
- Model is the version of the GPT you want to use for your query. Here you can select gpt-4o, gpt-4o-mini or others from the dropdown menu. The default value is “No model” that defaults to gpt-4o-mini so you will not waste your money when the Sheets automatically recalculates cell values. Read more about this in the “Caution!” section below.
In the Google Sheets file, I have this example:
- In the first cell I have a prompt “Please write a tweet, maximum 45 words. Topic”
- In the second cell I add specific data “How to write code with ChatGPT?” This cell is optional.
- In the third cell I have set the temperature to 1. This means the tweet would be more on the creative side.
- In the fourth, cell I have max tokens 50.
- Finally, the fifth cell contains the gpt4api function where you will get the output of the GPT model you selected.
This is just one simple example. You can create countless rows of data by just copying the example and changing the contents of the cells. Think about your business and marketing processes, you will certainly find things you can optimize with this tool.
Caution!!! Automatic recalculation in Google Sheets
One important thing to remember is that Google Sheets will recalculate every cell when you make a change on the sheet. If you have 100 cells sending API calls to GPT then the price could get high, especially when using GPT-4o model.
While the price of the API is not too steep, it can add up when you use the Google Sheets tool.
Just to protect against automatic recalculation, I made the model selection include a “No model” option. If you want to make absolutely sure you don’t waste any money on useless API queries, follow these steps:
- Make sure you select the right model in the B4 cell. “GPT Output” column will display “#ERROR!” as long as you don’t have access to the models you have selected.
- Fill out all the fields in the table with the data you want to send to GPT through the API.
- Now, select the model you want to use.
- After you get the results from OpenAI API, copy the values of the cells to a separate table or text document. (Then answers to your queries will be different after every automatic recalculation).
Using this process ensures automatic recalculations will not waste your money.
OpenAI API Price
Accessing OpenAI API is a paid service. You can find detailed ChatGPT API prices here. The price is different for GPT3 and GPT-4o and depends on other variables. But as a rule of thumb, you can calculate the price as follows:
gpt-4o
$5.00 / 1M input tokens
$2.50 / 1M input tokens
$15.00 / 1M output tokens
$7.50 / 1M output tokens
gpt-4o-2024-08-06
$2.50 / 1M input tokens
$1.25 / 1M input tokens
$10.00 / 1M output tokens
$5.00 / 1M output tokens
gpt-4o-mini
$0.150 / 1M input tokens
$0.075 / 1M input tokens
$0.600 / 1M output tokens
$0.300 / 1M output tokens
gpt-4o-mini-2024-07-18
$0.150 / 1M input tokens
$0.075 / 1M input tokens
$0.600 / 1M output tokens
$0.300 / 1M output tokens
As you can see the mini version is more than 16 times cheaper than the top model. You will have an almost real-time overview of how much money you have spent using the API.
You can set usage limits on your account, so you don’t spend all your money on AI. There’s a hard limit at which the system will stop accepting any new requests. And there’s a soft limit at which you will get an email that you have reached a certain threshold.
In the beginning, when you create your account, you will get some free credit to experiment with.
Tweaking the ChatGPT API code
If you want to tweak the code, then you can find it here:
- From Google Sheets menu “Extensions” select “App Script.”
- In the window that opens you will see the code that connects Sheets to GPT.
- You can find out more about how to use ChatGPT API here.
Now you are ready to experiment with the ChatGPT API. In some cases, you can 10x your productivity in many areas by creating an automated system that helps you tackle tedious and repetitive tasks.
You can also take a look at other AI tools for business I use every day.
The ChatGPT API code for Sheets is this
function GPT4API(inputPrompt, dataload, TEMPERATURE, MAX_TOKENS, model) {
// get ChatGPT API key
let settingSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
let SECRET_KEY = settingSheet.getRange(5, 2).getValue();
// Validate API Key
if (!SECRET_KEY) {
throw new Error("API key is missing or incorrect.");
}
// Ensure the model is set correctly
if (!model) {
model = 'gpt-4o-mini'; // default model if none is provided
}
const url = "https://api.openai.com/v1/chat/completions";
const prompt = inputPrompt + ": " + dataload;
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 = {
method: "POST",
contentType: "application/json",
headers: { Authorization: "Bearer " + SECRET_KEY },
payload: JSON.stringify(payload),
muteHttpExceptions: true, // Allows you to capture error messages
timeoutInSeconds: 60
};
try {
const response = UrlFetchApp.fetch(url, options);
const res = JSON.parse(response.getContentText());
if (response.getResponseCode() === 200) {
return res.choices[0].message.content.trim();
} else {
Logger.log("Error: " + res.error.message);
throw new Error("API Error: " + res.error.message);
}
} catch (e) {
Logger.log("Request failed: " + e.message);
throw new Error("Failed to connect to OpenAI API: " + e.message);
}
}
This example integrates ChatGPT API into Google Sheets, but you can also use Gemini 1.5 Pro with 2 million token context window.
What can you do with ChatGPT API automation
Here’s what you can do with ChatGPT API just by using the shared Google Sheet file in this post:
- Automated product descriptions based on features
- Social media post based on URLs
- Customer support responses
- Blog post outlines or summaries
- Job description creation based on various inputs (read more about AI in HR)
- Survey or questionnaire result analysis
- Marketing copy for ads
- Report generation based on data
- Bulk summarization of articles or documents
Let us know in the comments what use cases are most interesting for you.