ChatGPT Clone in Google Sheets Part 1

<div>00:00 All right. So in a previous video for members, I have created a open AI's API inside of Google Sheets. In this particular video, I'm gonna try to actually style the sheet a little bit and create a chat G P T interface.<br>00:16 We're gonna do it a little differently than chat G P T does, but I think it'll turn out really cool.<br>00:23 Hope you enjoy this video. I'm gonna have an API key shown in this, but I'll delete it before putting this in here.<br>00:30 And I wanna point out that open AI in their examples. If you scroll down, they do have a chat.

A chat example is, that's what I'm gonna be going over now, wherever it is.<br>00:42 There it is. So we have a open-ended conversation with an AI assistant. This is an example that OpenAI provided when they released their api and even before chat G P T existed, they had this.<br>00:55 So I'm gonna go off of this a little bit, and I'm also gonna go get the chat, G P t sort Learn how to sort data effectively in Google Sheets. Opens in new tab of design and look at it a little bit and see what we can do in a Google sheet.<br>01:06 But let's see what we can do here. We have our curl command, which is what we need.

We have our a p i, let's just open app script Discover how to automate tasks with Google Apps Script. Opens in new tab and give it a go.<br>01:20 I don't know what exactly what we need to ch change. So in the past video, we did get this your url, but, oh, here, here's all the script that we did before.<br>01:30 We're gonna rewrite it. We're going to use this one. I'm gonna delete all the other stuff from here that we did again in the last video for members only.<br>01:39 We did like write, we wrote some tweets. We asked a question. In this particular case, we just want to do this chat.<br>01:47 Okay? So our curl is the same V1 completions.

Our API key is inside of an a, a sheet called API key.<br>01:57 So I'm gonna go grab that and create a new API key. Again, I'm just gonna delete this before you, you watch this.<br>02:05 All right, enter it there. Okay, now we got a prompt. We don't necessarily want to prompt, do we? Yeah, we want, so we want to grab this prompt that they have, and we will look at it in a hot second.<br>02:20 Where Understand how to use the QUERY function with WHERE clauses. Opens in new tab shall we put it? Let's put it right. Actually not there. We're gonna do variable prompt equals, we'll put it in quotes.<br>02:31 All right. The following is a conversation with an AI assistant.

The assistant is helpful, creative, clever, and very friendly. We have some new lines.<br>02:39 Human. Hello, who are you? I am an AI created by Open ai. How can I help you today? Human. I'd like to cancel my subscription.<br>02:47 So I think this part is what we need to, I think, actually, let's see, let's just see what this does.<br>02:56 If we have this as the prompt let's look at the stop. We're gonna just change the stop. Oh, we have some differences here.<br>03:06 Temperature 0.9. Okay, our temperature here is 0.9. We have max tokens is one 50. Let's change that there.

Top P one, FQ frequency penalty, zero.<br>03:21 And then 0.6 for this one, the stop is different. So we're gonna change the stop to this one instead of this new line.<br>03:30 Okay? And that's all they have. Let's open this in the playground and see what we can do. Human. Hello? Oh, I need to hit submit.<br>03:44 &lt;laugh&gt; ai. Hi there. How, what can I do for you? So now we're gonna get into this conversation. What is the next thing you want me?<br>03:54 Oh, I don't, I spelled that weird. What is the next thing? Af next thing after G P T three submit.<br>04:07 G p t four.

Okay, so we have this sort Learn how to sort data effectively in Google Sheets. Opens in new tab of chat, right? This chat going back and forth. It's gonna ask us something.<br>04:14 Let's see if this works in our Google sheet. We're just gonna get the answer. What we're doing is really calling one.<br>04:23 We're gonna call it and get the response back. We are calling this open ai, but let's call this chat. I'm gonna change that.<br>04:32 And we need to give it a prompt, which is this prompt, variable prompt, actually, yeah, let's kill that prompt there.<br>04:43 And just go into here and go equal chat. See if this works at all. It's loading. It's loading.

Sure thing.<br>04:52 I'm sorry to hear. You've canceled it. So now it's giving us the reply back, right? Cool. So now we can ask a question Find out how to ask questions in Google Sheets. Opens in new tab , right?<br>04:58 Question. And we want to put it right here inside of this. Put two quotes. Put two quotes, put a plus plus.<br>05:08 And inside the plus, plus, we're gonna put our question. All right? Let's go back to our chat and put in here chat.<br>05:18 What is the number Get insights on handling numbers in Google Sheets. Opens in new tab of stars in a galaxy? I'm gonna ask that question to chat. G p T, right? Mm.<br>05:30 The now we're getting a reply back, right? But now this is really cool, right?

We can ask a question Find out how to ask questions in Google Sheets. Opens in new tab . We can also use the power of Google Sheets to ask a question Find out how to ask questions in Google Sheets. Opens in new tab .<br>05:38 What is the max number Get insights on handling numbers in Google Sheets. Opens in new tab of characters in a tweet? We can go over to B one. We can do equal chat a one, and wham, bam, we got it.<br>05:53 We got exactly, we're asking a question, we're getting a reply back, but we wanna do this back and forth, back and forth.<br>05:58 How do we do this? Now, this is gonna get a little weird and complicated.

