Hello. Welcome to this tutorial on adding open AI's API into Google Sheets. This is gonna be an interesting step-by-step process. It's going to basically, we're gonna build an application and I'm gonna show you how to integrate the API into App Script. But we're not going to. Open a api, open ais build your application, which is in node js.

We're gonna do this in Google Sheets and in app Script. So I'm gonna start with a sheet.new we're gonna start from scratch. We're gonna do open a I in Google Sheets.  getting, let's call it getting open AI into Google Sheets for now. All right. What do we wanna do? Well, let's talk about what we want to do, because there are actually some examples here.

There's on op open ai, there's q and a, there's some grammar correction. We can sort of do a lot of interesting things. But I wanna write, let's say, Let's say, let's, let's say we're gonna write a, I'm literally doing this off the cuff. We could just do q and a. If you're yelling at the screen right now, what to do, hit an email.

Send me an email of what you wanna see done in open AI in Google Sheets. Let me know what you can think of that you wanna do. We might do a q and a. We might do, like, let's write a tweet hook. I just did that earlier with someone else. So let's write like a tweet hook. We're not gonna use. The normal here, what they have as examples.

We wanna actually just go to q and a. I'm gonna open and playground. I'm gonna look at this. This is sort of what you can do as well to do. What I'm doing is we can see the view code here. If it's selected Python over here, just select Curl. This is a little easier for me to groc and show you how to change this into something we use in in App Script.

The thing we're gonna do in app Script is, , let's call this, let's, what kind of function are we gonna call it? Let's call it open ai. And in here what we wanna do is we want to return some answer. We're going to do a variable answer equals URL fetch, app fetch. And we want to get this curl, this, this . So what Curl does, why we change this to curl from Python is we're not using any libraries from Python.

We don't really know Python. We just know app script, and we can use a curl command as long as we can do a curl command to this is a CCE url. Command url. As long as we can do this, we can use URL fetch app to do exactly the same thing, but it's gonna take a little massaging as you'll see. We're gonna walk through each one, one of these steps to do this.

But first things first, we're gonna get the URL at the very top, and we're gonna just send it through fetch. Okay. Whoops. That's my <LAUGH> API queue, which we'll get in a hot second. How do we copy this? There we. . So we will need an API key, which I'll get into eventually. But now the extra thing, this is usually if you've seen other videos here on better sheets this is usually where we stop.

We sort of try to create a URL and then done, but in this case, we're gonna have to add some options.  over here we see what those options are. We have headers here. This is what this negative negative h hyphen H means. This is header content type application. Js o header here, authorization bear. And here's the key to this whole thing.

We need to get an API key. If you do not have an API key, you can get one over somewhere here. Personal. Where's my personal view? API keys right here. I just created. It says Last use never, cuz I haven't used it yet. Just create a new secret API key and actually I think I don't have it now. So we got, actually we're gonna do this, create a new secret API key.

I'm gonna delete this after this video. So we got it copied. We're gonna put it into a sheet. I'm gonna use tiny sheets to create a one by one sheet. I'm gonna call it API key, all lowercase API key. And right there I'm gonna paste it right. Okay, how do we get the API key here? Variable API key equals spreadsheet app dot get active spreadsheet dot get range A one.

Dot get. Nope, not get range. We skipped a step. Get sheet by name. API key dot get range. And in range we're gonna do a one and get value. So we have, this is going to get us our API key that we need. We're gonna call it variable API key, and we will use this later. All right, what else do we need? We need this header and content type.

So in our options here, variable options is equal to, we can actually use a Google app script, app script URL fetch app.

Okay. If we look at the class URL fetch app documentation here on Google Sheets, we can see that usually this is what we do. We get this response dot get content text. Actually, we should do that now. We are gonna do that answer dot content text. . But we're gonna add some options. Let's see. It's the pers Yeah, that's what we want.

What are the parameters we want? We could add. Let's look at this. U url. Fitch App. Options. Options Method, post payload. Here we go. That's what we need to do. So we're gonna go back here. Our options, variable options equals, what was that look like? Method. And we wanna do get,

we need to do payload. We will need payload.

We can just call this payload for now.

Here we go. We got this content type application json that's the same as, let's close that there. Content type application json. Perfect.

Add a, come at the end.

Do want all this data? Do we want to do that data? . Okay. We can look at the playground here. And we have this hyphen D hyphen D in curl means data should, no, I'm, I'm just telling you this. So this is going to be all of our data here. We can actually just copy paste this. I'm just gonna grab all of this and see what we can do with that in here.

Variable data equals all of this. Oh yeah, that's up. That's it. So it has actually much more text than I thought. But here's a prompt. We are going to do a different prompt, I think, but for this, let's just see if it'll work with this. We wanna insert a prompt from a sheet, so we'll get to that. But let's just see if this is gonna work, right?

We have our data here, we have our API key. Where do we need to put our API key? It's in this head. , we have headers here. We can add a JavaScript key value map of HDP headers for the request. So that should just go same as here, headers. And then we should have in here,

we need authorization. Hmm.

