ChatGPT Clone in Google Sheets

We're going to build a ChatGPT clone in Google Sheets and it's not going to take that long. You're going to be surprised. Sam Altman has even admitted before ChatGPT existed, about 10 months before it existed in open AI’s API documentation. There was a chat example. We go through that example and then we use the interface of ChatGPT inside of Google Sheets.

I take a screenshot of a button. We get it working. In a previous video for members, I have created an open AI's API inside of Google Sheets. In this particular tutorial, I'm going to try to actually style the sheet a little bit and create a ChatGPT interface. We're going to do it a little differently than ChatGPT does, but I think it'll turn out really cool. Hope you enjoy this tutorial!

So we have an open-ended conversation with an AI assistant.

This is an example that OpenAI provided when they released their API. And even before ChatGPT existed, they had this.

I'm going to go off of this a little bit and I'm also going to go get the ChatGPT design. I’ll look at it a little bit and see what we can do in a Google Sheet.
In the past video is where all the script that we did before is. We're going to rewrite that. In this particular case, we just want to do this chat:

Our curl is the same. Our API key is inside of a sheet called “apikey.” I'm going to go grab that and create a new API key.

Enter it in the cell of the sheet called “apikey.” (Cell A1)

Now we got a prompt:

We want to grab the prompt that they have (from where we made the API request).

We’re going to copy and paste this var prompt, complete with quotes:

Let's just see what this does if we have this as the prompt. We have some differences here. You can watch the video below to follow which ones we need to change based on our API request (i.e. temperature, max_tokens, presence_penalty, and stop):

Let's open this in the playground and see what we can do. We're going to get into this conversation.

We have this chat going back and forth. It’s going to ask us something. Let's see if this works in our Google sheet. We're just going to get the answer.

We are calling this "openai", which we’ll change to chat.

Then we need to give it a prompt, which is this variable, prompt. (line 8)

Also, let’s delete the prompt in line 1.

Let’s go the Sheet1 tab and type in =chat() in cell A1.

Now it's giving us the reply back. Cool. Now we can ask a question.

We want to put it towards the end of line 4.

“+ question +”

Let's go back to our chat and type in, “What is the number of stars in a galaxy?”

Now we're getting a reply back!

We can also use the power of Google Sheets to ask a question. Enter this in A1: “What is the max number of characters in a tweet?”

Then let’s go over to B1 and type in =chat(A1) and wham, bam, we got it!

But we want to do this back and forth. How do we do this?

Now, this is going to get a little weird and complicated.

What I want to do is to create an on open menu, which is a custom menu here in Google Sheets and Apps Script.

We're going to go grab the code because I want to access this and not by just putting in a formula.

In line 4, replace “Custom Menu” with “ChatGPT Menu.”

In line 5, replace “Second item” with “Enter Chat”.

Also in line 5, replace “menuItem1” with “chat”.

Let’s get this in our menu by saving it.

Close the Apps Script and refresh the Google Sheet.

The page refreshed the whole sheet, and we're going to get our custom menu there.

Let's work on a little bit of the chat and what we want to do. I think this is the idea. We want to get a prompt. Ask a question, then get it to enter the response.

et's look at ChatGPT and get a little bit of design style out of this. I got a screenshot from this website: https://html-color-codes.info/colors-from-image/

We're just going to get the color scheme. We want this background color: #333541.
We don't want any of these kinds of extra stuff. We actually sort of want just three columns and not very many rows.

Let’s change the color. Go to fill color, click “custom”, and enter our background color in the text field.

Then we’re going to grab the color for the chatbox: #404251.

We can use that color in our sheet by changing the cell color.

I want to do one more thing. Let’s give it a thick border and make the text white (but not too white).

Okay. Let’s type in our question: What is the airsped velocity of an unladen swallow?

This is a question we have right now. We want to get that API, then we want to get back the answer and also keep the question.

Insert a row right here on chat.

Maybe we add a little button for our chat, too. You can grab it from Insert --> Image --> Insert image over cells.

We can assign a script for our button (arrow image).

When we press this button, when we take action upon this script, what do we want to do? We want to take this question, go to the API, ask that question, then come back and write the answer. Then also keep this question there. Maybe insert a row right there.

Now let’s go to Apps Script.

Line 9: The function question is going to be wrong. We don’t want that. Let’s delete it.

We're going to get our variable question from:

SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Chat”).getRange(“B2”).getValue();

So now we can get the value of that question.

We're going to go send it.

Let’s enter some variables and some values to do that:

Now let’s assign the script we just created to our button.

Right click on the image, choose “Assign script”, enter “chat” in the text field, and then click “OK.”

We have done the following steps for to get these:

• insert two rows
• send this question to the API
• get it back
• insert the answer

We got some answers! The API worked.

But it doesn’t delete the question from here. Let’s do that right now. Add this code in the last line in Apps Script (line 41):

chatSheet.getRange(“B2”).clearContent()

Is it going to work? Let’s try this by asking some questions.

Look at this. We have ChatGPT working inside of a Google sheet. Isn't this cool?
There is a second part of this tutorial in video format where I add more and make it really ChatGPT. It takes the last few questions and answers and includes that in the next prompt. So if you want to watch that you have to be a Better Sheets member.

Or hit the like button in the YouTube video format, because not a single one of my videos has made a hundred likes yet. So if that video gets a hundred likes, I'll post the second part of this video on YouTube. Thanks for reading this tutorial!

Watch the video version:

Learn more cool things you can do with Google Sheets:

Get more Google Sheets Tutorials at BetterSheets.co

We love Google Sheets here. If you love Google Sheets as well, you might want to consider becoming a member of Better Sheets. For only $19/month you can access over 200 Better Sheets tutorials. Learn Apps Script in under 40 minutes. Design better dashboards. Make your sheets faster and yourself more confident in sheets.