Hello, welcome to this video on What Changed. We're gonna do a custom function where we learn how to do a double four loop. This double four loop is somewhat complicated, but we're gonna try to rebuild, get through it piece by piece. And we have an end goal in mind. I had just recently made a video about a change log where anytime we have an on edit, an edit in the sheet, it can actually log on one of the tabs.

What was the change, what was the edit, what was the value before? What was the value after? And then you get to see sort of every change that happens within a sheet. But in this case, sometimes we're writing maybe in a team. Or we have a copywriter working for us, or we are a writer ourselves like, and we just wanna see what changes did we make between two sentences perhaps, maybe between two paragraphs.

We just wanna see like are there editing or is there a copy editor that's just editing a couple words and we just want to double. What they're doing well, this is what we get. I'm making the custom function where if we have a sentence here, like, every boy has a dream and we're comparing it to two things.

One, B one, the, the line above it, and B two, the line that it is. Well, here, there's no changes. I mean, there's no ch not that there's no change, there's nothing in B one, so we really don't have anything to, to say, Hey, there's something. So we wanted to say nothing to compare to. Then we want this function also same exact function, B2 to b3 tell us, well boy has been deleted and girl has been added here.

And that is exactly what it does here. Here it says every person has a gen. We, we edited girl to person. And so we wanna say, you know, we added person and we edit and we deleted girl. And here we changed every person to everybody. And so see we have it say automatically. Oh, you've added everybody and we've deleted every person.

So this is what it looks like at the end. But I'm gonna rewrite this whole thing with you here in this video so that you can see how we do it from scratch. So I'm just gonna go to file new script file and we're gonna go, what changed?  and we're gonna create a function here. So I'm gonna actually delete this.

I'm gonna say first, and now when we go back to our sheet, it will say name. So we don't have a function called what changed anymore. So we need to create that. So we just right here, what changed right here. Save. And now when we go back, , you should say. Actually absolutely nothing. Once it loads Oh, it's, it's not gonna do anything cuz we're not returning anything.

Okay. So first thing first is we want to get we wanna figure out like what are all the parts of the cells that we're comparing to. , actually, scratch that. Before we get, before we get anything, we need two cells, so we're gonna name it first and second here. Now these two inputs, we need to know what they are.

In our function here, we're gonna be able to have two function, two inputs here, B one and B two. And we can also put in full cent, we can put in something like this.  and see the changes, but really we wanna abstract away and we just want to be able to say B one and comma B two. And so that B one and B two, we need some, some inputs here.

And that's what we do here. And we can name this anything we want, but right now we're just gonna do. First and second. Now when we get these, they're gonna be like a whole sentence or a whole string of letters and words. We really just wanna pick out all those words. And how we do that is we do variable first equals first dot split.

And what are we gonna split by? Just a space. And we're gonna do that to both of them. Okay? And in this case, now we will have an array of, there we go. We will have an array of words so that we can parse through them. Actually, I'm gonna view logs here. It's gonna have no function, so we just go to our app script and here if we want to see what would happen, like what, what is happening when we split this, what we can do is we can do logger dot log first.

And actually I want to call this first array and I want to call this second array. So we don't write over that array. And then we also.  want to do same thing, but second array and Logger helps us figure out what is actually going on. We already have these functions running here, so what's gonna be really helpful is that these are always running in our logs.

We're gonna have those executing. So we'll see what we have here. This is completed. Move my face over. Sometimes our logs don't show up for a while.

You know what? I'm gonna just, so it actually has something to return when I say return, working on it. And so if we go back to our function, custom function, it'll say that maybe we need to cut it and paste it in again.

There we go. Working on. So now if we go to our logs our last log here, and we see that the first array has nothing, which is correct cuz that's the first, it's a blank thing. Now every boy has a dream, is all separated by their words. Cool. So we're well on our way. Now what we need to do is we want to, we want to loop through these, okay?

So this is, we're gonna write four. Four. And all, every four loop has these three parts. It has what does I start at? What does it go to? I is less thin. We're gonna say first array dot length. That's just, we want to go up until the, the how long the array is. And then we wanna do i plus plus, which means every time we run the.

Add another I. So it starts at zero and it goes, adds one every time. And we have a four loop here. Now we can do anything we want here. We can return logger dot log first array, and then in these brackets we can put I there we go. Now I'm not gonna, I'm gonna not gonna show you what happens in the log there cuz we won't have anything.

