Chat GPT Custom Function in Google Sheets
Updated: Aug 20
Following on from the last post on How to connect OpenAI’s Chat GPT to Google Sheets, I wanted to dive deeper and explore the possibilities for utilising Chat GPT with a custom function to truly harness the power of natural language processing wherever you need it in Google Sheets.
In today's world, data analysis has become an integral part of any business. As you’ll no doubt be aware, Google Sheets is a popular spreadsheet program that enables you to analyse, organise, and collaborate on data. However, sometimes the built-in functions of Google Sheets are insufficient to meet everyone's needs.
That's where custom functions come in handy. Custom functions allow you to extend the functionality of Google Sheets by writing your own functions in JavaScript with Google Apps Script.
If you're looking for a pre-built Google Sheet Template, head over to ChatGPT Custom Function Template in Google Sheets below to get your FREE copy.
FREE Version
Pro Version
This Pro version comes complete with the latest gpt-3.5-turbo-instruct model from OpenAI, as well as a few additional ChatGPT environments.
I also have a paid version on Etsy.com, which includes product descriptions to enable you to construct creative product descriptions, plus a messages solution to help you build replies to emails, SMS messages, social media responses and much more. This version is also updated with the latest gpt-3.5-turbo-instruct model from OpenAI.
You'll need to add your OpenAI API key, so follow steps 4 and 5 below on how to do that, and you're good to go!
This blog post will explore using a custom function to connect a ChatGPT 3.5 model to Google Sheets. Don’t panic; you won’t need any experience with Google Apps Script or JavaScript, as I will provide you with everything you need to set this up; just follow the steps in this post, and you’ll have your very own custom function in Google Sheets in no time.
Imagine generating text with a single formula using ChatGPT directly from your Google Sheets without switching between different applications. With the help of a custom function, you can achieve just that.
By the end of this blog post, you will better understand how to use ChatGPT as a custom function in Google Sheets and how to leverage the power of ChatGPT to automate your text generation needs.
I’ll show you some awesome examples of what you can achieve with this custom function, but you’re only limited by your imagination of what’s possible.
Step 1 - Create an account with OpenAI
If you haven’t already created an account with OpenAI, you should head to https://platform.openai.com/login?launch to sign up.
Enter your email address and create a password, then click continue.
It is essential to confirm your email address. To do this, check your inbox and click the verification button. After that, you will be prompted to provide your name and phone number. A 6-digit code will be sent to your phone via text message, which you should enter on the screen to complete the process. Once you have done this, you are almost finished.
That’s it; you’ve created an account. You'll get 3 months of free credits up to $18.00; as you can see in the image below, I've been using this for a few months now, and I've only used $4.63 of the $18.00 credit, and I've used it a fair bit.
Once the 3 months are up, or you've used your free credits, whichever comes first, you'll need to set up a payment method here if you want to continue to use the OpenAI ChatGPT service. You can check their pricing here.
If you're using the free credits or you've set up a payment method, you’re all set and ready to move to step 2.
Step 2 - Create a new Google Sheet
For now, just create a new blank Google Sheet and name this something meaningful; I called mine Chat GPT - Custom Function, but you can call yours whatever you like.
You'll need this blank sheet ready for step 3.
Step 3 - Google Apps Script
The only fundamental requirement for this custom function is the script, which you can copy and paste directly into your Script Editor window. You don’t need specific settings tabs in the sheet, as this custom function will work just like any other standard built-in function in Google Sheets within any cell.
With that said, it’s important to understand how the script's parameters operate so that you can change them if required, but we’ll talk about these in more detail later.
In your blank sheet, click the Extensions option in the top menu and select Apps Script. This action will launch a new tab in your browser, enabling you to add the Apps Script.
Once the script editor is open, replace the default function shown below with the full script, including the green text at the top; this is all part of the custom function.
function myFunction() {
}Full Script
Copy and paste this script below into the script editor window:
/**
* Return a Chat GPT response from a given prompt - Custom Made by Ant @ SpreadsheetWise.com
* Copyright © SpreadsheetWise 2023
* @constructor
* @param {string} prompt - the content to pass to Chat GPT as a string or cell location
* @param {integer} maxTokens [OPTIONAL] - The Maximum number of tokens - The larger the number the more comprehensive the response - Default value = 100
* @param {number} temperature [OPTIONAL] - More deterministic = 0 - Less deterministic = 1 - Default value = 1
* @customfunction
*/
function GPT(prompt,maxTokens=100,temperature=1) { // Adjust maxTokens and temperature here
const apiKey = 'Add_Your_API_Key_Here' // Replace this with your API Key from OpenAI
const url = "https://api.openai.com/v1/completions";
const model = 'gpt-3.5-turbo-instruct' // Adjust the required model here
const payload = {
model: model,
prompt: prompt,
temperature: temperature,
max_tokens: maxTokens,
};
const options = {
contentType: "application/json",
headers: { Authorization: "Bearer "+apiKey },
payload: JSON.stringify(payload),
};
const response = JSON.parse(UrlFetchApp.fetch(url, options).getContentText());
return response.choices[0].text.trim();
}
Now that you’ve pasted this into the Script Editor, you’re nearly done! We just need to get the API key and add it to the script, so move on to step 4.
Step 4 - Generate your API Key
To obtain the API key, click on this link, https://platform.openai.com/api-keys, to access the platform. Then, click on the ‘Create new secret key’ button.
On this page, it is necessary to create a new secret key. It’s important to copy and paste this somewhere safe because it cannot be retrieved later once generated. We’re going to use it next in step 5 below.
Step 5 - Add your API Key to the Script
Now that you have your API key, we will insert this directly into the Google Apps Script.
You must paste your API key between the single quotes on line 10 of this script.
const apiKey = 'Paste_Your_API_Key_Here'It is essential to keep in mind that you must not share your API key with anyone or expose it to others. In this demonstration, my API key will remain hidden for security purposes. This is because if anyone else gains access to your API key, they could generate responses that would be charged to your account.
Now would be a good time to save the script. You can save it by clicking the save icon or pressing Ctrl+S on a PC or Cmd+S on a Mac.
You should also name your file by clicking on "Untitled Project" at the top.
Permissions
Next, we must execute the script to grant the required permissions for accessing your sheet.
Return to your browser's Google Apps Script tab and click the "run" button at the top (just next to the Save icon). A message will initially appear, prompting you to click the "Review permissions" button.
Just follow the on-screen steps to grant permissions.
If you need more clarification, you can view complete instructions on this process in my post about How to Connect Chat GPT to Google Sheets.
Once you’ve run this script for the first time, you won’t need to worry about permissions again, but before we close this Google Apps Script tab, we should discuss the parameters that make up the custom function.
Chat GPT Parameters
This ChatGPT function has three parameters, but only the first (prompt) is mandatory; the others are optional. I’ll break them down now, so you know how each works and what they do.
Prompt
The prompt is the parameter you must supply every time you call this function. In the example above, the function is called by typing in =GPT into any cell and then opening parentheses. Next, you’ll either need to enter the content manually within double quotes as a string of text for the prompt, or you can provide a cell location for the prompt, just like these examples below:
=GPT("Explain how the Google Sheets Query functions works")=GPT(A2)Both ways would work, so it really depends on how you wish to use it.
If you only enter the prompt parameter into the function and leave the maxTokens and temperature empty, that’s absolutely fine. Empty parameters will use the default values stored in the script.
Max Tokens
You can set the Max Tokens to whatever you want, as long as this is not more than the maximum allowed for your chosen model. Check column three in the table below to see the maximum token requests, or click this link for more details: https://platform.openai.com/docs/models
LATEST MODEL | DESCRIPTION | MAX REQUEST | TRAINING DATA |
|---|---|---|---|
text-davinci-003 | Can do any language task with better quality, longer output, and consistent instruction-following than the curie, babbage, or ada models. Also supports inserting completions within text. | 4,097 tokens | Up to Jun 2021 |
text-davinci-002 | Similar capabilities to text-davinci-003 but trained with supervised fine-tuning instead of reinforcement learning | 4,097 tokens | Up to Jun 2021 |
text-curie-001 | Very capable, faster and lower cost than Davinci. | 2,049 tokens | Up to Oct 2019 |
text-babbage-001 | Capable of straightforward tasks, very fast, and lower cost. | 2,049 tokens | Up to Oct 2019 |
text-ada-001 | Capable of very simple tasks, usually the fastest model in the GPT-3 series, and lowest cost. | 2,049 tokens | Up to Oct 2019 |
davinci | Most capable GPT-3 model. Can do any task the other models can do, often with higher quality. | 2,049 tokens | Up to Oct 2019 |
curie | Very capable, but faster and lower cost than Davinci. | 2,049 tokens | Up to Oct 2019 |
babbage | Capable of straightforward tasks, very fast, and lower cost. | 2,049 tokens | Up to Oct 2019 |
ada | Capable of very simple tasks, usually the fastest model in the GPT-3 series, and lowest cost. | 2,049 tokens | Up to Oct 2019 |
The default value for this parameter in the script is set to 100, but you can change this to another number in the script file if you wish; you’ll find it along with all the other parameter values on line 9 of the script.
function GPT(prompt,maxTokens=100,temperature=1) {If you're happy with the default of 100, then just leave this value as it is in the script; remember, it's a dynamic parameter, so you can always specify another number when using the function within a cell, such as 500, for example.
=GPT(A3,500)To put this default value of 100 into context, OpenAI’s language models comprehend and analyse text through a process of tokenisation, which involves dividing the text into individual tokens.
The length of the inputs and outputs determines the number of tokens processed in an API request. As a general guideline, one token is equivalent to approximately 4 characters or 0.75 words in English text. However, it's important to note that the total number of tokens in both your text prompt and the generated completion response must not exceed the model's maximum context length. For most models, this limit is 2048 tokens or around 1500 words.
Check out their tokeniser tool to learn more about how text is converted into tokens. This is quite a helpful tool to understand how many tokens or characters your text includes.
You just paste your text, and the number of tokens and characters are displayed along with a visual representation of how this is calculated.
In this example, 98 tokens represent 459 characters, which, if you are interested, is 82 words. If you typically require a prompt or response greater than 100 tokens, then you should change the default value in the script to a suitable value.
Temperature
Keep in mind that the model anticipates which text is probable to come after the preceding text. Temperature ranges from 0 to 1; this enables you to adjust the model's confidence level while making predictions. Decreasing the temperature will make the model less daring and provide more precise and predictable completions. Conversely, increasing the temperature will lead to more varied completions. Experiment with different temperature settings to determine which works best for your needs.
The Script’s default value is set to 1, but if you would like to adjust this to something more deterministic, just change this to 0 or somewhere between 0 and 1 in the script.
Again, you’ll find this parameter setting on line 9 of the script file.
function GPT(prompt,maxTokens=100,temperature=1) {Model
The model is not a main parameter, this has been hardcoded in the script, but it can be adjusted within the script on line 12 if you wish to change this. I’ve found this model to be quite stable. This model has a maximum token input and output of 4097 tokens with training data up to June 2021.
const model = 'text-davinci-003'For the model value, you can choose the one you want from here: https://platform.openai.com/docs/models - You can use some from GPT 3.5 and all from GPT-3.
Just make sure you check the pricing for your chosen model here: https://openai.com/pricing
The model endpoint compatibility for this script is limited to the models shown in the table mentioned above.
Using the Custom Function
As alluded to earlier, you don’t need to enter all the parameters into the function; it will accept just a prompt. If you leave all the other values empty, the default values stored in the script will be used.
For example, you can enter the function like this below, which would use maxTokens = 100 and temperature = 1. The model will always be set to “text-davinci-003” unless you change this directly in the script file.
=GPT(A2)Alternatively, you can be more specific and provide specific parameters. This example below includes the prompt from cell A2, maxTokens = 300 and temperature = 0.5.
=GPT(A3,300,0.5)If you’re happy with the default temperature and model, but you just want to fiddle with the maxTokens, that’s fine; just enter the function like this:
=GPT(A3,500)Example Use Cases
The possibilities are endless, but here are just a few examples of what is possible using this ChatGPT custom function in Google Sheets.
Simple Request
This is the most basic example, but one that will undoubtedly be the go-to for most people due to its simplicity.
Extract
For those that know how to use regular expressions in Google Sheets, aka REGEX, this might not tick all your boxes, but for most people, using this ChatGPT function will be a quick and easy way to extract pieces of information from cells with minimal effort.
This iteration of the function utilises the CONCATENATE function to construct the sentence, which is then used as the prompt for the GPT function.
In the example above, the CONCATENATE essentially creates the sentence - “Extract the email from cell A2” to get the job done.
Translate
Here’s another example of how this combination of the custom ChatGPT function and CONCATENATE can be utilised to translate text into another language.
Social Posts
I’ll leave you to ponder this final example, which uses all three parameters from the GPT function - prompt, maxTokens and temperature to create social media posts for various social platforms using topics and tone of voice to speed up the creative process and to generate some variety.
Assuming the notion that one token is equivalent to approximately four characters, I’m calculating the number of tokens from the character limit column to pass this as the maxTokens parameter for the second argument in this function (D2). The temperature is set to 1 to make the response more varied.
=GPT(CONCATENATE("Write a ",A2," post about ",B2," in a ",E2," tone of voice"),D2,1)I've used the new Google Dropdown Chips in this table. Check this Dropdown Chips post out to see how they are created.
Handling Errors
I thought it was worth mentioning that errors do crop up occasionally; most of the time I’ve noticed errors such as ‘Our servers are experiencing high traffic’, ‘Issue on our servers’, or ‘You are sending requests too quickly’.
When you see an error, don’t panic, it will likely resolve itself if you give it some time. Here is a guide to the error codes that you might see. Admittedly, they don’t happen often, but it’s just something to bear in mind.
Final thoughts
It would be great to hear what you think. Did you find this useful? Please leave a comment below to let me know your thoughts. Feel free to share your ideas about how you are using this custom function.
Don't forget to check out this pre-built Google Sheet Template
Thanks for reading!