In, in the sort of single quotes. And then we're gonna have bearer space plus API key. That's where we use our API key, right? Right there bearer. And then the open API key in the header authorization. Cool. We have our data. All right, this looks good. We think we need to add a comma there. We can save this, see what happens.

We're gonna come back and change this prompt. But we're gonna use this one in the playground that, that we used. We can also, let's close this and see sum hit, submit. What's gonna happen?

Hmm. It's giving us. . Oh, we need, there we go. Right. What is it? Question. Oh, we need a question here. What is a, we will figure this out later, but let's see if this runs. So data's not being used. We didn't use data anywhere. I think this is supposed to be here. Payload. We can also look at this URL fetch app again.

I think we need to do jsun dot stringy data. Oops. Okay, let's see if this is gonna work. All we need to do is call open api, a open AI in here, equal open ai, and let's see if we get an error or if it works first time. I don't know. Oh my God, we got something. Alright, what does this look like? Let's wrap.

something null. We got a lot of stuff here, but we already, we have this. Okay, so this is json. We can do a JSON Beautif. Fire

json not valid. I think we have the extra quotes. Tree viewer. No, but this is Jsun and I think what we can do is grab.  part of this choices text is what we need. We're almost there. Wow. We got something. We got a call back from the api so we know this cuz we can refresh this and see. Did we? Yep. We last use it.

So the API works this way. We just need to get a response. I think we need to add at the end, the prompt A. Let's save that. I think that's all we need to. Let's see if something else comes up. There we go. We got text. Okay, this is cool. What we're gonna do now, , is we're gonna parse this a little bit. Let me add a few more.

Let's go in a little bit. We add a few more enters, so this is at the top. This content text comes back to us as a J S O N. We need to parse this. What do we gotta do? We gotta do this, move it variable. Response equals answer dot get context. We need to

let's do, call it jsun equals j S o dot parse response. And then I think what we need to do is JSON choices. If I do this, I'm doing this a little bit from memory, but the choices here at this Bren notation zero is important. If we don't do it it won't work. But we're doing dot choices because there's multiple choices here.

If you see, can see the text says choices, and then the first thing we need is text. We need the reply of text. Alright, let's save this and see what happens. This is exciting. We should be able to parse.  And it should. There we go. There's our answer. So now we need to move that prompt into something we can actually work with, cuz this is all just text, right?

So let's look at this. Let's cut, cut this and let's call it prompt and move it into the open AI prompt Save that we're getting. Should get an error here because we have nothing in here. But if we move that text.  here and we're like, equals open ai. A four. Now this is the prompt. I'm not showing you the whole thing cuz we, we saw it here and here's the answer.

So now let's see what else we can do. Let, let's see if we can just ask it. I know there's chat, G P T, which is sort of on top of open ai, but let's just ask a question. What is a telescope without all of that? , that whole prompt, right? Let's just ask a super simple prompt. What is a telescope and what happens here?

We can copy and paste using the power of Google Sheets. We can copy and paste open ai, our open AI into here, and now we get nothing, right? We can do q a, see if it's a different prompt. Prompts an answer. It's loading there. We. . So we can just ask a question. Q Colon. What is the telescope? A telescope. So let's call this, let's do another thing, right?

We can do one more step here and we can prepare the prompt inside of our script so that the user only has to ask a question so we can do question prompt. And then actually we're gonna change this to question.  and change this? No, we don't know. In gonna change that prompt. So we wanna do variable prompt equals we want to take Q question mark.

Not question mark. Oh my god. Colon plus question plus space answer. That's all we wanna do, right? The exact same thing, but we just wanna ask a. So now we can save this as question. Let's see. And instead of asking what is a telescope with the Q and the A here, we can ask, what is a star? And now equals question A five.

It's loading. Loading. Is it gonna. . Perfect. So now we have created a prompt in our app script that all we have to do is now create a custom function here that allows the user to enter what they want to enter, right? Maybe we want to write some hooks. So we want to add a prompt. Write a Wait. Actually, I wanna save this.

We'll, we'll save it. And, Pieced another one up here. So now we have question two questions. So we'll change this to write a tweet, write tweet. I'm gonna call this mm, subject, let's say, and the prompt is equal to, let's call this, I want you to write a tweet, a maximum. 280 characters write about and then plus the subject  plus.

Let's see if just that works. I don't know if that kind of prompt is going to work. , but We'll, we'll see. What do we want to, we wanna write a tweet about Google sheets. Googles write tweet a six. Hmm hmm. We're not getting anything back. I think it's because we don't really have like a prompt for it to do.

Write a tweet about.

Hmm. Starting with this , I don't know if this is gonna work.

Hmm.

Yeah. This is a bit of a, a prompt building that takes a bit of time to figure out. Hmm.

Okay, so we tried this. Let's see if this works. Just found this generate tweet medium. Hmm. It's still not working. Hmm.

I think it's because we need to add a  because it's a question. What are we doing here in the completions? I think this is asking a question, so there we go.  and the, let's see if it's under that length. B six a hundred thirty nine characters. Great. So now we were able to say the actual prompt is write a tweet about X, right?

Google sheets in less than 2,280 characters. We had to add this A, which is answer because we're using. This question example here. We can, you can play around in the playground. I did this for a while and I was able to like really figure out what kind of prompts and what kind of things work here.

