Create a Waterfall of AI Prompts and Responses

Advanced

About this Tutorial

This is an attempt to create a waterfall effect in sheets. Where an AI response becomes the prompt in another ai API call.
I show you one way not to do it, that fails with onEdit() but then I do show one particular way it could work. We wrestle with AI a bit in this video and the conclusion is not so sweet.

Get the sheet below with the Apps Script and API code already written for you.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Featured Formulas

IF() INDIRECT() ISBLANK()

Video Transcript

<div>00:00 Hello! Okay, so this is gonna be a weird video because I'm not sure exactly if this is incredibly useful or not. 00:06 I'm just discovering it right now. I'm trying to figure out how could I write AI prompts that sort of get better prompts. 00:15 I'm trying to write better prompts and I'm trying to see what AI thinks are good at gooder prompts. So, Oh my god. 00:22 And wondering what scale. I just took Andrew Wing's one hour class at deeplearning.ai. I think it was called. Writer Better Prompts Bigger Brain AI Emails Me Every Day

It was a one hour class on prompt writing and it sort of gave me a lot of like things I could do in explaining to.ai what to do. 00:42 Like literally what to do. Write in JSON. Write in HTML. Write it out in with bullet point blah blah. But I'm like ugh. 00:50 Sometimes I just don't know what to ask. I don't know sometimes I can sort of describe something in some way and it's really better described in another way. 01:00 I feel like right now prompt writing and getting a good response is really all about explaining something well. Writer Better Prompts AI Emails Me Every Day

Not objectively but rather subjectively. 01:13 Writing something, writing a prompt that ends up getting a subjective response that is good. And so I'm testing out Google Sheets functioning here. 01:25 I'm trying to create sort of a waterfall. Just like if I, let me create a new sheet. If I wanted to write in C2 the reference to A1 And I wanted to say I want to get the text here. 01:41 There's some, text here. And I know in C2 here, I'm gonna write A1. But sometimes I want the text here. 01:53 There. We'll make it different. And instead of C A1 in C2, I will write A2. Writer Better Prompts Bigger Brain AI Formula in Google Sheets

Now, this could be for a, quite a crazy amount of reasons. 02:07 How do I get the text that's actually over here based on whatever is in C2 here? I would use the indirect formula . 02:15 Indirect being a cell reference as a string . And I can write, actually, A1 here. You can sort of pick out and figure out a string of what you want. 02:27 A1, B1, 2, 1. Not 2, 1. D1, or D10, D12, based on any number of things. You can even construct your text here. 02:38 A and then you can put the ampersand and put the number. 2 there. Your First Formula in Google Sheets What is a spreadsheet formula?

And see now it's constructing A2 and saying go to A2 and get me whatever is there. 02:49 But also I can just do C2 here and instead of getting the text in C2, it's saying the cell of C2 has the string of the, actual cell I want which is A2. 03:04 Now this sounds crazy, right? It sounds like it sounds like, why would you ever use this? But this comes in handy a lot of times, this indirect . 03:13 Pointing to different cells because right here in E2, I want the text that's over here. How to Bring value from other cells | How to Reference an array in a cell Enter Name Get Grades

But I want to select it maybe out of a drop-down menu or there's some other way to change it. 03:28 And I can go A1 here and now the text in E2 is different. I did not have to go into into the cell of E2. 03:39 Maybe this is showing some information. Maybe it's showing options. I think there's a few weird reasons you might try this but there's also some very good reasons. 03:50 I like it. I think Indirect is one of the coolest I think it's one of the coolest formulas honestly. How to Bring value from other cells | How to Reference an array in a cell MESSIEST SPREADSHEET MISTAKE

Like it's hard to wrap your head around the first time and you're like, why would I ever need this? 04:00 But then the moment, the moment, and you need it, it's there for you and it's called indirect so it's like a weird name. 04:07 But that is the name indirect . You indirectly want that cell reference A1. Alright. How to Bring value from other cells | How to Reference an array in a cell

So that's sort of an analogous analogy, analogous sort of situation of what I want to do here. 04:21 I want to write, a prompt that then gets a response, that then gets a response, that becomes a prompt of another response. 04:31 So in chaining these kinds of prompts, I want to figure out how to do that in Google Sheets. And there's some limitations of Google Sheets that sort of come up right away when I'm thinking, thinking through this and trying to figure out what to do. 04:45 In other videos, in another other ways, I have created sort of waterfalls of prompts. Writer Better Prompts Bigger Brain CLUTTERED CODE

