Hey there stranger!

Sign up to get access.

Tic Tac Toe In A Google Sheet

About this Tutorial

Figure out who won a game of tic tac toe. inside a Google Sheet. Great for teachers and educators.

Video Transcript

 Hello, welcome to the middle of a build. So what we're doing today is I actually wanted to build Connect four. I saw someone had built a fairly in non interactive connect four. You had to figure out if you won, you had to, it was just marking X and o's into a spreadsheet. Like a, something like this, sort of just taking a square squares and saying let's resize 'em to 30 and then resize these, something like this resize 30. Actually we should probably do, I think it was like seven across 30. And then doing something like this, like having a nice. Something like that and then just saying, X and o and then, and you could do a Connect four in there, right?

But you have to real figure out who won, right? You can't programmatically say who won or who lost. And as I was looking at this I was trying to figure out how to build it. It's very difficult to figure out how to build something like this, just to say is have you won or lost? And you. If you were programming this, if you were a programmer, maybe it might be actually fairly simple to just get a put all of these into a con, a joint list and say, does do four xs exist altogether?

And I might actually try to build that in some way But like the diagonal is the hardest, right? X or O oh O, right? And I wanted to try to figure out a simpler version of that. And so I started toying with the idea of tic-tac toe. And now I've g gone to a point where I thought it was pretty interesting what.

Discovered and then I thought it would be also interesting to share with you how I how I problem solve through this next part cuz it's actually probably the most difficult part next I think. I don't know. And also I don't believe that the way that I am the way that I am trying to figure this out or solving this, sorry, is the only way It's a bit of a conundrum here.

And so I wanted to share this with you. So here's where I've gotten so far. I'll try to explain where I am now and then we will get to the next step. So I created just dropdown menus, Xs and Os, so you can say, player one and player two and select, you know which one you're doing. You're, this is the X and this is the.

You can, you can stylize this pretty well. I wasn't really stylized stylizing it yet. I didn't want to deal with that just yet. But yeah, you can make this actually a square. I think it's almost a square. Yeah, that's as close to a square as we're gonna get, I just wanna make one more change.

There we go. Okay. So we have Xs and Nos, right? And we can have E there are three states of each. It's nothing we have to do delete O or X, right? And the wind state here with TikTok toe, it's fairly simple. You either get a row across, so that's three winds here, a row down. Three wins there, and then across either way.

And that's two wins. And I realized I was actually gonna start marking these up as what is this one. And I realized in a Google sheet we already have this, right? We have a one B one, C one. And so we know there are a finite number of winners. There are eight possible ways to win tic-tac toe, right?

And so I started listing. So I said, okay, if there's a finite number, and I thought this might be an interesting way to do Connect four as well. But let me get to that later because I think I have another solution, but I'll try to remember it later. But here's where I come up with. So A one, B one, C one, if all of those are Xs or all of those are os, then you win.

So what I did here is I have an indirect and I say, just give me whatever that is. And then I'm have three of them together. And now all I need to do is if it's if they're all X's win. If they're all O's win, right? X one. What I'm using here is an if formula and then inside it. And so I'm saying, and meaning all of these have to be true in order to be true right now.

If we go in here, it's false because not all of these are X's, but if I go here and I go X, oh, I need to do capital X, right? Capital X, capital X. Now suddenly, all three of these see indirect means just give me, basically it's saying, give me the notation that's in c6, and this is says A one.

So really just give me a one. I did this, I think because I was just listing these and I was like, oh, I need those. Okay. You could, I could have absolutely done this. This is absolutely like a sort of saving step I could have just done equals. Each of these and then did this if and around here.

Totally possible. I just chose to, I wanted to visually see all these representations. What's a one, A two? So we need to do the columns a no. A one, then a two, A three. And this is gonna be B one. See, this is super boring, right? But we're only gonna do this once. This is not a thing we have to do EV each and every time.

Wait, no, we want c1. C2 C three. And then the last thing is the the A one B two c3, A one B two c3. And then it's gonna be the opposite way. C one. B2 and a three. A three. So now I have all these indirects here. I'm just gonna copy paste these down because they're all just referencing here. So now we have all of the possible wins.

We have eight different WIN states here, and we say, okay, that's a winner. And honestly, we, all we need to do is put like a What is it? We want to filter for everything in here that is not false. I don't know if this is gonna work. I always try doing not, and it doesn't work. And it doesn't work.

I think. Oh, I think I need to make this I six to I 13 equals there. It's, that is, that's what I had to do. Not as a weird formula. I always, or maybe it's not weird to you, you figure it out. So now we're gonna just filter for everything that's not false. And then we're gonna get, for most of the time, when there's not a winner, it's gonna be an.

So you wanna say, okay, equals if error do nothing or if error rural within our, there we go. So this is going to be our Wednesday, so let's do this and actually I'm gonna put that there. That's who won. This goes there. And now as we play player one and two, player X, player zero, let's do an O.

And now we're seeing them filling out here, right? And they're just, we need to get three in a row. We need also capitalize lies. Capitalize. Oh wait, we need an O there. And then who's gonna win? It's gonna. So X one, C one, C two, C three. Why didn't it work? Oh, because I didn't take this formula all the way to, there we go.