What we wanna do now is another four loop and we wanna do j equals zero. And we want J is less than second array dot length. Same, same thing. But we're inside of the for loop of the i of the first array. And now we go i plus plus. Same.  and we put two brackets here so that we are inside of that. So now we can access both the first array, on the second array and deal with it as we wish right now.

This is going to also be logger. Let's do this second array j Now we're gonna use J here, and if we save that, we can go to the log and see what happens. , it's gonna probably be interesting running, and now it will refresh. It will refresh anytime. Now this may take . There we go. So now see it, it loops through everything, every a bunch of times and it's looping through every word.

Cool.

Oh, I just realized I did something wrong here. This is supposed to be J here. That was wrong. So that every was  funny. It was not doing it correctly. So let's see if we got, we looped through everything now. There we go. We now have every single word in both arrays. Cool. Okay, so we don't need this to log now.

We just. To return something they do here, what we wanna do is let's, let's start getting a return here and let's just call it all text. And our variable of re of our all text will be this variable. All text will equal. We want to have sort of an array of two things. We wanna know what was added and what was deleted.

So let's create up here for.  variable added equals, and we're just gonna create an empty array here and we're gonna fill this and then delete it. Okay. Save that. We get this. Yeah, we, we know. So now here we are going to return added and then plus.  added. So we want all, everything that was added. But we wanna also get a this is gonna be interesting.

This is a, this, this slash n is a new line. And we want deleted colon, and we want then give us the list of deleted. There we go. So now this alt text will return these two arrays in this format. So let's look at.  think Perfect. Right now, this is exactly what we should be getting is just empty arrays because we haven't added the arrays too.

We haven't, we haven't added the words that have been added or deleted yet. Perfect. Okay. Now in this for loop, we are going to have a few op a few things that happen, right? One, if a word appears in the. Like if we have this right, we want every, every appears in both of these. So we don't want anything to happen.

Boy, appear. It's, it's, it's the second word in this one, and it does not appear in this one. Okay. So we're, when that happens, we want to say it's been deleted. Girl here does not appear in this one. So in that case, we wanna say it's been. , right? And in this one, every single word is a is new. But this is, we'll get to that later, but right now the, the difference of this is just boy has been deleted and girl has been added, this person has been added, and girl has been deleted.

So that's what we ha should have here. We are going to use, in this case, if, and we're gonna say if second, no, we'll do this right first array. DOT includes, and we're gonna say second array, and then we're going to use that bracket. And we're gonna say, J here. So as it loops through J it's gonna say, well, if this word is included in this array, then yes, do this.

And then we want.

Yep. We need another bracket there. Okay. Now we have, if, if, if the second, if the second array word is in the first array, then we're gonna have this. But we really want to have one more thing here and we want to say if in the knot second array that includes,  first array, and here we have, I

Now if, if the

Now here we're gonna say that this is deleted, so deleted. Push first array.

and then we wanted to end. Right? So let's see what we messed up. Alright, I think I have a weird thing here. So this should, I think I forgot one of the curly brackets here.

There we go. I think that's where it goes. Nope. One sec. Nope. It was not a curly bracket. It was the parentheses. So I missed both of these parentheses here. There we go. That should work. Now let's see if this works. So now in this line, boy has been deleted, girl has been deleted. Perfect. Deleted every and person.

So these words don't appear in the second, the second array, but they do appear in the first. Perfect. This is perfect. So that's deleted there. Now we need to add, add our, our add in, right? So we go else, where are we? We wanted to say else. So if our second array word does not appear in the first array, that means it's added, right?

So we go added push, and we're gonna push the second array. , get that bracket, get the J and close that there. Let's save that. So now this, now all we're doing is using Includes, we're saying, does this word, is it included in this array? And then if it's not, push it there. That's basically what we're doing here.

And now we have this crazy thing, right? So one. . Okay, I think I put this else in the wrong spot. I'm just gonna cut it from here and put it right there. That's where chi go, right there. So if the first, if the second array word is not included in the first array, that's where we need to go. So let's. Save that and go back and look at our example.

Great. Okay, so now we have a few things going wrong, but these are all good things. So this one says go, go, go, go. Four times this one's person, person, person. Four times. What's going on is this is, this is looping through every single time and it's looping through and it's saying, let's fi, let's push this four times, because it's probably the same number as the number.

