Hey there stranger!

Sign up to get access.

Wordle in a Google Sheet | Work in Progress

About this Tutorial

Building a clone of Wordle inside Google Sheets.

Video Transcript

00:00 Hey, better sheets members. Uh, this video is only going to go to, um, better sheets members because it is, uh, revealing everything in this sheet.
00:11 Uh, it is also declaring that I am not completed with the sheet, so this is a work in progress. Um, but I hope you are clicking on this cause you love Wordle too.
00:21 If you've heard of Wordle. Um, I play Wordle every day. I like it. I love it. I, I wanted to see, um, if it's possible to make it in a Google sheet, I'm like so close.
00:33 So I will show you that you can play Wordle in a Google sheet, but I will also show you what is the problem and ask you if you are, um, interested to try to solve this sort of last little bug that's in the game, um, and to figure out, um, a solution to it, if you can.
00:54 So I will, um, this is sort of a work in progress. This is a plea that I am not completed with this, but it's also like go ahead and play and, and just know that there is a bug and I will explain the bug it, after I explain the game, I will explain the bug.
01:09 And then, um, also dive deeper in this video, into the script, which I think is super useful if you're making any type of game or some type of sheet that can need to be reset after each game.
01:22 So the script here is very, very simple. It will just clear the game and reset it. Um, it's not part of the game.
01:30 I'll show you also how the game works in a Google sheet, but first let's just talk about Wordle. Um, what you do is you have six, uh, guesses and a five letter word.
01:41 Uh, if you get the correct letter in the correct space, it'll be green. If you get the right letter, but in the wrong space, it'll be yellow.
01:50 So here I've chosen one of the classic or our modern word, old, um, best words to start is a do.
01:58 So we do the again, um, maybe we do a corn and then we see that, um, we have an a and an O um, I don't know if it's something like that.
02:17 Probably not. Um, I don't think this is a word, not enemy. Oh. And if you notice, it's not checking the actual, like, if you, you can type in a wrong word, like word that doesn't exist.
02:37 So that's one issue, but excellent. Maybe there is a solution to that. Um, well I know what the word is.
02:47 Cause I started this. It is, uh, there. So we got you see here we have X's when we don't get it right.
02:54 And we do get it right. We have a thumbs up. If for some reason we got to the end, maybe, Hey, we didn't get it right.
03:05 We can click the reveal button and it'll show us what is the actual word. Um, so what, how does this work?
03:13 Um, first off here's a couple of things. If you are looking to, um, fix this notice, uh, one thing is that it doesn't check.
03:23 So I'll probably be adding that soon. It'll probably check if it's an actual word or not. Um, I do have all the words that exist in the game.
03:32 Five letter words, there's a hundred, 615 of them here. That's where they exist. It's going to be on that list.
03:39 Um, there is a big bug and the bug that I can't figure out is this okay. So how, um, so let me show you how it works first.
03:47 It is, uh, it is, um, conditional formatting. Let's go up to two format format and we'll be able to conditional formatting.
03:58 It is to conditional formatting. First, we just check that it's a green, we'd say it's green. If C two is equal to C nine.
04:06 Now the dollar sign is there. It's a custom formula. The dollar sign is there so that it always checks on CNI and C nine is actually a, um, sort of just a hidden word.
04:17 It is the word on the random row. So this is a random row that's created with the script. Um, when I said I can reset the new game and you can see that there'll be a different row here.
04:29 And it is a different word, Jones Jones and for a new game, every time I say, Hey, we new game, they'll read, have a new row.
04:41 Um, conditional formatting always checks that C nine four, if it's the correct one, if it is the wrong one. So if it is the correct letter, but in the wrong spot we use or so in this custom form and let me move my face over here we use, or, and we say, if it's one of these things, change it to yellow and it's all of the C 12.
05:06 Okay. So here's how I had to make a little bit of a fix. Let me reveal what's going on here.
05:12 So if I just reveal this, you can see it, but also there is in C nine, it's changed that text color to black.
05:22 There's the word as well. And then let's do that as well here. Okay. So what's going on here? You ask, well, let's start guessing.
05:35 Let's let's do, uh, do we have a D I U let's try stone. So we have S T N E C.
05:52 These are yellow and these are green because it's stand, notice the part S T N the N are missing here.
06:02 It's because as we guess it correctly, it'll take it out of the next line. And so what we've done, let's go back to our conditional formatting.
06:13 Um, the C to here is not, does not have a dollar sign, so it will change. So C two is looking at C 12.
06:21 If the rain, if the actual cell is a C3, it will change, but the seed, and then it will also change the 12 to 13.
06:31 So it'll look down here 13, and we'll say, does this S exist? And it won't have, um, it, it won't show if it doesn't exist, if we've already gotten what this fixes is, actually, if there are two letters, uh, let me actually do there.
06:58 So if the S is actually gotten and another S appears, it would usually show yellow, um, but in, it should be green here.
07:06 If it's, uh, S here, this is the existing bond that still exists. If there are two letters in the guests, and there's only one in the result, in the answer, they will both show up as yellow.
07:23 So if you are, uh, looking for a challenge, then try to figure out how to not make that happen. Right.
07:30 It is some, it has something to do with this, or right. Um, it has something to do. We have to like take out letters once we guessed them again.
07:40 Um, but I already did that for the green ones so that we don't have that problem here. So if we do S T a, uh, let's do stats, right?
07:55 This is not yellow. So it used to be that this one would also be yellow, even though we only have one S in the result in the answer.
08:02 Um, but now by doing this part, by having basically, if it's the correct answer, we just take it out. Um, we have now fixed that problem, but the problem still exists.
08:15 If you've guessed two letters, and there is only one in the answer, it should be the actual results should be the, this first S is yellow.
08:25 And the second S is, does nothing remains gray. Um, again, if you want to try fixing this, go ahead and try and let me know what your solution is.
08:38 I will continue this video and go into, um, the reset code, because I think out of all of this, I think that is the most interesting part of it.
08:46 If you are looking to use something from the sheet in a sheet that you're making. So again, if you're making some kind of game, or maybe like, you're trying to enter information into a sheet like each day, and it needs to be changed, but at the beginning of the day, say, you want to like clear out any information from the previous day, you can actually create a script.
09:11 And now we'll show you the world script here, Wordle in a Google sheet script, we created a new menu. So that's what this function on open does just like creates a new menu, creates a menu up here, called word menu, right there.
09:25 Then we have new game and we're going to go down here. New game. What new game does is it does three, um, functions clear as the game, hides the answer and creates a new row or gets a new route.
09:40 So the first one is we just clear the content. We go, spreadsheet, app dockets, spreadsheet at the spreadsheet. Doc gets you by name dot, get range, get the quick range and clear content.
09:50 That is it. That is the whole script. Just to clear the game, then hide the answer. What we want to do is just in case the person, somebody has checked this box down here, a 10, just in case someone checked it because they want to know what the answer was, and then forgot to uncheck it.
10:09 Meaning we need to reset the entire game. We need to uncheck that all we're doing is setting it's false. So, um, we take, whereas it high to answer spreadsheet, app dot, get active spreadsheet.
10:23 Duckett she by name dot get range, a 10 set value to false. And that is it. So if you want to take a check box and set it from a check to unchecked, just set that value to false done.
10:34 Now the new row, what we do is first we clear the content of B 11, C what the 11 is B 11.
10:45 That is the row. So we clear that then we take, create a random number with this math dot floor. And then we do math dot random times, 615, and we add one, so it's not zero.
10:57 And then we enter that number. They set that value at to be 11. And that is it. That's the entire script is 1, 2, 3, 4, 5 lines of code is resetting the game.
11:11 And you can use this code in other kinds of games, in any kind of information you're trying to get daily or weekly, what you could do as well.
11:18 And I have not done it here is, uh, go over to triggers and create a, an hourly trigger board daily trigger or a, um, let's see, create a new trigger and show.
11:32 You can also add trigger here. We want to, um, choose which one to do that we would probably do your game.
11:42 Yep. Head's fine from, Nope. We want time-driven. We can do day-timer it's like time of day. And within this hour, probably like sometime like 1:00 AM and 5:00 AM.
11:56 You said, uh, that time, and that runs at that reset happens. You can also do an hourly, um, and any time here, every hour, every two hours, every 12 hours.
12:09 So this is a really, really powerful script. Uh, even though it's fairly short, just reset, clear something and set, set it to the right thing.
12:17 Um, and it can happen. See here. So let's go very quickly, new guy running the script, but we got a new game.
12:27 We have a new answer here. I'm going to go. And also I have to, I have to, um, re hide these, so I will do that now, too.
12:41 There we go. So we're hidden. Uh, let's do cause let's play one more time. All right. Finish. Got to enter each letter as it is stacked to try.
13:01 Okay. Got it, man. That was fast. Alright. Um, thanks for watching this video of  again, it is a work in progress.
13:29 So if I actually, or when we can figure this out, or if we figure this out, um, how to get rid of this weird little bug, I will make a new video.
13:38 And, um, I will also be releasing this publicly. So right at this moment, I'm not going to release a publicly, maybe later this week or next week.
13:46 Um, if we do choose, but I love playing well, I hope you play. I hope you can play a word or two in this Google sheet.
13:53 Um, please go play the original Wordle. It is a fun, fun, fun game. Every every day. It has a lot more features like actual good game play and not, not a busted, um, broken like we do.
14:10 Okay. It doesn't do it here, but if, uh, maybe it was, yeah, there we go. So that's probably a problem.
14:21 The second are, should probably be a gray, not yellow. All right. Thanks so much for watching. Um, feel free to ask any questions.