So what I want to do actually, is I want to create an on open menu, which is a called custom menu Create custom menus in Google Sheets for better navigation. Opens in new tab here in Google Sheets and App Script Discover how to automate tasks with Google Apps Script. Opens in new tab .<br>06:11 We're gonna go grab this code. And I, cuz I wanna access this by not, not by just putting in a formula Master the syntax rules for formulas in Google Sheets. Opens in new tab .<br>06:22 This might make sense later, but we're gonna just do chat here. Enter chat. I don't know if this is going to work exactly as we have it right now, but if we just go to cause we need the question, right?<br>06:42 We wanna have check G p t menu for now. Let's just get it in a menu.

We're gonna close this.<br>06:50 We're going to refresh the page, refresh the whole sheet, and we're gonna get our custom menu Create custom menus in Google Sheets for better navigation. Opens in new tab there. We're gonna delete this.<br>06:58 Let's work on a little bit of the chat. And what we want to do, I think this is the idea.<br>07:04 We want to get a prompt asking, what are you, what are you asking? Ask the question, then get it to enter the get it to enter the response here, but then use that response.<br>07:16 And then next time we have a chat so that we, we keep having that prompt. The last few things in the prompt.<br>07:23 So, hmm, let's look at chat.

G p t, right? Chat, G p T. Let's look at it and get a little bit of design style out of this.<br>07:34 Okay, so I got a screenshot, actually, I went in and got an image Learn how to insert and manipulate images in your sheets. Opens in new tab of chat pt. Let's load it up in here.<br>07:42 And we're just gonna get the color scheme. We want this background color Change the background color of cells in Google Sheets. Opens in new tab , okay? We don't want any of these kinds of extra stuff.<br>07:52 We actually sort Learn how to sort data effectively in Google Sheets. Opens in new tab of want just three columns, right? We want not very many rows, but this background color Change the background color of cells in Google Sheets. Opens in new tab custom is going to be this.<br>08:08 Then we're gonna grab, what is that chat ui look like. Chat that. There we go.

And we can enter right here.<br>08:20 And, okay, so we can enter here. Chat. Let's give it, I wanna do one more thing. I wanna give it a thick blackboard.<br>08:32 That looks cool, right? This looks like chat. G p t, right? Sheet chat. G p t and a Google sheet.<br>08:44 Let's do that. Do, do, do do, do give it white. Ah, that white's too white. Now this, this might sound too, okay, so if we have a questionnaire question here, what is the air speed velocity of an un unrelated swallow, right?<br>09:01 What is the air speed, velocity, velocity of an unladen swallow?

Okay, this is a question we have right now. We want to get that into the ai ai yeah.<br>09:16 Api. Then we want to get back the answer and then delete this question, right? Is that how chat tv works?<br>09:23 Well, maybe we wanna keep the question, so we're gonna have it put insert a row right here on chat. Let's look at our extension app script Discover how to automate tasks with Google Apps Script. Opens in new tab .<br>09:37 So we got a question, maybe we add a little button here too. Insert. Let's see if we can find that cool image Learn how to insert and manipulate images in your sheets. Opens in new tab for that.<br>09:52 So here it's this what is this called? Arrows. See if we can insert image Step-by-step guide on how to insert images into your sheets. Opens in new tab over cells Explore how to work with cells in Google Sheets. Opens in new tab .

I'm going to drag this over there.<br>10:14 I had to redo Learn how to redo actions in Google Sheets. Opens in new tab that, but here we got a button. Gonna move that over here. Make it a little bit smaller.<br>10:25 Move it a little bit there. That now we got a button, right? So we can assign this a script. We can, we don't even need this.<br>10:33 I dunno why we added this menu option, but we can add it. Okay? When we press this button, when we a take action upon this script, what do we wanna do?<br>10:42 We want to take this question, go to the api, ask that question, then come back and write the answer.

And then also keep this question there, right?<br>10:51 And maybe insert a row right there. Alright? That's what we gotta do. So chat question is gonna be wrong. We don't want that.<br>10:58 We're gonna get our variable question from spreadsheet Get familiar with the basics of Google Sheets. Opens in new tab , app dot get sheet by name. Our name is gonna be chat. I think it's lower.<br>11:10 Nope, it's app, absolutely capital chat dot get range Understand how to define and use ranges in your sheets. Opens in new tab . The range Understand how to define and use ranges in your sheets. Opens in new tab is going to be b2. Get value. So now we can get the value of that chat of the chat.<br>11:25 The question. That's the question. And we're gonna go send it and then we're gonna get back.