Words there, minus one. Okay, so what we need to do around this added is we need to put an if now if, and in there we'll put it around there and we're gonna say, we're gonna only add it if it's not there. Okay. So in this, if we're gonna say,  if it's, if added includes second. Alright,

so we first, we could have said this, if, if this is included here do nothing. But we're gonna, we want the added, so we say if it's not included. So if this second array.  does not, is not already included in this added, and this, this exclamation point at the beginning means not. It means basically find, figure out if this is true or false, and then switch it.

And if it's not in there, then add it and we're gonna hit save and we go back to our thing and we only have one example of it. Perfect. So what might happen? This does offer the edge case. Like if there are two words that are exactly the same that have been added, it will only have. . So that might be something to fix later.

Another edge case that might happen is that what if our, what if we have this, this is just a brand new line, right? We have nothing to compare it to. And it's just gonna give us the entire. Sentence or paragraph. It's gonna, every time we've, we do this, it's just gonna give us everything. But we know, we, we see we can, we're we're, we're very, it's very easy for, for us to see that there's nothing and there is something.

So what we really need to do. Oh, and then also we have another edge case that what hap what happens if they are the same? Right. This is a funny edge case because, , it's fine that they're, these are blank. We can easily see that this is a blank. There's nothing added, nothing deleted. And so we have nothing.

But we want it to be very apparent. Even more. We want to know that like, oh, these two things are the same. We can do that very easily. We can just wrap this entire thing. Let's take out this logger first off cuz we don't need that anymore. And we say if.  first array two equal signs is equal to second array,

and we're gonna do turn. Nothing changed. Okay. End there. Now it's gonna go through that before it goes through. Okay. And it's gonna say nothing change here. If they are the same, it will go through will.

Oh, we wanna actually just do right. Bef what? We don't even need the array. We just need to know first and second.

There we go. Right away it says nothing changed and it returns that done. Okay, but what if there is nothing before? Nothing. Or what if it goes to nothing? Right? So

we have say this, right? That's also blank, but we can see that it's blank. . So this one is a little funnier. It's if first is equal to nothing or, and we use two pipes here, I don't know what they're called. Big up and down things. All pipes. I guess second is equal to nothing. Now that's an or. So if either or are.

we're gonna return nothing to compare to there. And so now we have these two edge cases taken care of right away before we get to the four loops. So it gets us a result. Very simple. So very quickly, very simply. And it's just gonna tell us nothing to compare to. And here this is nothing changed if they are equal.

So if we have. This, it'll say nothing changed. There we go. So now we have a really cool little function that allows us to see what is the actual change, what's been added, what's been deleted from these two things. We can check copywriters. We can do our own copywriting and copy editing and keep adding a line and just say like, here's, you know, 10, 10 levels of changes for copywriter.

This is a really cool. Way to automatically check our, our data. Right. And I think you can have some pretty cool things with it also. What I was playing with before I made this video was something like, if words are moved, you can ch you can once you can actually loop through them and say, oh, those, that word does appear in this other thing, but it's in a separate.

Then you can say here that here's where it moved to with this I and this J We can say, oh, it's the first thing and then move to the third thing. But what happened is that once one thing got changed and everything, or if you, if you added one word, then everything in the rest of the sentence or paragraph got cha moved one.

So the move results were incredibly large. But I think it's a really fun way to get to figure. These two loops. So sometimes if you're just learning Google Script, you'll have this four loop pretty much memorized. But the double loop allows us to go through two different variables very quickly.

And it gets us our results of this sort of what change. It's a cute little change log we can add to our sheets. And if you are watching this and you're a better Sheets member, then as always, the script and the sheet link are in the description below. If you are watching this on YouTube, then consider purchasing a membership to Better Sheets, unsubscribe to the YouTube channel.

If you are watching this, you're a Better Sheets member and you're. And you're not yet a member, a subscriber to the YouTube channel. Come check it out. youtube.com/better sheets. That's it, that's all. You have to go slash better sheets youtube.com/better sheets. You'll find us on YouTube and we're posting some of the videos, but my God, like 80% to 90% of all the videos are still only available to members, and only members are able to get to every single template, every single sheet, every single script I'm writing, you get access to it as a better sheets.

Bye. Thanks so much and remember, click subscribe if you're not subscribed yet.