One. Oh, we have to, okay, I see what happened. So X one, we say X one, but what if Zero one or no wins, right? What if they're, these are all false and nobody won. It's a tie, right? So right now we just say filter, but that's not gonna help us. That's not gonna get us here. Let's put it, oh, see, nothing.

Okay, we need to get two more wins. Two more states. Okay? So if X wins, then one of these will say X. So that's cool. They all are false, so they're all going to be false. But if all of these are filled in and it's still false, then it's a tie, right? And all of them need to be filled in. So see all of that is filled in.

So basically this states, this is 24, so let's do this. Let's get rid of this and do, if count all. F six to H 13. If that equals 24, then we're gonna put tie, it's it's,

it's a tie, right? So that's what we. Now, if so, if it's not 24, then we're gonna add a little comma here and say, okay, if it's false, what should we do? We should go, we wanna know where is the one. Okay? So they're all gonna be false unless they have a win. So if.

Then we can do, if you can do a search, you can do search for, what are we searching for? I always have to get this search, search for X one and text to search I six. I don't think we need, can do join join with nothing. Value I six to I 13.

And we're searching for X one, right? So if we don't find, it's gonna be a error, right? If we do find it if it's true. So we're searching for X one in all of this. Join. Let me just hit enter, see what we get. If I'm gonna get a value. If in search valuation cannot find X one within that. So what if we did have X skew the correct state?

So really what we're doing is we have a bunch of states, and let me write 'em out here. One we're in the middle of a game no result. We have two, we have three Xs and it's win. So we'll do state and result none X 1 0 1 if we have three Os. And then no. All filled. Hi. All filled without.

Out X three, XS three, XM three. Oh, that is the only possible things that could happen. We could be in the middle of a game where there is no we don't have 24 of these, right? 24. And then we could have three X's and one one of those will say X one. See, this one actually says 2 1 3 O's, which will say, oh one.

So let's do. That is generally speaking, if you're playing a game, oh, here we have three O's and we have false here, so we haven't even added the oh one yet. So let's see. So if this is false we want if we could do this either way, if I was as false or if so let me just copy this.

Because this is gonna be the same if and then we're gonna say, oh one, okay, we want to change this to oh. And if both of those are not nothing, it'll say false. Okay. We could. We could do something like this. So if both of these are false, it'll come down to here and it'll say, keep going.

Maybe we say keep going instead of false. And then we can also do it false here.

So also we need so that's false. And false. We'll keep going. So what if we did that and,

Okay, so we have a state of a win here. So if Search Exon join if you can't find that we are going to do an if error here, but instead of doing nothing, we're gonna search again for this, so we're gonna search here. I think that's it. Search for, oh.

And then that might have an error as well. We're gonna do that later.

All right. I think we need to get search

starting it. Ah, so it's gonna have a number. That's what's gonna happen. So this will. Result in a number or an error. So if error, now search this and if this is an error, then neither X one nor oh one. But we know it's not a tie. Cause we know if it's all 24 it'll be a tie. So we can say if error here, we can add here.

It's really hard sometimes to. Track of where you are in a long formula when you have a lot of formulas. So using this, there's a little gray shading when you're on parenthesis. Definitely make sure to use that and understand where you are in the spot. So we want it to say on.

So what is going on? We have, we're in the middle of a game. Oh no. We have the O is one, so we need to find that oh one. So this should have searched and found a number. Oh, we don't want this. We want nothing. So now if error nothing and around this. The result of this is over zero, right? If there's a number, if it actually found X one or oh one in that join.

Then we want to, what do we want to do? We want to go


I guess we want it to say one or not, then we want filter for everything, not keep. Filter here.

One six, filter i six Fornot.

This equals two. So now should we, we have a false, Ooh, we are so close to this. Okay, let's figure out where we went wrong. Okay, so if count all is equal to 24, it's a time. Cool. So if we complete this all, let's just double check that this is gonna do what we want it to do. It's a tie, right? So we have that down, but by the time we get.

We might have a winner. So we have a couple winners here. We have false alright, let's try to figure this out. Where did we go wrong? Let me make this a little bit bigger so you can see it. Oh, okay. So we got this correct. If count only equals 24 at a time, right next. If it's greater than, if this is all this, where is this if error, so is that if error working?

Oh, is there a false maybe here? No, it all says keep going. Why does it say false?

Must be some kind of if function

no matches. What are we matching? Okay, let's just double check. We got parenthesis here. Our parenthesis there. If, oh, I think this is in the wrong spot. I think we need to add a parenthesis there. Delete that one. If, and then add another parent. Oh, I think we missed two parenthesis there. There we go.

Sometimes it adds parenthesis at the end if I don't get 'em, and then it confuses the heck outta me cause I actually missed a parenthesis in the middle. So what I did, so this is really good. So I mentioned this just a couple minutes ago, use these gray highlights to understand where you might have gone wrong.

Hopefully this is work. So this if function is here. So actually I don't want it to end here cuz I want this to be, if no really that's, this should be here. I think this is wrong. Yeah, there's an error. Definitely an error. I'm missing something. What am I missing? This one is if error. That's correct. If,

