This tutorial is available for all users. Start with a 7 Day Free Trial today and get access.

Hey there stranger!

Let's not be strangers any more. Start a FREE TRIAL today to view this tutorial, right now.

ChatGPT Clone in Google Sheets Part 2

About this Tutorial

We fix the problem of OpenAI's API not remembering our last questions. And we label the text as Human and AI. I also add an archive Chat and new Chat function to create a new chat fresh.

Video Transcript

00:01 All right, well we have made chat c p t in Google Sheets, but I think there's a few more things we can do to make this just a little bit better.
00:10 One thing is I can't really tell right now what is human, what is, I mean, I can tell what is human versus AI versus like the questions, but if we get into like a real dialogue, right?
00:23 It's really hard to tell. So I want to label these human and ai or chat G P t I want to also make it so that we can have multiple chats.
00:33 One of the new interfaces in chat G P T is you can create a new chat and then you can go back to old chats.
00:38 So I wanna do that and instead of enter chat here, I'm gonna create, first off a couple more items, add item, and I'm gonna call it new chat or create new chat maybe.
00:56 And what I wanna do is I'm gonna label this well, I wanna make a copy, duplicate it, and I'm gonna call it template.
01:07 Then I'm gonna delete all the stuff here. We're actually delete a bunch of rows maybe, and what else can we do?
01:18 So we're gonna copy this and call in new chat and then make the that new chat active and then make the active cell b2.
01:27 So we'll do this, we'll do new chat and we'll call it new chat function, new chat. All right. And when we click that button, what do we want to do?
01:41 First off, we want to duplicate a template. So we'll do variable template equals spreadsheet, app dot get active spreadsheet dot, get sheet by name and call it template.
01:58 Is it? I had a capital T there. Gotta make sure temp lit. All right.  Template, template dot copy two. Now here's the weird thing is we need to copy this to its own, to the own destination.
02:17 Like the, the sheet we're in. So I think think we do spreadsheet, app dot get active spreadsheet. I think we do that.
02:30 Let's double check. We're gonna save it, close it. We're going to copy open it again or refresh it so that on open in our menu, we get that function.
02:44 And we're gonna look at, in case we have any errors, we'll see create new chat. Should say copy of template.
02:51 Perfect. Now we wanna n name it. How do we set the new name is we probably do variable new chat.
03:03 New page equals that. And then new page dot. Can we set the name to chat? Okay, because we want chat to be the one here.
03:23 We don't want this to be chat. We want the copy to be chat, right? Let's delete that. We have a template, we have chat.
03:30 Maybe we need also like archive chat or like maybe when we create new chat, we variable chat sheet equals spreadsheet app dot get active spreadsheet, get Active spreadsheet, get sheet by name.
03:51 We want to make sure that chat is renamed. Chat sheet dot setnam is chat. And then we're gonna do plus timestamp.
04:08 Actually we can just do new date there. Okay, so we know that whatever, if there's a chat that exists, we're gonna rename it to chat plus the timestamp.
04:19 Then the template. We're gonna take the template that we have and we're gonna make it, we're, we're gonna copy it and we're gonna name that chat.
04:28 Ideally, that's what's gonna happen, hopefully. Let's see. So we have chat now create new chat that renames it and we have chat over here.
04:38 But now we need to make chat spreadsheet, app dot get sheet by. We need to make it active, get active spreadsheet dot, get sheet by name.
04:50 Chat dot. I think we can do act. I think we can just do activate. We might be able to get away with that or we might have to do set active.
05:03 Let's try to just do activate. Let's, so now we have this big old timestamp. We probably don't want all of it, but it's okay for this.
05:16 We'll create new chat. We rename it and now we have a new chat. Great. And now this, if I command click, this should still be assigned.
05:31 Yeah, that's still the same, same. The that in that image is duplicated as well. So we're gonna ask a question.
05:40 Hi, what's your name? We're gonna hit this button, Running the script. You can call me open AI assistant. Cool. Now we need to set these as human and ai, right?
05:57 So we'll go through our chat, we'll go to the bottom where we return stuff. We will set a value for chat sheet dot get range.
06:11 The row is gonna be four. The column is gonna be one. This is going to be set value, and it's gonna be the question is human.
06:24 And then the same exact thing, copy paste. But row five will be ai. Now as we go, what's your middle name?
06:43 Hit the, enter the, the button. Now we have human and ai. Cool. Maybe we can style that a little differently.
06:54 That maybe we can also add colons to. That might be nice. Should I do ai? No. Capital ai? I don't know.
07:06 We'll do that. I don't have a minimum name. However, I cannot <laugh>, he really wants to help me. What tasks can you help me with?
07:15 Hit that button. Ooh, it's exciting, right? What tests? I can help you with various tasks. Let's make sure this template has I think we need, oh, it is this, the text wrapping is correct.
07:35 Hmm. It's gonna be a bit of an issue, right? Cause it doesn't look very good when it enters that data in there.
07:40 It doesn't automatically. Do you have any? So now this feels like a chat, right? But it's, I know, I know on the backend what's going on is that this chat is only, the prompt is only the exact question that I ask.
07:54 Okay? So it's not really, the prompt doesn't really know that I have some other questions and the answers that it's given me.
08:03 So I want to insert here some of the past question answers. If there are past question and answers and what they're gonna be is there gonna be this b a a four and B four, A five and B five?
08:27 And we probably want to limit it maybe to like three or four. Because if we got a lot of text in there, it's just gonna cost a lot in prompt tokens.
08:40 So one easy thing to do, maybe let's do the easy thing first and then fix it as we go. So we wanna get past questions.
08:52 Is going to be, this should be chat. Actually, I copied the wrong one. That's the a p i key. Here's the question.
09:02 Okay, let's write variable past question and let's do ver the same thing, but past answer. This is going to be a, what was it not a three, A four was the question and a five was the answer.
09:28 Wait, no, that's just, we know what that is. So we actually B five, this needs to be B four. All right, let's do that again, but it'll be six and seven.
09:42 Hmm. Okay, so now we can add these in here, but we don't wanna necessarily add, could we add them blank?
09:52 And is it gonna like not be a bad thing? Sort of. Okay, so I think, I think I thought of something.
10:05 Okay, so we know how big the template is. So we know that there are eight rows in the template before anything has been asked.
10:16 So we can guess if some, if the, if there's more rows than eight in this sheet, then we have some past questions and answers.
10:24 We don't necessarily know how many. So we can do something here. So I'm gonna pause this part cuz this we don't need, we can, we need those, but we don't necessarily need to do it in this piece by piece.
10:35 We can get it all at once. So if let's do variable row count equals we can also fix this chat sheet equals all of this.
10:54 So we don't need to do that again and again. Chat sheet chat sheet dot get max rose is going to get us the number of rows, right?
11:06 So if row counts greater than eight, then we're gonna do something else other than this else. It's gonna do, we're gonna do this.
11:30 Wait, actually we can do less than eight then the variable past questions Equals we're just gonna have nothing here. So we can add right here, plus past questions.
11:48 And then I think we can just need to for figure out how to format this human and ai. The last part is going to be the same, the last like question, right?
11:58 We're gonna put in the human question and then the ai dot colon, that's gonna stay the same, but our previous questions need to, I think be here.
12:10 And if the row count is less than eight, then variable past questions is just gonna be quotes, nothing. It's not gonna be anything.
12:24 But if it's greater else, variable past questions, it's equal to. Now we have to do sort of a four loop, I think.
12:37 I think we can do an array. Hmm, no, we don't want it to be an array. We want it to be a string.
12:46 Okay, here we go. Four. I equals zero I is less than, we actually only want like three or four, right?
13:02 Oh, I got an idea. Actually we can, I'm gonna, I'm gonna do this super valley. If you're watching this and trying to learn how to program, this might not be the best way, but row count is Equal to 10, right?
13:14 It's gonna be two at a time. Now we know there's only one. So we can do something here and we're gonna do variable past questions as equal to this.
13:23 And then if row count is equal to 12, we're gonna do something else here. Not else, but we're gonna do variable There.
13:34 So we need to figure what this is. It's probably gonna be question one. Question human question each question. AI answer equals check sheet dot get range.
14:03 What Was this again? It's going to be, let's look at a past one. B four is the question. B five is the answer.
14:18 B five is the answer. And now this is equal to the slash nhu. We do have a space there, human plus question plus in AI plus AI answer.
14:46 So now we're inserting if the ro count is exactly 10, we're gonna insert the past question and answer, oh, what are we doing?
14:54 Line 36. Okay. And then if we need two of them, we'll do exactly the same thing, except we're going to add another one.
15:10 Or actually it's gonna be greater than 10. We only wanna have two. I I think it's gonna be outta the bounds of this particular video if we're gonna do more than two.
15:20 So we'll just add two and let's see if this actually works right. Go through all of this and find out it doesn't work.
15:28 Would be crazy, right? Each Question two, actually one, one plus we can add all of this here. One. Now I think that is it.
15:53 So now let's look at our current chat. Our current chat has more than, you know, 10. So it's should grab in all of this.
16:03 What was the last thing I asked? Let's See. Chat sheet does not defined details. <laugh>, okay we just need to find all of the, where, where is all of this?
16:26 So maybe in here, this variable or maybe this needs to be constant. Sorry, let's try this again. Okay, that wasn't it.
16:41 Hmm. See the executions, see if there's a, oh, is there, this is chat, right? That is chat sheet should be telling me what line?
16:51 No. Ah, there is 37. Let's look at this line. 37 chat sheet. Oh, <laugh>. It's supposed to be a capital S CHT sheet.
17:07 So actually we probably didn't even need these. These were, this is overkill. How many chat chat sheets do we need?
17:13 Do, do, do, do, do. So we had it up here, so we don't need it here. Should not need it here.
17:22 And if we see that again, we'll no grab it. Okay. Dismiss, Do, Do, can I read properties? Undefined get range.
17:31 Let's look at the error again. Executions. That was my silly mistake. I accidentally deleted an extra chat. One. So that should work.
17:41 Now. Let's see, what was the last thing? US range. That's not actually true. I mean, what? <laugh> It's nonsensical. Can I have a conversation with you?
17:59 I mean, tech, it seems like it's working, right? Do we really need to go through this? But yeah absolutely. I'm here to help.
18:05 So write a tweet about chat, G p T and Google sheets. Let's See. Nice. Make it shorter. Hmm. All right, this is strange range.
18:32 Ah, I see an error that I did here is I'm rewriting over this. This should not be B four and B five.
18:38 Again, this should be B six and seven. All right, let's see if this fixes it a little bit. Write a tweet.
18:48 A tweet hook about Google sheets. Write A Write it shorter. Ah, what range do you need? Something weird about the range?
19:12 Oh, is it? Oh, this is so stupid. I can believe I did this. Okay, so this is get range. We need to get value.
19:22 Oh my god, that's, I can't believe that's that's so funny that I did that. All right, value. Alright, this is gonna work because it's <laugh>.
19:29 This is exciting. Dot get value. We need the value, not just the range. I'm so excited. All right, now let's see.
19:41 Will it work? All right, <laugh>, let's try this again. Write a tweet about Google Sheets. Hit Yep. Mm Running that script.
19:56 Get organized, work smart. Write it shorter please. It is shorter. Take out the hash text. Nice. So now we have a pretty cool implementation of chat G p t coming in, in that Google sheet.
20:22 We now are able to create more chats, save our chats. We are also able to add in the questions and answers that we're getting into the prompt a little bit, right?
20:34 We haven't really done it a programmatic way. I sort of just hacked this together. It's only giving up to the last two questions and answers, but I think for this video, this is a pretty cool place we're in Now we can create new chats, save our old ones, archive our old ones based on our timestamp of what, when they're times when they're ended.
20:54 If you are looking to use this, go ahead and make a copy and try it out and you'll need your own API key.


