How to use Chat GPT in Google Sheets

Connecting OpenAI’s Chat GPT to Google Sheets could be very practical in many situations, such as automating customer service responses, generating suggestions for website keywords, composing emails or social posts, suggesting article titles, or even an entire article, plus everything in between.

Not least, by connecting Chat GPT to Google Sheets, businesses can take advantage of OpenAI's powerful natural language processing capabilities to make their workflow more efficient and consistent.

In this article, we're going to trigger the ChatGPT using a menu button to submit a request and retrieve a response from the API.

Update 4th January 2024 - Since this post was published, OpenAI announced that they will shut down their older completions and embeddings models; for more details on this, read this post here: Older OpenAI models will be shut down on January 4

If you can't wait, download the FREE Google Sheet Template with this Menu Trigger using the button below. You'll need to add your OpenAI API Key, so follow this step using the link, and you're good to go!

Chat GPT Menu Trigger Template in Google Sheets

Buy Now

If you're looking to build a custom function in Google Sheets, click the image below for full details. This is a new and more powerful way to use ChatGPT in Sheets.

Introduction

In this article, we will set up an account with OpenAI, generate an API key necessary to connect to Google Sheets and then join everything up with Google Sheets using Google Apps Script. If this sounds daunting, don’t panic; just follow all the steps, and you’ll be surprised at how easy the whole thing is. All you need is Google Sheets and an account with OpenAI. You won’t need any prior experience with Google Apps Script; I’ll walk you through the whole process. You’ll have your very own personal assistant AI within Google Sheets in under 15 minutes.

We’ll start with a basic sheet that allows you to return results using a menu to trigger a response from the AI. This is perfect if you want to generate multiple rows of results simultaneously. However, using this solution, you could just return one or two results; it’s entirely up to you.

In a follow-up post, I’ll also cover another more dynamic solution to create a ChatGPT Custom Function within any cell or cells that trigger the AI for a response. The second method allows more flexibility since joining multiple cells to create a dynamic request is possible. Here’s an example below of what you can do, so stay tuned for that.

However, both solutions allow you to play with the parameters directly within the sheet, which is a nice feature if the thought of code scares you. The fundamental setup does the same thing under the hood, but the application will depend on how you wish to use OpenAI’s Chat GPT within your sheets.

Before we dive into the nitty-gritty, here’s a brief overview of OpenAI’s Chat GPT to give you some background, but if you already know about the nuts and bolts of Chat GPT, you can skip ahead to the desert!

What is Chat GPT

Chat GPT by OpenAI gained enormous popularity by generating more than one million users within the first five days following its launch in November 2022. This is remarkable when you consider that it took Instagram more than two months to generate the same number of users.

​​Chat GPT is a Generative Pre-trained Transformer, a form of natural language processing (NLP) technology designed to understand and generate human-like conversations. It is based on the Transformer architecture, a powerful AI system developed by Google in 2017.

Chat GPT works by understanding the context of a conversation, breaking it down into its individual components, and then generating a response based on that context. This is accomplished by combining different AI layers, including a deep learning model, a recurrent neural network (RNN), and a generative model.

The deep learning model is used to detect patterns in the conversation and extract relevant information. The recurrent neural network is then used to remember the context of the conversation and generate a response based on that context. Finally, the generative model creates a reply similar to what a human would say.

How can Chat GPT be utilised?

Chat GPT can be used in various applications, such as virtual assistants, customer service environments, and conversational artificial intelligence (AI). It is a powerful technology that can help improve the user experience and make interacting with computers more natural and intuitive.

Aside from natural language processing, Chat GPT can also interpret programming languages such as JavaScript, TypeScript, Python, CSS, HTML and SQL, to name a few. It can even translate one programming language to another. If you don’t understand a piece of code, just ask Chat GPT to explain it or ask for suggestions on improving it or adjusting it to your specific requirements.

With all that said, Chat GPT is not perfect. It has limitations and will not always generate a flawless response, so don’t rely on this too much. It should be seen as a tool to assist with specific tasks to make you more productive, not to vanquish the creativity of the human mind altogether.

How much does it cost?

Chat GPT costs depend on two main factors: the language model and the number of tokens used. Each language model has a different cost per token, with larger and more complex models costing more than smaller ones. In addition, minimum usage requirements must be met for each model, meaning that users will need to pay a certain amount even if they do not use all the tokens.

However, discounts are available for bulk usage, which can significantly reduce the overall cost per token. For users requiring a large amount of natural language processing, this can effectively reduce costs while still using the best models available. Check out their pricing here

How to Access Chat GPT

Once you’ve created an account with OpenAI, you can use the input field to chat directly with Chat GPT, you can also download a desktop application or use a Google Chrome Extension to harness the power of the AI.

The good news is you’re not limited to these options; you can also integrate this artificial intelligence technology directly into Google Sheets. The process is actually not that complicated either, but the great thing is once this is set up, you could then simply type a request into a cell in your spreadsheet, and the AI will generate natural language answers to your questions without you even leaving Google Sheets.