Ooh, we have two ifs and only one. Oh, did I mess this up earlier? If count all it's a tie, and if so, this one goes there. That is definitely it. There we go. Now we're cooking. So we did put extra. You're still false. Why is it false? What is going on? Because you're in the middle of the game, so eh, oh no. We need to delete this. So we're ha this if Lemme just delete that and see where we go from there.

I think this filter, we can rewrite this later. Let's go back into the core. So we said search X one, join, we're looking for X one. If we find it, we'll find a number, right? If we have an error let me get rid of these if errors and just do these searches first. Get this correct. Oh, here. Search. I think I found it.

Was this here? Okay, so we have a search here and it says, search here. Join search X one for this join. And if we find it, it's gonna have a number, right? If this number is above.

Then it's going to be true. So then we go X one if this number is error. So we go if error, then we wanna search again and search 4 0 1 in here. And if this is a.

I think I'm doing this wrong. Oh no. So we add another, if search is above zero, then oh one. If not we're gonna do, keep going. Keep on going. So that means these are all error. I think I have to add. If error, or maybe this one works. If it doesn't work here, we'll know why in search.

Yeah. Oh one. So we do need to do another if error right here

and if error com do nothing. Nope. Keep it going. So what I'm doing is I want to know that we got the actual. Result. We're gonna come in and change this back, but I want to know which one it actually is going to. Keep it going. So now we are in the middle of a game. I think we figured that this one out, we are in the middle of a game.

So all of these say keep going. And both of our searches, we're looking for a winner. We're constantly looking for a winner. We're looking for oh one, we're looking for X one. It's gonna show up over here if we actually have our winner, right? Oh one. Wow. So it works. There if we have X one, cool. And we want to know.

Okay, so none of those are actually, keep it going is the second one. Cause I think we added something we didn't need to add here. Keep on going. I think this will never happen because. If the search is, if you have not found X one and you have not found a one, you'll you'll have an error. So this one is probably unneeded.

Keep on going. We'll probably never see that. I think. Again, let's just check the tie. This, oh. Nope. Oh, it's a tie. But if there is a winner. Okay, so we have, if you win on the final one, we have a problem. And you might, if you're watching this video, you might have figured that out already. Okay. So we need to add something where instead of just always view it to the end.

It's a tie. It's not a tie if the. The final, the ninth key is a winner. Okay. So how do we figure that out? So we wanna override this. It's a tie. So really the thing we should do is, oh, I know we should do the searches again. Cause yeah, it will only be a tie if you fill this out. If you fill it 20.

But there is the state in which it is filled out and O has one, or X has one, and you wanna state that. So instead of it's a tie is we wanna do this entire search again except instead of keep it going, it's a tie. Okay. So I think we can actually do this. Let's just This. See, now this says oh one. See, we can, oh, Google Sheets even helps us here.

It knows that right here, this says oh one, but because this overrides it, if common oil equals 24, it's a tie that overrides it. So really we want if error. Wait, have we have it already? We have it. So this, and then take out, it's a tie. And put it right here. So now it says oh one, if this is X, it's a tie.

So the only time it will be a tie is if everything is filled out here, all 24, and it doesn't find a winner. Cool. Whew. That was actually way more complicated than I thought. Oh my God. We can definitely make this more beautiful and more interesting. We can put, we definitely wanna put this all in another on another page, but because this was so riveting and right up until the very end, I didn't, I don't think we got it.

Yeah, so we see I'll let's like double check that it, we got it correct. In this video I've showed you use of, in. Obviously we don't absolutely need this, but I think it absolutely makes us a little more elegant where we can tell what the winners are. And in fact, this sort of leads into Connect four, right?

Like this may be a solution for Connect four because you can state, oh God, there were just, there's just going to be like a hundred exponential. Larger number of potential winners in a Connect Four situation, right? But this might be a way to get to a solution for Connect four. But we may tic tac toe in a Google sheet.

I will give you access to the sheet as is. I'm not gonna make it any different. I, we'll just let's undo those the grid lines. Let's do this, let's fill this in. This will be the thumbnail. Probably we'll have a X You did so well. How many

X one? So maybe add, maybe make this a little bit bigger. Okay. I can stop fidgeting with this. I'm gonna take a screenshot of it. Thank you for watching Tac Toe in a Google. If you're a better Sheets member, you have access to the sheet. If you are not, you have access to the video. I guess if this ends up being on YouTube become a better Sheets member if you're not already.

AppSumo is doing a deal now on it it whatever it is, whatever. Whenever you are watching it, the in the description will probably be a link to better. In some way somehow, but hopefully this was fun to figure out how to make tic-tac toe in a Google sheet. And this was actually, I was supposed to do, to Connect four, but still haven't figured that out.

And I don't know if this is the most elegant solution for Connect Four either, but at least we have this. And if you want to take a shot if I don't have it yet and you don't see Connect four in a Google sheet, then you should try creating Tic-Tac. Connect four. You should try to create name, connect four in a Google sheet and comment your solution, share your sheet.

And enjoy. Thanks so much. Bye.