Sheet Stories / Video Notes + ADDED: Email Notifications


ChatGPT Clone in Google Sheets Part 2


Why Different Cell References in AI Integration in Sheets?

Show Sheet Tabs Based on Edit

Add Title Case to Google Sheets

How to Power Testimonials with Google Forms and Sheets

Getting Started Coding in Apps Script

Seek Errors When Coding Apps Script

Think Like a Programmer: Develop The Mindset of an Apps Script Coder

ChatGPT Clone in Google Sheets Part 1

Embed a Number in a Website from a Google Sheet

Create Navigation Like A Book or Presentation

Add Click Tracking To Your Google Sheets | Bitly in a Google Sheet


Hold a Giveaway Raffle in a Google Sheet

Embed a Headline in a Website from Google Sheets

Capture Emails from Website Form to a Google Sheet (Without Zapier)

Quickstart Tutorial OpenAI API in Google Sheets

Create a new Spreadsheet from just a Name in a Sheet.


Bjarne Asks: Can I show the Last Time of the Last Edit in a sheet?


Email Yourself a Cell from a Google Sheet, Every Day

OpenSea Data Inside Sheets

Create an Email Campaign Stats Calculator


Twitter App Clone in a Google Sheet

Dylan Asks: How to Automatically Delete Rows If Cell Contains Value