This has so many use cases. As mentioned earlier, you could automate customer service responses, generate social media content, write product descriptions, translate text, create SEO keywords, and write article titles or entire articles. Your imagination is the only limit.

Creating an OpenAI Account

To create an account with OpenAI, click this link https://platform.openai.com/login?launch. Enter your email address and create a password, then click continue.

You’ll need to verify your email address, so check your inbox and click on the button to verify your email address. Next, you’ll need to enter your name and phone number. You’ll get a text message with a 6-digit code sent to your phone. Just enter this on the screen, and you’re pretty much done.

You’ll get $18.00 worth of free credits for the first three months, but this is limited to just one phone number, so if you create more than one account linked to the same phone number, any subsequent accounts won’t be entitled to free credits.

You’ll end up on the Overview page once you’ve logged in, so it’s worth looking around at the Examples and the Playground, but if you want to dive in, head over to https://chat.openai.com/chat to play with the Chat GPT model. You’ll need to log in again using the login details you used to create the OpenAI account.

At the bottom of the page, you’ll see the chat field where you can chat with the AI and ask away.

This Chat GPT window is useful for quick access to chat with the AI. You can ask pretty much anything but remember the training data only includes information up to September 2021 with the gpt-3.5-turbo model. Check this out for more details on the models available. If you ask it a question where the information is more recent, in other words, after September 2021, it won’t be able to help you as it doesn’t have access to real-time data.

Below is an example of a basic question with the AI’s response.

Connecting Chat GPT to Google Sheets

Now that you’ve had some fun chatting with the AI let’s jump in and connect Chat GPT to Google Sheets.

To start with, create a new blank spreadsheet in Google Sheets. You’ll want two tabs. I’ve called mine AI and Settings.

The AI sheet is where we will ask the questions and generate the responses. The settings sheet is where we will store our parameters. More on this later.

Create two headings in the AI sheet; I’ve called mine Request in A1 and Response in B1. You can call this whatever you want, but the key to making this Google Apps Script work is that the question should be in the left-hand column and the answer from the AI on the right. If you change this, the script will not work.

The great thing about this script is you can add multiple rows for as many questions as you want. You can just have one request (one row), or you can have hundreds or thousands of requests (lots of rows); the script will work the same either way.

Model

In the Settings tab, we must add four parameters and their respective values. For the model parameter, you can choose one of these options; it’s worth trying a few out to see what works best for you, bearing in mind the costs associated with each will vary. Remember, you’ll get $18.00 worth of free credits for the first three months, so it's worth trying out as this won't cost you a penny.

Max Tokens

The Max tokens parameter allows you to restrict how many tokens are used for each request. The higher this number, the longer and more comprehensive your response will be, but as alluded to earlier, you should consider the costs if you’re intending to run hundreds of rows.

Temperature

OpenAI models are non-deterministic, meaning that identical inputs can yield different outputs. Setting the temperature to 0 will make the outputs mostly deterministic, but a small amount of variability may remain. You can essentially set this anywhere between 0 and 1. Have a play around to see what works for you.

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.

You’ll need to generate a new secret key on this page. It’s important to copy and paste this somewhere safe; once generated, you cannot view them again. For the sake of this demonstration, we’ll paste the API key directly into cell B5 of the settings sheet.

However, it’s important to note that you should not share your API key with anyone else or expose it where others can view it. My API key will be hidden in this demonstration. The reason is that if anyone else discovered this, they could use it to generate responses which would be charged to my account.

I’ll show you how to adapt the Google Apps Script to store it in the code instead of the sheet, but either way, if you share your Google Sheet with others, they could effectively find it in your script file regardless.

It’s just something to bear in mind; if you trust someone you wish to share your sheet with, then it’s probably fine, but don’t publish your sheet publically; otherwise, you might end up with a large bill!

Now that you have your Google Sheet all set up and ready with the sheets, parameters and values mentioned; we can add the script to start the magic.

Connecting Chat GPT with Google Apps Script

In the top menu, click the Extensions option and choose Apps Script. This will open a new tab in your browser where we can add the Apps Script.

Clear out the default function in the script editor; you won’t need that.

function myFunction() {
 }

To give some context to what’s happening here, I’ll break this down. This will come in handy if you want to change things around to suit your specific requirements.

The onOpen function generates a custom menu in your sheet to trigger the script.

function onOpen() {
  SpreadsheetApp.getUi().createMenu("OpenAI")
   .addItem("Generate", "openAi")
   .addItem("Clear Content", "clearContent")
   .addToUi();
}

This has two options:

1. Generate will run the main openAi function to take your request and return a response.

2. Clear Content uses the clearContent function to reset the table to clear everything to a blank canvas, except for the headings.

You’ll see both in the menu shortly once everything is saved and permissions have been run.

The main function is openAi - This generates a response from the AI. If you want to adjust the name of the settings or AI tab to match your tab names, you should change the name in the script, in lines 20 and 21, respectively - ('Settings') and ('AI').

