This guide will explain how to use ChatGPT in Google Sheets through the OpenAI API.
OpenAI’s GPT-3 language model API is a great way to generate text using artificial intelligence.
ChatGPT is a new chatbot from OpenAI that produces human-like responses. Because of the number of possible use cases for ChatGPT, many users are already trying to add ChatGPT to their Google Sheets workflow.
Since ChatGPT’s API is still unavailable to the public, users will have to find another way to integrate the language model programmatically in Google Sheets.
ChatGPT’s algorithm was built on top of OpenAI’s GPT-3 language model. Luckily, OpenAI already provides an API for GPT-3. First-time users will have access to a limited amount of credits that will enable them to call the API.
While access is limited to those credits, users can still send hundreds or even thousands of requests before paying for more credits.
GPT-3 is powerful enough to understand moderately complex prompts and instructions. In this guide, we will create a custom function that allows Google Sheets users to interact with the OpenAI API as if it were another built-in function.
A Real Example of Using ChatGPT in Google Sheets
The following section provides an example of how to use GPT-3 to generate text in Google Sheets. We will also explain the formulas and tools used in these examples.
First, let’s take a look at a real example of ChatGPT’s algorithm being used in a Google Sheets spreadsheet.
Since ChatGPT’s API is not yet available to the public, we must use OpenAI’s GPT-3 models to generate text. In the example below, we used GPT-3 to enumerate gift ideas for specific recipients.
The table above was generated using the following custom formula:
=OpenAI(B3&A6)
The sole argument in the formula above is a string that will be used as a prompt for the GPT-3 language model. In this case, we’re instructing the model to return a list of appropriate gift ideas for someone’s mother.
Before we explain how the OpenAI API was used, let’s take a look at the App Script code to create the custom function:
const SECRET_KEY = <INPUT YOUR SECRET KEY HERE>;
const MAX_TOKENS = 100;
/**
* Uses OpenAI's GPT-3 to generate text based on a prompt
* @param {string} prompt Prompt
* @param {number} temperature (Optional) Temperature
* @param {string} model Model
* @return Completion
* @customfunction
*/
function OpenAI(prompt, temperature = 0.5, model = "text-davinci-003") {
const url = "https://api.openai.com/v1/completions";
const payload = {
model: model,
prompt: 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].text.trim();
}
The code above is an Apps Script file that sets up a custom function that sends a request to the OpenAI API. The function has three arguments: the prompt, the temperature, and the model.
The prompt refers to the string of text that will instruct GPT-3 on what to output. For example, the prompt ‘Name an animal.” will return an output such as ‘Dog’ or ‘Octopus’. Our code sets the maximum length of the output to 100 tokens or about 100 characters.
The temperature and model arguments are both optional. The temperature controls how random the result will be. A temperature of 0 will result in a predictable output, while a temperature of 1 will result in a highly random output. By default, the function uses a temperature of 0.5.
The model argument indicates which GPT-3 model to use. By default, our function uses ‘text-davinci-003’, the most powerful model currently available.
Do you want to take a closer look at our examples? You can make your own copy of the spreadsheet above using the link attached below.
If you’re ready to try out OpenAI’s language models in Google Sheets, head over to the next section to read our step-by-step breakdown on how to do it!
How to Use ChatGPT in Google Sheets
This section will guide you through each step needed to use ChatGPT’s language model in Google Sheets. You’ll learn how to retrieve your OpenAI API key and how to use Apps Script to connect to the API itself.
- Before creating the custom formula, the user must first sign up for an OpenAI account.
Once you’ve secured your own account, head to the API keys page and create a new secret key.
- The API key generated must not be shared with anyone else. Since the key will only be shown once, the user must store the key locally on their device.
- Next, open a Google Sheets spreadsheet and click on Extensions > Apps Script.
- The Apps Script code editor will load in a new tab. Select the Code.gs file and paste the custom code shown in the previous section.
Ensure that you enter your own API key as the SECRET_KEYvariable. - Click the Save icon found in the toolbar above the code editor.
- Head back to the Google Sheets spreadsheet you’ve opened earlier. You should now have access to the OPENAI custom function.
- Write the first argument for the OpenAI custom formula. The first argument will be the prompt the model will use to generate text.
In this particular example, we want the model to generate text that gives a reason why you should buy a particular item. - The user may also provide an optional second and third argument to indicate the temperature and model to use when generating text.
- Hit the Enter key to evaluate the function. It may take up to 10 seconds for Google Sheets to receive a response from the API.
- Since the custom function works like any other formula, you can use the Fill Handle tool to generate more text with different prompts as an argument.
These are all the steps needed to create a custom function that allows the user to create text using OpenAI’s GPT-3 API.
This step-by-step guide should provide you with all the information you need to begin using OpenAI’s GPT-3 to generate text in Google Sheets.
We’ve shown you how to use Apps Script code to create a new custom function that uses the OpenAI API.
Custom functions are just one example of the many Google Sheets features you can use to speed up your spreadsheet workflow. Our website offers hundreds of other functions and methods to help you get more out of Google Sheets.
With so many other Google Sheets functions available, you can find one appropriate for your use case.
Don’t miss out on our team’s new spreadsheet tips, tricks, and best practices. Subscribe to our newsletter to stay updated on the latest guides from us!