Highlight Row as You Move Your Cell Selection

Create a Timer with Apps Script

LinkTree in a Google Sheet


Password Protecting Data In a Google Sheet

Automatic Weekly Backup of Google Sheets

Create a CPM Custom Function (Create Better Calculators!)

Move Entire Row when a Cell is changed to "Yes" - The $75,000 Google Script


What Can You Automate in Google Sheets? Every single trigger available to Google Sheet users

Sync Two Tabs Without ImportRange()

Google Sheets Stories? No! But we'll add timestamped video notes to your google sheets.


Password Protecting Data In a Google Sheet Part 2 The Basics

Benoit Asks: How to Convert Case


Learn to Code in Google Sheets, For Programmers

Add a Checkbox to Turn on Dark Mode


Write Your First Script


Find Keywords in Any Column. Create quick search dropdown to find keywords


Basic CRM - Add a Powerful Script To Move Row Based on Status

How To Improve: 1,000 Business ideas: Business Idea Generator


Let's Make a Bookmarklet!


Troubleshooting Bitly in a Google Sheet Script


Unique Features - Design a Better Dashboard Part 2


How To Set Up Stripe Webhook to Google Sheets with Google Script


How to Edit a Macro


Sheet Stories / Video Notes + Clear 24 Hour Old Videos


Add A Timestamp to Task Lists (without Now Formula)


Make your Custom Functions Like Native Functions | Custom Function Autocomplete


Create a Changelog Between 2 Cells Custom Function | To learn Double For Loop


New Syntax for WhatChanged Formula in Google Script


How to Record Macros


2 Ways to Delete Lines Quickly (CAREFUL, it's a script!)


Deep Inside Dark Habits Google Script


How to Trigger Macros Daily


5 Ways to Create Coupon Codes | Create UUIDs

Create a Radio Button From Checkboxes Using Google Apps Script