Chat GPT 3.5 Pro Template Setup Instructions
Setup Instructions
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 three months of free credits up to $18.00. Once the three 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 have set up a payment method, you’re ready to move to step 2.
Step 2 - 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 4 below.
Step 3 - 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 can access the Google Apps Script file by going to the menu Extensions > Apps Script from your Google Sheet.
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. 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.
Step 4 - Permissions
Next, we must execute the script to grant permission to access your sheet.
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.
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. Click this link for more details: https://platform.openai.com/docs/models
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 four 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.
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 Chat GPT Pro custom function in Google Sheets.
You can simply use the examples in this template or create your own following the details above for the GPt parameters.
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.
Messages
This example is great for crafting replies to emails, text messages and lots more. So if you’re looking for some inspiration, let Chat GPT do the heavy lifting!
You just need to enter the email or message you received into column A and let Chat GPT generate a reply for you in column B, nice!
Product Descriptions
If you’re looking to create a whole load of product descriptions then this one is for you. You’ll just need to provide a brief overview of the product and let Chat GPT get creative with a description.
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)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 to let me know your thoughts and to help others who may be on the fence. Feel free to share your ideas about how you are using this custom function.
Thank you again for your purchase and for supporting SpreadsheetWise.We really do appreciate your business.
We would encourage you to check our full range of Google Sheets templates here, and be sure to check out our YouTube channel for tips and tricks in Google Sheets.