Upgrade Google sheets with the ChatGPT API
Use the power of ChatGPT right inside Google Sheets
I've got a cool idea for you - using ChatGPT API inside Google Sheets. It's a powerful combination that can save you time and streamline your data processing tasks. In this article, we'll explore the basics of using the ChatGPT API inside Google Sheets, a few tips to streamline the process, and last but not least some easy ways to optimize results. It’s straightforward and easy!
Setting up ChatGPT API in Google sheets
To use ChatGPT API in Google Sheets, you need to write a script that calls the API and returns the generated text to your spreadsheet. Sounds complicated? No worries, here’s a step-by-step guide on how to do it. No coding is required, just copy and paste what I have here. There’s one requirement to get it to work, which is an OpenAI API key. If you don’t have one yet, sign up with OpenAI and go to this link: https://platform.openai.com/account/api-keys
Here's how to set up Google Apps Script to use the ChatGPT API:
Open the Google Sheets spreadsheet where you want to use ChatGPT API.
Click on
Exetensions>App scriptsto open the Google Apps Script editor.Copy and paste the following code, make sure to add your API key at the top
Make sure to give your file a name!
//fill in your API key here between ""
const apiKey = "Paste API key here (starting with sk-)";
//standard amount of max tokens if not specified
const Tokens = 700;
//standard temperature, determines the randomness of the result.
const Temp = 0.7;
// Creates menu at the top
const onOpen = () => {
const ui = SpreadsheetApp.getUi();
ui.createMenu("GPT")
.addItem("Lock results as text", "Lock")
.addToUi();
};
// Saves formulas as plain text
function Lock() {
var ss = SpreadsheetApp.getActiveSheet()
var data = ss.getDataRange().getValues()
ss.getRange(1, 1, data.length, data[0].length).setValues(data)
}
// Generates text based on user input
function GPT(prompt, val, Temp, Tokens) {
var ss = SpreadsheetApp.getActiveSheet()
var data = {
"messages": [
{"role": "user", "content": prompt + val},
],
"model": "gpt-3.5-turbo",
"temperature": Temp,
"max_tokens": Tokens,
};
var options = {
'method' : 'post',
'contentType': 'application/json',
'payload' : JSON.stringify(data),
'headers': {
Authorization: 'Bearer ' + apiKey,
},
};
// Sends the API request to OpenAI
var response = UrlFetchApp.fetch(
'https://api.openai.com/v1/chat/completions',
options
);
Logger.log(response.getContentText());
// Parses the response to extract the generated text
var result = JSON.parse(response.getContentText())['choices'][0]['message']['content'];
Logger.log(result);
return result;
}
Run it! You might be asked to give the script permission to change the sheet, which you are required to do to make it all work.
You might notice that at the top of your sheet, to the right of the tool and help button, a new button has appeared called GPT - We’ll get to this in a bit!
Using ChatGPT API Inside Google Sheets
Alright, now that we've made it through the boring technical details of setting up ChatGPT API inside Google Sheets. Now it's time for the real fun to begin! With just a little bit of creativity and some clever prompts, we can use ChatGPT API to generate all sorts of text-based content and make our data processing tasks a breeze. So let's roll up our sleeves, fire up our keyboards, and see what kind of magic we can create with ChatGPT API inside Google Sheets.
Give a simple prompt a try. Pick a cell and type:
That should list a random product. You can now drag it down and list a whole bunch more.
What if you pick a cell next to it and give a different prompt a try? Maybe reference the product too?
Can you see the potential? You can now generate a ton of these in a very short time.
Being able to reference cells allows you to be able to apply the same prompt to different contents, adjust the prompts on the go, and format large amounts of data in a very short time.
Tips and tricks
The code that we used has a little bit more to it than just what is shown above. There’s the GPT button that is added above - you might have noticed that when working with the new GPT function, it likes to redo a prompt whenever you change something. The button is there to lock the generated content in place! Simply hit the button and click Lock, now it’s stored as text instead of a formula.
Click it, and then click lock. This will remove all the formulas and just store everything as text, preventing you from redoing everything again on accident.
On top of that, there’s a little extra to the prompt. If you took a good look you might have spotted the following in the code:
If you use the new =GPT() formula, you can set four different values. The first is prompt, and the second is reference value as we showed earlier. Third and fourth is Temp and Tokens. With these you can adjust the temperature, which changes how “creative” or wild the response will be, you can set it to anywhere between 0 and 2 (it’s set at 0.7 standards, you can adjust this if you want). Tokens stand for the max tokens allowed.
If you’re formatting a bunch of numbers you’re best off using a low temperature and a low token count. Want to generate wild ideas for social media content, a DnD campaign, or something else? Increase that temperature!
Part 2 coming soon…
Get ready, because Part 2 of this series is going to be even more exciting! We'll be exploring some really cool use cases that demonstrate just how powerful ChatGPT API can be when used inside Google Sheets. From generating custom writing prompts to formatting data in new and creative ways, we'll show you how to make this tool work perfectly for your needs.
Part 1 will also be available on Learn Prompting course. Part 2 will only be available on my substack, so make sure to subscribe! It will be available for free!