Hey there stranger!

Sign up to get access.

ChatGPT Clone in Google Sheets Part 1

About this Tutorial

In this tutorial, learn how to create a ChatGPT-like interface using Google Sheets and Apps Script. Record your questions and get answers from OpenAI's GPT-3 API. Follow this step-by-step guide to build your own interactive GPT-3 interface.

  • Style a sheet like ChatGPT. Use the correct colors and interface design.
  • Add new rows to record the questions/prompts.
  • Add rows for the responses, and answers we get from OpenAI API.

Video Transcript

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.
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.
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.
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.
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.
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 of design and look at it a little bit and see what we can do in a Google sheet.
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 and give it a go.
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.
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.
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.
01:47 Okay? So our curl is the same V1 completions. Our API key is inside of an a, a sheet called API key.
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.
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.
02:20 Where 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.
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.
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.
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.
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.
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.
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.
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.
03:44 <laugh> 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?
03:54 Oh, I don't, I spelled that weird. What is the next thing? Af next thing after G P T three submit.
04:07 G p t four. Okay, so we have this sort of chat, right? This chat going back and forth. It's gonna ask us something.
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.
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.
04:32 And we need to give it a prompt, which is this prompt, variable prompt, actually, yeah, let's kill that prompt there.
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.
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, right?
04:58 Question. And we want to put it right here inside of this. Put two quotes. Put two quotes, put a plus plus.
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.
05:18 What is the number of stars in a galaxy? I'm gonna ask that question to chat. G p T, right? Mm.
05:30 The now we're getting a reply back, right? But now this is really cool, right? We can ask a question. We can also use the power of Google Sheets to ask a question.
05:38 What is the max number 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.
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.
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 here in Google Sheets and App Script.
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.
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?
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.
06:50 We're going to refresh the page, refresh the whole sheet, and we're gonna get our custom menu there. We're gonna delete this.
06:58 Let's work on a little bit of the chat. And what we want to do, I think this is the idea.
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.
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.
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.
07:34 Okay, so I got a screenshot, actually, I went in and got an image of chat pt. Let's load it up in here.
07:42 And we're just gonna get the color scheme. We want this background color, okay? We don't want any of these kinds of extra stuff.
07:52 We actually sort of want just three columns, right? We want not very many rows, but this background color custom is going to be this.
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.
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.
08:32 That looks cool, right? This looks like chat. G p t, right? Sheet chat. G p t and a Google sheet.
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?
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.
09:16 Api. Then we want to get back the answer and then delete this question, right? Is that how chat tv works?
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.
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 for that.
09:52 So here it's this what is this called? Arrows. See if we can insert image over cells. I'm going to drag this over there.
10:14 I had to redo that, but here we got a button. Gonna move that over here. Make it a little bit smaller.
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.
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?
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?
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.
10:58 We're gonna get our variable question from spreadsheet, app dot get sheet by name. Our name is gonna be chat. I think it's lower.
11:10 Nope, it's app, absolutely capital chat dot get range. The range is going to be b2. Get value. So now we can get the value of that chat of the chat.
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.
11:38 Chat answer equals this. What do we wanna do? We don't wanna return it. We actually want to go spreadsheet app dot get active spreadsheet.
11:49 Bear with me here. Get sheet by name. Chat. Oops, Let me that up. Chat dot get range. Wait, actually variable?
12:07 No, no. Spreadsheet App first dot insert. No, we get active spreadsheet. We want to do it right on this chat.
12:20 Let's take this, make it a variable chat sheet equals that. So we can just do chat sheet dot insert row after three.
12:38 I think that's where we want to put it. So now after the third row, there's gonna be a new row underneath it here on four.
12:48 We're gonna insert there. Then we're gonna do chat sheet dot get range. We want row four cause we're gonna go to third row.
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.
13:22 And then our answer, chat answer. We're gonna do chat sheet dot get range three, no column five, three. Ah, we want to add two insert rows.
13:41 I think we want rows, insert rows. How many we want to insert two rows and we're gonna insert the question and then we're gonna have the response.
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 you have to assign here hit command, click that three button assign script chat.
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.
14:30 Okay? If, if this all runs okay, we need to authorize authorization is required, it's like 15 minutes into this video and you're like, is the guy gonna get there?
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.
14:48 And I think we have to do that one more time because I, I think authorization first and then does it work?
14:55 Oh, we just, oh my god, I, I screwed up where we're en entering this and the oh my god. And also the we can't see it.
15:05 We need what is this? The, oh my god. According to studies. Okay, so we got some answer. The API worked where we put it is wrong.
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.
15:28 Let's do that right now. Chat sheet. Chat sheet. Do get range b2, right dot clear content. That's all I want to do.
15:44 I think that's all I want to do. Alright. What do you think of Excel? Ooh, let's ask that we insert.
16:01 Is it gonna work? We in, I think we have to change this text again. Let's try this. <laugh> Excel is a powerful tool, but what about, but what about Google Sheets?
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.
16:23 We have chatty PT working inside of a Google sheet. Isn't this cool? Google Sheets is a powerful and popular spreadsheet application, right?
16:31 Oh, awesome. Okay. So in this exact example in Cheche PD clone, we're using open AI's example here of a chat with this playground.
16:43 And in their playground, it seems as though the next prompt is taking in the data that's already inside of here.
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.
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.
17:13 So we'll do that in the next video.


Sheet Stories / Video Notes + ADDED: Email Notifications



ChatGPT Clone in Google Sheets Part 2

Create an Internal Google Sheets Add-on

Why Different Cell References in AI Integration in Sheets?

Show Sheet Tabs Based on Edit

Add Title Case to Google Sheets

Getting Started Coding in Apps Script

How to Power Testimonials with Google Forms and Sheets

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

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

Embed a Headline in a Website from Google Sheets

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