The same for each parameter (model - temperature - maxTokens - apiKey). The numbers in the parentheses represent the location of that parameter in the sheet; for example, the model parameter is located with this reference (2,2) - It means this value can be found in the second row of the second column in the settings sheet - in other words, cell B2.

If you want to store the API key in this script file, as opposed to the spreadsheet, as I have in this demo, then you’ll need to adjust line 25 from this:

const apiKey = settings.getRange(5,2).getValue();

To this:

const apiKey = 'PasteYourAPIKeyHere'

That’s pretty much all you need to worry about. The only other thing you might wish to alter is the location of the response, which you’ll find on line 50. Currently, this outputs the response in column 2 (B) from row 2 down as many rows as you have requested in column A.

If you want to position the output somewhere else, then you’ll need to adjust this, but doing so may cause issues if the request and response are not positioned next to each other. It’s probably best not to change this unless you know what you’re doing.

Full Script

You can copy and paste the full Google Apps Script shown below straight into the Apps Script Editor.

/*
* Connecting OpenAI's Chat GPT to Google Sheets by Ant @ SpreadsheetWise.com
*/

function onOpen() {
  SpreadsheetApp.getUi().createMenu("OpenAI")
   .addItem("Generate", "openAi")
   .addItem("Clear Content", "clearContent")
   .addToUi();
}

function clearContent(){
 const sheet = SpreadsheetApp.getActiveSheet();
 const lastRow = sheet.getLastRow();
 sheet.getRange(2,1,lastRow,2).clearContent();
}

function openAi(){
 const ss = SpreadsheetApp.getActiveSpreadsheet();
 const settings = ss.getSheetByName('Settings');
 const sheet = ss.getSheetByName('AI');
 const model = settings.getRange(2,2).getValue();
 const temperature = settings.getRange(4,2).getValue();
 const maxTokens = settings.getRange(3,2).getValue();
 const apiKey = settings.getRange(5,2).getValue();
 const data = sheet.getDataRange().getValues();

 const url = "https://api.openai.com/v1/completions"
  var results=[]

 for (var i =1; i<data.length; i++){
   var prompt = data[i][0]
   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());
     results.push ([response['choices'][0]['text']])
 }

 sheet.getRange(2,2,results.length,1).setValues(results)
}

Now would be a good time to save the script. You can click the save icon or Ctrl+S on a PC or Cmd+S on a Mac. You should also name your file by clicking the Untitled Project at the top.

If this is the first time you’ve used Google Apps Script, you’re doing well; we’re nearly done, just hang in there!

Permissions

Now we need to run the script to give it the permissions it needs to access your sheet. Before we do that, just head back to your sheet and type a question in cell A2 under the Request heading on the AI tab.

Head back over to the Google Apps Script tab in your browser and hit the run button at the top. First, you’ll see this message pop up - click the ‘Review permissions’ button.

Next, you’ll need to choose an account; in my case, I’m only logged in on one account, but if you are logged into Google with multiple accounts, you’ll see a list of them here. Choose the account you want to associate with this script by clicking on the name.

Lastly, you’ll see this window with a message saying that Untitled Project (the script we just created) wants access to your Google Account. Don’t panic; everything is fine - this script was created in your account. This script is considered just like a third-party App, so consequently, your script (the App) wants access to your Google account to get the data in your sheet and pass it to Chat GPT to be able to return a response.

The script needs to be able to see, edit, create and delete sheets and connect to an external service (OpenAI - Chat GPT). This is perfectly normal. Click on the allow button, and you’re done.

After you’ve done these initial permissions steps, you won’t need to jump through these hoops again; it’s only required the first time you run the script.

If you head back to your Google Spreadsheet, you’ll notice a new menu option has appeared called OpenAI. This is what you will click to run the script from now on. You can close the Google Apps Script window now, but if you ever want to check it again, just find it in the extensions menu.

As mentioned earlier, the Generate option will run the main function to collect your requests in column A and return a response in column B. The Clear Content option will just reset the cells to empty them out so you can begin with a clean slate.

I added this Clean Content function to allow you to clear out old requests quickly and easily, as you don’t want to run them unnecessarily. It won’t hurt to run the same question multiple times, but you may have 20 rows or 100 rows of requests in column A, and it might be pointless to run these multiple times if you only want to add one new request. Remember, each request will use a certain number of tokens; they’ll quickly add up if you’re running hundreds of lines unnecessarily.

With all that said, go ahead and click on the Generate menu option and watch the magic as your Google Sheet now sends a request to Chat GPT for a response. The response might take a second or two, but it’s worth the wait!

Make sure you look out for the next post, where we’ll explore how to use Chat GPT as a custom function in Google Sheets; you can even customise the little formula helper window to your liking!

If you're looking for a premade template sheet ready to go, click the button below; it's FREE!

Chat GPT Menu Trigger Template in Google Sheets

Buy Now

We’ll also explore other ways to utilise this custom function in Google Sheets to make you more productive.