OpenAI API Prompt with Array of Cells

Intermediate

About this Tutorial

Create a prompt to OpenAI with an array of cells

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Featured Formulas

CONCATENATE()

Video Transcript

<div>0:00 So you want to get an array of cells into this prompt, uh, let me show you how. So basically, uh, remember asked, I'm using this code here to get API access to open And I want to actually call the cells into, to the prompt. 0:17 Uh, more than one cell, because I'll show you what we just do sheet.new. Uh, I'm going to use this exact script again. 0:24 Again, this is at www.bettercheats.co slash snippet slash open a iAPI prompt in response. You can find it, you can use it immediately. 0:32 But let's put it right into our app script . We're just going to go to extensions.

App script . And we'll paste it right in here. 0:40 And here we go. So we have our function . We'll paste it right there. Save it. Uh, what we do need to do is make sure that we have an API key and API key there. 0:51 I'll do that. And so what we might want to do is write an email to Ronald about the tool of the project. 1:00 And so let's say we have our AI prompt. I'm ready to go.

Uhh what we can do simply is AI call the AI function and say hey umm get right a short email uhh short follow up email asking this per. 1:22 This person and we can put in quotes here put a ampersand b2 ampersand uhh if they are done with the project. 1:35 Thank you. Named and here again we'll do ampersand and we can access individual one but maybe we don't want to do all of this and I'll show you how to do an array into the prompt uh in a second but let's just look at what this will do umm. 1:51 Let's see if it will actually do it. Right?

We are not really saying uh what you might think and there we go. 1:59 We got it. What you might think you had to do was something like equals ai, in cell b2. There's a name and in c2. 2:13 There's a project right in email about c2. The project. To the person b2. And what you're gonna get is nothing. 2:30 It doesn't know the cell uhh information. And it's probably gonna even give us a message like hey, I don't know what you're talking about. 2:36 Um, but actually here it's actually funny. It gives us an email back.

I'm thinking that we can put that stuff in there. 2:43 Oh, we can probably. That's for another video. But here, how do we get this whole range ? Like I wanna do all of this. 2:48 All at one go instead of having to like copy paste this. I wanna be like hey. Here's the data . Analyze it. 2:54 Uh, do what you want with it like- How do we get that array in here? And again, I can show you right here just individually put an ampersand around it. 3:03 Sort of concatenate together. We can also use concatenate here. Uh, we can take exactly this. Right? Equals. A.I.

and write concatenate . 3:15 Uh, and instead of the ampersands we're going to replace this with, uh, commas. And this is exactly doing the exact same thing as those ampersands, but we're using concatenate inside of A.I. 3:28 Uh, our A.I. formula , and it's going to give us the exact same response. We're very similar, right? Um, depending on if you're changing your, uh, tokens here, temperature we can change this, you know, to .5, top P frequency penalty presence, we can do all of those things. 3:44 It's there. But how do we get an array? Let's say what, let's say instead of an A.I.

prompt, let's copy this entire thing. 3:52 Let's say we're going to write a prompt in here. We don't need, uh, A.I. prompt as the . thing inside. We're going to actually write variable prompt equals, I'm going to use something like this. 4:07 Uh. Right and email to each person in column one is the name and in column two is the project. Uh. 4:23 Asking about the status of the project. Again, you can probably write a prompt here, analyze this data , give me the insights of x, y, and z here. 4:32 There's the data , but where is the data , right?

And so instead of prompt here we're going to do prompt plus data . 4:39 Actually instead of data array, uh, data array, let's call it. And now we need to create a data array for this. 4:46 So what we're going to do here is. So we'll go array. Uh and here variable data array equals and what we could do if we wanted to straight up just. 4:58 Give it something. We could do spreadsheet app dot get active spreadsheet dot get cheap by name. Uh in this case it's sheet one dot get range and it's going to be the array in here. 5:12 Uh dot get values.

Okay what we're doing with get values it will give us an actual array. A of the values here so let's see if this works. 5:24 We're going to save this and instead of get all these errors actually because we want ai. We're going to call this ai array and we're going to. 5:36 Give it another thing I'm going to show you is this custom function at custom function because sometimes we're not going to remember how to spell this but now that we save that with that custom function actually let's make sure it saved yes. 5:48 Uh so and we'll take all of this instead of that we'll do a ai.

ai and there it is it's right there once we start typing it that custom function adds that ability it's really cool ai array actually equals ai array and here we'll do b 2 colon c 4 let's see 3 and see what happens what is the response that 6:10 we're going to get gonna get an error range not found line 34 tells us right here range not found ahh one second ah for this actually I think we just we don't need the actual array here as an array we need it in quotes probably because ah it's just gonna fill it in let's see if it this works now loading 6:31 loading loading there we go so we got the answer right if we had some data here we want to analyze me now have that array. 6:39 So again, we're using a right in quotes here that is key we saw that error and we're adding that array here we're adding a get values just to get that values in a right and then push it into that prompt over here prompt dot ahh sorry prompt plus data array. 6:56 So I hope that helps that gives you a few ways to do this in just a few minutes.

Uh we were able to show you with impresand, with concatenate , how to access some kind of cells and now we can put it directly into our function here. 7:13 So hopefully that gives you a few options on what to do.</div>