Now I'm calling this video Create a Waterfall of AI Prompts and Responses. 04:57 So essentially taking the outflow of a prompt, which is a response. So I get the response from a prompt and turn that response into a prompt of another and to get another response. 05:12 So how should we do this? And how do you typically think of this right away when you like start thinking through this? 05:19 I'm gonna show you one wrong way and then I'm gonna show you one right way that I don't know. Writer Better Prompts AI Emails Me Every Day

Oh, if it's really the complete answer, but it is an interesting answer and I thought show a little interesting part of Google sheets. 05:29 So first off, I got the AI, I copy and pasted AI script from upgrade Google sheets, open AI prompt to GPT four. 05:38 Only eight of you so far have gotten that saw seen that video. So I'm excited that a lot. More people are going to be using AI in Google sheets. 05:46 That's over here. That's this function here. I rename it up to AI. Google Sheets AI Formula OpenAI API Prompt and Response Bigger Brain AI Formula in Google Sheets

Just I think in the other video, it's open AI, but the function in here, I just renamed to AI. 05:57 And all we're doing this is literally just calling response. We're calling AI with a prompt. Here's the prompt and we're getting back. 06:03 The response that is it. We're using GPD 3.5 because it's a bit faster right now than GPD for. We put our API key over in a one cell sheet called API key. 06:15 So that's all the setup of what I have here. Saving OpenAI API Calls inside of Google Sheets GPT in SHEETS OpenAI API Prompt and Response AI Emails Me Every Day

Now here's some code that does not work, but I think it is the original way. 06:23 You might think of how to do this and I want to show you what the limitation might look like because there's a huge limitation here. 06:30 So you might say, okay, I'm gonna write my prompt in A1. And I want to run, I can run a script by some kind of custom menu or I can add a, button here to run a script. 06:45 And I want to take this prompt, get a response from it. The response I want to write in B1. Writer Better Prompts Generate Short Random Codes or IDs

And then from that response, I might just straight up use that as a prompt to get this B2. 07:01 Okay? Now this could be sort of anyway. You want maybe A1 to be A2 and then A2 to B2 either way. 07:08 But I like this sort of horizontal first and then vertical. It's sort of like a waterfall. We're going to take A1, we're going to get a response and put it in B1. 07:18 And from B1, we're going to get what we want to send it and get it the response in B2. Create a ChatGPT Cheat Sheet Spreadsheet

Right now I'm just having to. 07:24 I'm asking GPT to write a prompt that ultimately writes an introduction to an encyclopedic entry about Google Sheets the Prompt should include some other examples. 07:33 Like Excel and Lotus 123 should give historical context. What it ends up getting is not that different from what I wrote. 07:41 So I don't think this is a great example. But. It does get a response and I want to run that and this what I read. 07:48 The first way you might think about doing this is to say run this function . Run Every Prompt in Every Cell All At Once OpenAI API Prompt and Response Bigger Brain VIBE CODE GOOGLE SHEETS

Get a prompt, get the prompt that's in A1 which is 1 1 here the range . 08:00 Run it AI and run this the AI. Function as we've done before just run AI and with a prompt. Right? 08:09 Whatever that response is in A2. Nope, that's wrong. That's B2. That's row one column two. So that's B1. That is harder. 08:25 Figure out. Right. Alright. So write the response and we're using the set value function here in App Script . Run Every Prompt in Every Cell All At Once OpenAI API Prompt and Response AI Emails Me Every Day

And then I created a function called unedit and I said in an event, just get the whatever every time that there's an edit get the row and get the column. 08:41 Now there's something I forgot to say right now. But I will reveal that information soon enough. And if you're ahead of me, if you know the limitation of this on edit simple trigger , you know what's coming. 08:54 You can guess what is about to happen and what limitation we're going to run into and what roadblock we are going to smack our face into. 09:01 So we have a variable row. We just get that from event. range . How To Create a Stop Watch in Google Sheets Automation is not Magic

Dot get row with this column week of the event. 09:08 If there's an edit, we are going to get the column. So we'll know exactly where there is an edit where I said get the range of row one column two. 09:18 We're going to say if the edit is row one column two. Then we're going to grab that as a response. 09:25 Or say as a prompt. And we're going to run that as a prompt and get a response from that prompt. 09:32 And then in row two column two, so that is B2, we're going to set the value of the response. Writer Better Prompts Autofill Today's Date When Cell Edited

So, pretty simple, right? 09:42 Now if you don't know what's about to happen, this, might, you might think this will work, but it does not. 09:48 A hundred percent, this will not work. And why is that? It's because there are on edit is a simple trigger . 09:57 It is something we don't have to install. We can just literally write on edit here. And there is one huge limitation that you can, you sort of have to read between the lines. 10:08 But it says here, on edit runs when a user changes the value in a spreadsheet . Spreadsheet Automation 101 Lesson 2: onEdit() Trigger What Can You Automate in Google Sheets? Every single trigger available to Google Sheet users Automation is not Magic