We're gonna return json traces text, but we don't, we one variable.<br>11:38 Chat answer equals this. What do we wanna do? We don't wanna return it. We actually want to go spreadsheet Get familiar with the basics of Google Sheets. Opens in new tab app dot get active spreadsheet Learn about accessing the active spreadsheet in Apps Script. Opens in new tab .<br>11:49 Bear with me here. Get sheet by name. Chat. Oops, Let me that up. Chat dot get range Understand how to define and use ranges in your sheets. Opens in new tab . Wait, actually variable?<br>12:07 No, no. Spreadsheet Get familiar with the basics of Google Sheets. Opens in new tab App first dot insert. No, we get active spreadsheet Learn about accessing the active spreadsheet in Apps Script. Opens in new tab . We want to do it right on this chat.<br>12:20 Let's take this, make it a variable chat sheet equals that.

So we can just do chat sheet dot insert row Find out how to insert a row in Google Sheets. Opens in new tab after three.<br>12:38 I think that's where Understand how to use the QUERY function with WHERE clauses. Opens in new tab we want to put it. So now after the third row, there's gonna be a new row underneath it here on four.<br>12:48 We're gonna insert there. Then we're gonna do chat sheet dot get range Understand how to define and use ranges in your sheets. Opens in new tab . We want row four cause we're gonna go to third row.<br>13:06 That's gonna be the fourth row. Column two, we're gonna set value is going to be our question, right? Is our question here.<br>13:22 And then our answer, chat answer. We're gonna do chat sheet dot get range Understand how to define and use ranges in your sheets. Opens in new tab three, no column five, three.

Ah, we want to add two insert rows Learn how to insert multiple rows at once in your sheets. Opens in new tab .<br>13:41 I think we want rows, insert rows Learn how to insert multiple rows at once in your sheets. Opens in new tab . How many we want to insert two rows and we're gonna insert the question and then we're gonna have the response.<br>13:52 We want actually probably the question and then the answer, right? Hmm. Dot set value Chat answer, right? So if this all works correctly by clicking this button chat where Understand how to use the QUERY function with WHERE clauses. Opens in new tab you have to assign here hit command, click that three button assign script chat.<br>14:18 Okay? Now, if all works correctly we're gonna insert two rows.

We're gonna send this question to the api, get it back and insert the, the answer here.<br>14:30 Okay? If, if this all runs okay, we need to authorize authorization Understand the authorization process for Google Apps Script. Opens in new tab is required, it's like 15 minutes into this video and you're like, is the guy gonna get there?<br>14:39 Okay, we gotta add all these, correct? We got an external service, Google Docs, Google Sheets, Google Drive.

Sure, we'll allow it all.<br>14:48 And I think we have to do that one more time because I, I think authorization Understand the authorization process for Google Apps Script. Opens in new tab first and then does it work?<br>14:55 Oh, we just, oh my god, I, I screwed up where Understand how to use the QUERY function with WHERE clauses. Opens in new tab we're en entering this and the oh my god. And also the we can't see it.<br>15:05 We need what is this? The, oh my god. According to studies. Okay, so we got some answer. The API worked where Understand how to use the QUERY function with WHERE clauses. Opens in new tab we put it is wrong.<br>15:16 Okay? Five two. What a weird thing to get wrong. I didn't check that, right? Okay.

But now, oh, and it doesn't, doesn't delete the question from here, so, or so we gotta do that b2.<br>15:28 Let's do that right now. Chat sheet. Chat sheet. Do get range Understand how to define and use ranges in your sheets. Opens in new tab b2, right dot clear content Discover how to clear content from cells in Google Sheets. Opens in new tab . That's all I want to do.<br>15:44 I think that's all I want to do. Alright. What do you think of Excel Learn about working with XLSX files in Google Sheets. Opens in new tab ? Ooh, let's ask that we insert.<br>16:01 Is it gonna work? We in, I think we have to change this text again. Let's try this. &lt;laugh&gt; Excel Learn about working with XLSX files in Google Sheets. Opens in new tab is a powerful tool, but what about, but what about Google Sheets?<br>16:14 Click leave the arrow button here. Ooh. Is it running?

Like, this is really exciting. Google Sheets is a powerful Yeah, look at this.<br>16:23 We have chatty PT working inside of a Google sheet. Isn't this cool? Google Sheets is a powerful and popular spreadsheet Get familiar with the basics of Google Sheets. Opens in new tab application, right?<br>16:31 Oh, awesome. Okay.

So in this exact example in Cheche PD clone Find out how to clone sheets or data in Google Sheets. Opens in new tab , we're using open AI's example here of a chat with this playground.<br>16:43 And in their playground, it seems as though the next prompt is taking in the data Get tips on managing data effectively in Google Sheets. Opens in new tab that's already inside of here.<br>16:51 So the prompt is actually remembering all of this stuff, and I think we're gonna have to do that in order for this to be a real tried and true chat.<br>17:01 G p t, it's gotta remember our conversation and build upon it, right?

So what I'm gonna do in the next video is I'm gonna figure that out and we're gonna add in to here some more of the conversation.<br>17:13 So we'll do that in the next video.</div>