00:00 Hello, welcome. Would you like to write with AI in the sheet? Would you like to write literally inside of Google Sheets, like equals ai?
00:09 And then have some prompt here prompt <laugh> here and be able to write as much prompt as you want. We're gonna have a name here.
00:18 It's gonna give us an error right now cuz it doesn't exist. But let's, do you think it would be cool, do you think we wanna write a prompt here?
00:23 Like, Hey, I think you are great ai can you explain how you work and then be able to do equals AI and then ask AI this question in a three?
00:38 Wouldn't that be cool? Right? Right now we have unknown function ai, but let's actually go build it. Let's go to extensions app script.
00:47 Now in some other tutorials I might have I already have a video walking step by step, how to take open AI's API and put it into app script.
00:58 So what I thought today I would do is I have this script and I'm gonna hit save. I'm gonna check that it works.
01:07 We're gonna check right now that it works. And once it works, I'm gonna walk through every single line of this and tell you exactly what that line does.
01:15 And if I don't know what it does, we'll delete it and we'll see if it still works. Okay? Let's see.
01:19 So all I did is I have function AI prompt here. Now I'm loading here. What I need to do first is I see that it says variable API key.
01:32 Oh, let's go up back to sheet one. You should give us an error. What is that? Error cannot read. Properties of null.
01:37 And it says reading Get range line three. We go to our app script and we go line three was actually here.
01:45 This variable API key, we, we'll fix it. Now it says line two, right? Okay, why does it say that? It says that because API key equals spreadsheet app dot get active spreadsheet.
01:56 So we're saying just grab something from a spreadsheet, get the sheet name sheet by name, API key, get the a, a one and get the value that's inside there.
02:06 Well, we didn't, we don't have a sheet called API key. So we'll, we'll go create one API key. Great. Now we have a sheet that says API key.
02:15 Let's go back to sheet one, see what our error says. Now it says cannot read properties. Still know. Why is that?
02:21 Probably because <laugh>, probably because it's blank. We can also delete all of this and delete all this. Or we can use tiny sheets to create one, right?
02:31 But we only need a one here and we need an API key. I'm gonna go grab an API key from ai.
02:41 All right, so I'm here. You can see my secret. I'm gonna delete this by the time you see the, by the time you see this video.
02:47 And we could absolutely put it right here in quotes, but I won't do that. I wanna show you the power of Google sheet.
02:54 So we're gonna have this spreadsheet app, and we're going to go to a one here. We're Gonna gonna pop it in here.
03:00 Let's see what happens. Now, do these errors change? They say still cannot read properties, but let it give it a second or what we can do is cut this, hit enter and paste it again.
03:14 And we can do the same here. We got no response there. Do, do, do, do we get no response? What is going on?
03:24 Hmm. All right, so we just played around with the prompt a little bit. I didn't change any of the code yet, but I realized that we need to actually have some prompt for the ai.
03:35 So if I just go back, if I just have, can you explain how you work and have a colon that still didn't do anything, but if I do Q colon, can you explain how you work Now?
03:48 I'm getting a response. It'll be loading. I like to approach test in a methodical organizational why? Let's move this over.
03:56 Let's make this there. Wow, that is AI returning us something. So let's walk through this thing. It works, right? So we have function ai.
04:07 Now when we write a the word function, we're we're gonna create a function. We need the same syntax, we need some word or group of words.
04:13 If you have a group of words, you can do camel case. So it's like, if you wanna do like, get AI response response.
04:21 Now you see here get ai, the capital letters A and the capital letters are moving, go in a little bit there.
04:28 But if we can just use ai right now, cuz there is no function ai, there's like far 504 formulas. There's a couple of save values, but you can pretty much use almost any words you want here.
04:39 And if you cannot, if you're thinking that the way you named it is wrong, then just add some more characters to get it away from saved names or saved letter arrangements, like value or name.
04:52 But now we have a prompt, and now this prompt is gonna be down here to hear prompt and is gonna be whatever we put into the cell.
04:58 So we can reference another cell or we can put in quotes, we can have the exact same answer. If we do this, we do equals ai.
05:08 And then in quotes, have the prompt there. And we're gonna get probably slightly different answer here once it gets it.
05:17 Okay, it's the same. Well that's because of the temperature, but we'll get to that in a second. So what is going on here?
05:23 So we have function ai and we have the prompt come in here. We might want to have other things like temperature we want, might wanna play with.
05:32 So we have the variable, we sort of explained that already went through it very quickly. We have this variable data.
05:37 Where do we use this? We can actually copy and then command F and search for it somewhere else. There it is.
05:42 So it it's taking all of the data that's in from this curly bracket over to this curly bracket. It's taking all of that and putting it into a json string of fine.
05:55 Just saying, give that to me in a string. And that's the payload of the api. Okay? And here we got the options as well here, which we will see down here.
06:06 Let's move my face over here. All right, so we're saying text is Da Vinci. Oh, sorry, the model is text da Vinci.
06:15 The prompt is literally whatever we put up here, prompt. We can even add a prompt inside here and say, you know we can have this maybe question, maybe we put the queue here and then we add in here a prompt.
06:34 In order for this to work, we need a plus sign in front and behind it so it can concatenate all that stuff.
06:39 It can put it together the Q for question, the prompt, the question we want and the answer here. And now we don't need the Q and A here, so we can just delete those and we just need the prompt the question.
06:53 We could even change this word from prompt to question and say, Ew, just ask a question. Maybe we even change AI to ask ai.
07:04 We can do all of that and we're gonna change. See you now we have a name error again, we're gonna do equals ask ai.
07:12 And then a three here, you can delete that. Load it. There we go. Wow. What's a typical day for a social media manager?
07:26 We can sort of ask any questions we want, right? And we're gonna get a response. Cool. So yeah, we change a bunch of stuff here.
07:36 We have a prompt, we now have a question. We have sort of some text around it. This temperature, it says it's zero and it's gonna always be zero unless we change it to a variable.
07:45 And we're gonna call this temp and then we're gonna add up here temp. Now what we can do is ask this AI a question and then we can change the temperature.
07:54 So let's save it, go back to here and instead of a three, we're gonna put in zero. So the same syntax, the the A three before the comma is the question.
08:09 And then zero is the temperature. Same exact syntax is over here where it's question comma, temp, or temperature. And look, now we're getting something, but now let's say we want to change the temperature here.
08:23 We wanna put a zero here. I wanna say temp in question. And we want to change this to maybe equals this plus 0.01.
08:36 And now we can take the same exact question. Take this temperature down to like a hundred, almost a hun. There we go up to one.
08:48 Now we can see how this temperature, we can test this AI by changing the temperature. Now we're testing what does The temperature actually do?
08:56 So we can copy and paste this all the way down to the one. We're gonna do a hundred calls now to open ai, this is gonna cost me like $2.
09:08 I dunno, maybe we can load all of these questions. Look at this is the, the answer for all of these.
09:15 Oh, we messed something up. We totally messed it up. Okay, we need to change this. Zero to b3. Oh Jesus.
09:22 Okay, do that all again. <laugh>. Now we actually have the temperature going here. It's gonna change typical day for gives us the answer.
09:32 Let's see, did we overload it? Did we overload it? Is it gonna give us a answer? Ooh, this is exciting.
09:40 Ooh, there we go. We got one. Didn't change it much. It's not changing it very much at all. Oh, there we go.
09:51 We got a few words difference. We got a few words difference saying up to date on latest trends in practices.
09:57 Wow, okay, now as we go, we can see it many, many, many different, more differences. It still starts the same, a typical day because we ask what's a typical day?
10:04 But the end is just like a little bit crazy, a little bit more, a little bit more, a little bit more.
10:10 All right, cool, cool, cool. See now we're getting somewhere. Now we've created this like sort of AI tester this max tokens.
10:19 We can also change from 100 to let's talk it. Let's talk it, let's call it tokens as a variable. Let's add up here tokens.
10:29 And we're gonna save and we're gonna go change exactly the same as we did here, but we're gonna do 100 to 200, 300.
10:40 I'm gonna actually delete most of these cuz I don't think we need all of these. I think we need a few test ones, and I'm gonna write tokens to and temp here.
10:54 We need like 0.0, 0.5 and one. Let's do that and then do 1000 here and 500 here. Let's just test three of 'em.
11:05 I don't wanna do hundred calls every time we change something. We do need to change this though. We need a comma token.
11:14 And now we can see really big difference, right? Ooh, it's gonna be, it's gonna be really big difference. Okay? It's not that much difference <laugh>, it's not that.
11:23 All right? But that's interesting, right? We changed it from 100 to 1,010 x difference and didn't really have to take, and we changed temperature from zero to one.
11:30 Probably a poor prompt. Now we can test our prompts. Now we can use Google sheets and test like maybe instead of what's a typical day for a social media manager, maybe it's what's a typical day for a business analyst.
11:46 And then we ask what's a typical day for a social media coordinator, Or I don't know how to say coordinate door.
11:57 There we go. So now we're changing these things that were like hard coded into this code, right into different parts of our sheets.
12:09 So now we're really integrating the, the best parts of this sheet. We're creating a playground of our own making in here.
12:15 We can add in different text, we can replace text, substitute text we can do math on some of this, like temperature and tokens maybe.
12:26 We can change top p frequency, penalty, presence, penalty. These are all similar things that we can tweak. We can turn this if we really wanted to.
12:38 We have this options get we in APIs there are like get, and there are post requests. Get post is just we're asking, Hey, do you have this thing?
12:48 If so give it to me. If we change this to post that is for like websites or APIs where we're giving it information and it is going to write to its database some piece of information.
12:59 So in this case, we just wanna get the answer. So AI is really simple. We're always gonna have a get method at least right now.
13:05 We're always gonna have a get method. Content type is always gonna be application json n as you can see, we, you, we are using json, so that's good.
13:11 We, that's why we have JSON here, our headers, this is where we do our api. I key. We already had this written a p i key and we wrote it here.
13:21 We can hard code this absolutely, but we chose to get a value from a spreadsheet. Maybe we want to loop through some API keys for some particular reason.
13:31 We might wanna loop through different API keys. We can do that, right? But this is just gonna be authorization bearer and then plus the API key.
13:40 Pretty much simple text that's embedded into this curly bracket J S O N into headers. And inside of that, in that is inside of options and options is this part back here on the u url fetch up.
13:55 Honestly, all of the heavy lifting of APIs is in this URL fetch app dot fetch. If you ever wanna use an API inside of a Google sheet and you wanna use App Script, you're always going to use URL fetch app.
14:09 And you're always gonna write URL fetch app dot fetch. Now, here's an interesting thing for AI and for specifically the open ai api, we need to add this comma options.
14:25 And they showed us, if you watch the video quick start tutorial on Be Sheet Co. I walk through showing you in open AI's documentation.
14:34 I show you the playground, maybe not the playground, but their curl. If there's a curl, you can do it. And they have options there.
14:42 So we just took that and did it here. But here's a magical thing about a lot of other APIs. There are APIs including gum, roads, api, including even open C if you're working with NFTs open CS ap, there's a api, a URL that you can just fetch.
15:02 You can just get exactly the text a URL with just text, maybe adding a few different options and it'll give you back some information.
15:12 There's a lot of APIs out there. You don't even need an API key. You just need to know how to access it, how to create that j s o, how to access that JSON and get it out.
15:22 And now we're gonna go into that right now. So what we do is we have this U fetch up that is the heavy, heavy lifting of all of us.
15:29 Every api every time we're coding in here and we need an api, we're gonna be using your URL fetch app dot fetch, but it's a variable.
15:37 This is variable answer. And then the next line down, it says response equals answer dot get content text. All this is doing is saying in that J you're gonna get a response, you're gonna get some information back.
15:51 The get the, this whole thing is gonna go to that URL and say, give me whatever you got. Here's our my api, here's the information I want.
16:00 I'm supposed to give you whatever you give back. It's gonna be in this like format that's essentially, essentially text. It's some data, but we use dot get content text to just get the text out of that and say, just give me the text that you give me back.
16:15 Not all the message, not a bunch of other messages and stuff. And from that we wrap around it json dot parse.
16:23 What this allows us to do is now use notation to go deeper into the text just saying Js, o n js, o n, because of this variable here dot choices.
16:34 And then with bracken notation zero meaning the first thing of the choices that you give me back where there's a little bit of text that says choices and then it has some extra text there.
16:43 Literally it says choices. And we say, give me the first one and then of that first one, just gimme the text.
16:49 This is it. It's these three words here that parse through all of the response. We can't even see the response.
16:57 Actually, if we go here, this is the text, but let's change it to just the J S O N isn't that?
17:06 This is gonna be crazy, right? Let's see what happens. I don't know if this is actually gonna have some, I wonder what this actually looks like.
17:14 Might be nothing because it's not anything. But let's see if we can just do instead of return that let's return just the response.
17:29 Save that, see what that looks like if it gives us anything. Oh, there we go. So now you see the the, the J s O response.
17:38 What's really cool here too is if we go to js o Beautif Fire and in spreadsheet automation 1 0 1, I go through This much easier.
17:51 Oh, it says jsun data of not valid. I think it's because we have quotes here. I think it's these quotes from Google Sheets.
18:00 Darn you Google Sheets. There we go. Yeah, let me see if we can do this a different way. I think it's because of these double quotes.
18:14 Yeah, but really what this looks like is it's gonna parse it into these trees and it's gonna say there's this id here's text completion.
18:24 It gives us all this information. But you can see right here the word, I don't know if you can see it, the word choices.
18:29 Oh, this is horrible. Maybe go back here. I hope you can see this. It says the word choices right there.
18:37 And so what it's doing is we're gonna say in all of this, just give us choices. And then it's an array with, with brackets, square brackets and curly brackets.
18:46 That's called an array there. And it's an array of different things. We just want the first thing then of that just give us the text back.
18:53 And that's how we parse the response from open ai. So we have this choices. Let me bring this into the center.
19:02 We have this return json dot choices. So instead of response, we're gonna wrap that in json dot parse to give us the ability to parse it.
19:09 And then we're gonna parse it. And how we parse it is literally just writing dot choices dot text. So all of that gobbly goop and all that stuff is now no longer frustrating.
19:19 It is literally just JSON choices with a square brackets of zero, the text. Now we have a really cool prompt sort of testing area, little playground inside of Google Sheets that we can do a lot of really cool stuff with and access the Open AI api, get Da Vinci zero three.
19:39 We can also change this model to something like chat chip p t. If you change it to the chat chip p t 3.5 turbo you might have to give it a little different prompt, not so sure yet, but we'll be going through that maybe in some future videos.
19:52 Perhaps. Hopefully this was a fun video for you to parse through this api. And if you are looking for more information about accessing APIs, consider taking spreadsheet Automation 1 0 1.
20:07 Again, I have the full video course spreadsheet automation 1 0 1 here on Better sheets.co. I also do workshops from time to time.
20:14 So check out workshops and email me anytime if you have any questions about anything sheets related.