So if something goes from, if there is a cell that goes from nothing to something, you're changing the value. 10:17 Your, the old value was literally a blank cell, and the new value is, you know, two, three, four. Then if you edit two, three, four, you're changing your, changing the value from two, three, four, two, ABC, right? 10:29 That is changing the value. And if a user does that, on edit will trigger . But you have to read between the lines. 10:36 On edit does not trigger when a non-user changes the value. So our on edit here does not happen. Automation is not Magic

He wrote this code. 10:47 Nothing happens. This code does not work as is this code actually sorry enough does work if we edit the. If we edit the so I'm gonna actually delete this shows one example. 11:03 If I edit this, if I just even take a period. This on edit should trigger . Let's go see if that actually is doing its failing. 11:14 So let's see what happens. You can probably figure this out right away. But it will trigger . So I can change is right and introduction for an encyclopedia entry about Google sheets. 11:26 I can. No More Google Sheet Tricks Advanced Coding in Google Sheets for Programmers How to Create a Custom Automation Menu in Google Sheets

Sort of take the quotes off of here, maybe hit enter and there will probably be another. There will probably be another failed thing here. 11:38 There it is. And so you can see what that failure is and it might be. Ah, so even on edit even. 11:47 If it does trigger we're gonna get a URL fetch app where we need permissions, we need to add it. This actually isn't that bad of a failure. 11:55 We actually sort of can get around this.

But for this video, we don't want to get around this because I'm just sharing with you that yes, we can sort of do this waterfall if we want to edit the. 12:07 Text, right? We get the response and we edit the text, but really, like, let's think about, I really want to create a waterfall where I put in one prompt, get a response, that response becomes a prompt and get a response from that. 12:18 And I just want to boom, boom, boom, boom. So one way I figured out to do this, so let's go back and edit this out. 12:24 I did figure out one specific way. Run 100 ChatGPTs at One Time CLUTTERED CODE

I'm going to get to do this, again, it might not be the best, but I just want to save this in here. 12:36 So that function is there if you want to test it out. And if you're a better student, go down below. 12:40 If you're watching this on bettersheets.co, just go down below and, and you can get this exact script and this exact sheet and. 12:47 Happy to your drive. You won't get my API key out, but I'll delete it far before it. Scrape Google Maps GPT in SHEETS Access Free API's with Google Sheets Scrape Google Maps in Google Sheets

This shows up. 12:53 Alright, here's this code that I wrote that it shows a little funny thing that you can do, which is I'm going to run one script. 13:00 So the other time I ran this AI to edit to on edit, not tune edit. And then I was trying to trigger another script. 13:14 In this case, we're doing it all in one script, but we're doing two things. Once we are still getting the prompt, we're getting the prompt from a one, which is one one. 13:25 We are running it through our API API. I call just getting the response. Run 100 ChatGPTs at One Time OpenAI API Prompt and Response AI Emails Me Every Day

We are setting it exactly the same in B one this time. 13:35 But at the once that happens, once we get that and we set it, set that value, we are now going to set a formula . 13:43 The formula is going to be if is blank B one. And if it's. False meaning there is something there. We're going to do AI. 13:52 Be on so we're going to write a function in a formula . And if for any reason whatsoever, we can actually run it's funny enough this formula . 14:01 Not a non form function can actually be run. We can set this formula before we get a response. Run 100 ChatGPTs at One Time AI Formula in Google Sheets

But what I want to do is I want to actually send that AI call soon because it's going to, it does have a timeout of like 30 seconds, right? 14:17 We want to get that in and out. But now we're going to set this if is blank. This is just in case there is no response whatsoever. 14:24 It's not going to run again of a blank response. So if there's actual text, it will. Run and sort it does work. 14:31 So I'm going to delete these two things. And now we have nothing in B1, nothing in B2. We have just something in A1. 14:38 And the idea is we're going to run this script. Run Every Prompt in Every Cell All At Once AI Emails Me Every Day

Take the prompt we have, get a response, take that response and say if there is something there in the. 14:48 That text running it run that response as a prompt. So I'm going to go to run AI. I'm going to run it from here. 14:56 We could also run it from inside of the sheet if we added like an on open.. Some kind of on open thing. 15:02 Now this will run eventually run two functions right it's going to run this a one. Should be there there we have a. 15:11 Oh, that is not a pumped. Run Every Prompt in Every Cell All At Once OpenAI API Prompt and Response AI Emails Me Every Day