I wanted to share with you one other thing about writing opening. I. Prompts in Google Sheets, or actually, there's a few things and one huge thing you need to figure out if you're watching this all the way through, thank God. Because what happens is if you leave the.  Custom function, you leave it in the sheet, it will call that custom function every 10 minutes.

Now, this doesn't sound like a lot because each sort of 750 words is something like 2 cents. Doesn't sound like a lot, but it could add up to about $80 a month. If you are leaving this these prompts for each one, meaning it's gonna call every 10 minutes, it's gonna have a call even though it'll give you the same.

Again and again. So what I would recommend, if you're doing this on your own and you're writing open AI prompts in here and trying to figure this, do not leave these in a sheet like this or delete your API key from your account. So that's another thing you can do is just delete the API key from your account.

Once you're done with that page, you're writing and you know it works. . There is other ways that you can do this. Like if you want to create a function in a custom menu and then run that function and it enters the response inside of a sheet cell, you can do that in order to, in order for the re it to only act once.

So if you only want it to act once and you don't want it to continually reapply this API call every 10 minutes.  Just have it enter data or copy and paste the values. So I'm just command C and then shift command V, copy and paste the values. Now this will never change because it's not gonna call the URL again, speaking of changing, if you're looking for open ai and, and G P T three kind of things to do more creative work, what you're gonna wanna do is change this tempera.

up beyond zero. You may want to go all the way up to one, so it's numbers between zero and one. So you have like 999 options here, right? You can have zero, you can have 0.01, you can have 0.1, you can have 0.5. You can have point. Nine. Nine, and you can have one temperature means like how creative do you want?

There's very specific language they use in the API documentation about really what temperature's doing, but generally speaking, it is how creative. Do you want each reply to be? If you want to be definitive, like if you want to just answer a question, you should probably have it at zero. But if you're looking for, like writing a poem about the city of Miami in the mode of Walt Whitman, you may wanna go up to like 0.5 or one.

Also, there's a max tokens here. I'm only using max tokens 100 because. Writing a few words, but if you're looking to produce, say, opening paragraphs or longer text, you can put this up to like 2000 I think in the API documentation, it talks about the the prompt plus the tokens for the reply. If you look, I can show you.

usage here. Where's usage? Usage? What we just did? Let's look at that. Hmm.

Look, you can see 21 requests. Where are we? I think this is here now to requests. You can see a prompt.  466 tokens. 17 tokens. So you can see your usage. If you want to see how, how many tokens did that really take? There's some other things you can do with max tokens here. I'm gonna keep that at 100.

Top P is another thing you can tweak here. Stopping is a tweak. You can do, say you want to write like 10 items of something, then have the stop, the 11 dot cuz it's gonna write 1, 2, 3, 4, 5. All the way to 10. , these are things you can.  tweak very easily by just entering the text, right? We can te change the temperature to one, we can change it to 0.4, anything we want, lemme keep it at zero and hit command save.

And that's sa command s not too safe, but you can also play around with these over on the right side of the playground. So I, I really think. The playground is fantastic to try stuff out. You can see different stop seq. You can add different stop sequences. Try 'em out. This best of is also available here.

It was not in the copy and paste API that I just did, but best of is available. Insert start text. You can have different probabilities as well. Lot of tweaking you can do here with api. Open api.  open AI is API . I'm never gonna get that right. You can also check different G P T three. So Curry Babbage, ada.

When I was working with this, I found that Da Vinci just. Most of the time. Got it right. Many. I'm working on something where it has to have the right answer most of the time. And in fact, it didn't have the answer sometimes, and I was like a little freaked out, but when I turned it to like ada, which is the cheapest and fastest one, it almost never had the right answer.

I was like, oh, okay. These really are definitely different. One extra thing you can do, if you are starting to use the open AI's api, you might not get into this. If you're doing something very specific and you need it to get better over time, then consider training data and doing fine tuning that I'm obviously not covering in this video.

Hopefully I've shared with you enough of the trials and the tribulations that you're gonna run into with open open AI's api. And you are more than welcome to go into this sheet. You're watching, you're a Better Sheets member, you're watching this video. Probably gonna just get that, call it Open AI API and Google Sheets.

It's probably the name of the title of this. Go into the sheet, go grab it. I'm gonna obviously delete my API key from here. So you're gonna need your own API key. And you can see right at.  prompt and everything here. Also, just the regular open ai and then also the question one where I added the Q and the A.

So you have full, you can totally use this. Feel free to use this. Check it out, edit it as you wish. We're using the completions api, which I might do some more videos about other things.  within Open AI and go ahead and search through. There's these great articles. There's now been, you know, a few, couple years of open a Open AI's api, so you might find some really cool prompts.

I know there's prompt. Base let's go find that if you wanna buy some prompts, cuz sometimes they're pretty hard to write very long prompts. So you can definitely go buy 'em for like two bucks a pup. That's a cool marketplace. Or you can just search for 'em. There's a bunch of tweets for open AI's prompts and now you know how to use open AI inside of a Google sheet.

Good luck.