That's a pretty weak prompt, but now we have this loading great so right this is not even written like a prompt. 15:26 So we'll see what actually it. Does. I think it actually ended up writing. Q and yeah, it wrote it. So let's change this right a prompt for chat. 15:38 She P T. Ultimately. The response from that prompt will be. Introduction. Only write the prompt. Write. The prompt. I think that's gonna be okay. 16:01 Oh, we got an error . I think we exceeded time. So let's try to do this again. We might get errors right that again. 16:09 We have a. Writer Better Prompts

30 second time limit here, but let's see. Change this a little bit. Change the only write the prompt. 16:16 Write the prompt. I'm telling it write prompt. Uphrumped. And it's doing this. I'm like, I have learned nothing. Okay, this did work just before I started this video. 16:25 Okay, I wrote a little bit more. Let's do this text a little. A little bit smaller so you can see this whole prompt. 16:32 Write a prompt. And I'm sort of trying to explain it more, not concisely, but explicitly what to do. It's going to do a few things. 16:40 So it might time out again. Writer Better Prompts AI Emails Me Every Day

Let's see what happens. We're going to run AI again. Again, this is just going to send this a one as a as a prompt to AI. 16:49 To chat GPT to open AI. Actually, we're using GPT 3.5. Get a response in B. There we go. And we still, Oh, he's still, But welcome to today. 17:05 We're going to discuss, I don't know why it's doing that. It did it perfectly. Earlier, but now, okay, you can see that this is going to do something else. 17:14 This is taking exactly what's in B1 and running it here. It's just this if is blank B1 AI, B1. Run 100 ChatGPTs at One Time OpenAI API Prompt and Response AI Emails Me Every Day

So it's just taking B1 running through AI. 17:25 So it ended up actually coming out with a really good, Thing here, and I don't know why. But this is an interesting thing machine learning and also AI, right? 17:36 Sometimes it does do what we wanted to do, even though we haven't asked it. We have explicitly asked in the A1. 17:44 So I want to introduce one more theory in the end of this video here where , Because we're running a waterfall of inputs, responses, prompts and responses, prompt and the response is a prompt. 17:57 You could probably add more. Writer Better Prompts BEFORE YOUR SHEET GOES TO AI

And that's sort of what I've been trying to figure out in some other videos here in Adventures in AI. 18:04 So watch other videos here Adventures in AI where I try different formats of chain, I'm not needing these prompts. So this one took exactly the output and tried to run it and it did get well. 18:18 But we can add more, right? We can say to this prompt we can add another prompt and say maybe summarize it. 18:25 We can also maybe add and this is a maybe. Writer Better Prompts CLUTTERED CODE

We can maybe add, more formulas and write sort of more formulas and with different inputs. 18:34 So what I did notice is that if you just put in this formula , it's gonna write, it's gonna see loading. 18:41 And so that's gonna be some text. Let's see here. If I do this, right? This fourth line, is B3. Replace 1,000 Formulas Instantly Your First Formula in Google Sheets What is a spreadsheet formula?

Because it says loading, we're already taking the words loading and the text there and putting it in here. 18:58 So we need something, and this is sort of the conclusion of this video, is we need something to block this. 19:03 The other videos I've done, I've put check boxes here to limit this and say, okay, just execute this one when, the check box happens, but I sort of want to get a little more chaining going on without human interaction. 19:17 But we might not be able to in Google Sheets. Automate Google Sheets without Apps Script, without Zapier Click Checkbox to Copy to Tab in Google Sheets

There might be this crazy limitation, which is not that crazy because we don't want Google Sheets to go, Kuku and go on and on and on and on. 19:28 Right? We don't want it to keep writing more and more and more. Maybe. We want other programs to do that. 19:33 But one, I thought this would be a fun way to show you this set formula and to see this sort of waterfall effect. 19:43 And if you're watching this video and you're like, I really need a solution, let me know what your problem is. 19:48 Let me know what you're trying to do. Maybe you're doing it horizontally. When did you learn the secrets of Google Sheets? | Sheet Talking Episode 8 Esa How to Add a Series to a Google Sheet

Vertically, maybe there's some other solutions depending on how you are structuring your maybe it's dependent upon your situation and how you're structuring your sheet. 20:04 But yeah, I thought this would be fun. And again, you can grab this minus the API key down below. Just copy it directly to your drive. 20:11 And you can use this AI. You can even test this code and edit a little bit if you want. Bye.</div> GPT in SHEETS Access Free API's with Google Sheets AI Formula in Google Sheets