00:01 Hi everyone. Andrew here at better sheets today, we are password protecting a Google sheet. This is a very funky, fun video.
00:12 I hope you enjoy, uh, watching this and learning along with me. Uh, I learned a little bit of something tube doing this.
00:20 Um, let's I wanna give a big caveat to begin with. Uh, this is in no way actually password protecting to enter into a Google sheet.
00:33 It is a fun, interesting way to use the, if, uh, formula, uh, import range, because we want our data to show up only when password is entered and the password is entered into the sheet.
00:46 Um, so a user will have access to the sheet first and then write the password and then be able to see the data.
00:53 So this is not password protecting to enter a sheet. Uh, some use cases for this might be, um, you have different people that work on different things.
01:06 Uh, you might have a client portal, you might run an agency and you just want to, uh, share, uh, with certain people at the agency or at, at the, uh, client.
01:19 Uh, this is totally expandable as I, I will show you throughout this whole video, you can do this in three different ways, uh, with varying degrees of protection.
01:28 Um, the first way is almost no protection at all. The second way, uh, will give you a lot of protection, but your password will be seen inside the sheet in some way, not inside the sheet, but in an app script, in a script.
01:42 So if nobody in your organization knows APS script, then it's fine, but it is really not protected. And the third way is there will be an ENCO in encoding.
01:52 Uh, so your password will be encoded, but it'll be the most basic encoding you've ever seen. And I will show you how to break it too, because it's probably good to know how to break this code, uh, en coding and decode it.
02:08 Um, okay, so let's get started. What I did is I created a sheet with some data on a completely different sheet.
02:16 That's the first thing I did is I have two sheets, one, it has this high, I am data that is password protected, and it is on a completely different worksheet, um, in the sheet that I want to have that data in only if the data, if the password is correct, I have here.
02:32 So it will show up on this tab called data right now we do not have the password in here. So, um, I will show you like, if we write pass or, um, anything here, it doesn't show up and I'm not doing, I'm not stopping this video.
02:48 I'm not editing this video in any way. If we type in the, the, okay, I'll tell you the password is password password.
02:55 If we type in, and this is the correct password, it is set here. We look at data it's here, here.
03:00 Now we say, hi, I am data. That is password protected. And it's over here. That's, it's actually getting that data.
03:06 And it's only showing it if it is the correct password, how did I do this? This is the first level is in, uh, a one I wrote, well, first off I named this range.
03:20 So I went up here and I called this range C4 password. Okay. So that's the very first thing I did.
03:27 Then I went over here and I wrote if password, the range is equal to quote, quotes password. Then if that's true, then import the range from this other sheet.
03:41 And we're getting the entire sheet of data called data on a to Z. We're getting everything here. So we even get this, get this, we put this on this sheet and we go over here and we got this right.
03:54 But if our password is wrong, if we even it doesn't show up, okay. So that's a pretty darn week way to do a password because we can always see this formula.
04:09 Even if the password is not there, we can go here and see, oh, that's password. Very, very simple way to do password protection with almost no protection at all.
04:20 Okay. The second way I did it is that I stored the password in app script. So you can't see in a one, you don't see the, uh, if import.
04:32 So we go up to extensions and click on app scripts and it'll open a new tab and it'll, this is called an ID or, uh, ID E uh, way to edit code.
04:41 We go up to code and we see on edit. Uh, we say attempted password. We need the variable attempted password.
04:48 And we get it by just going to the cell C4 on the page login and we get it. And our actual password is right here.
04:55 We just type in password in quotes. Now, if we want to change that password, so let's say we change it to password, um, script to capitals.
05:10 We're just gonna hit command S to save. Now, if we type in password, it'll still work here, but the app script is not working.
05:21 So let's look at that. So we just type in password script. This is the actual password that's stored in our script.
05:26 We go to app script, it's here. How did that happen? If we type in anything else, if we delete it or we type in anything else, it doesn't show up.
05:38 How did we do that? There are, it's an ONED script. If the attempted password and the actual password right here are the same.
05:47 What it does is it goes to the value, a, uh, sorry, sell one, a one. And it sets the value as to equal import range.
05:55 And it it's the entire formula again, without the, if we just use import range. And if the attempted password is wrong, this is the, uh, exclamation point equals.
06:06 That means is not equal to actual password. Then it's actually gonna go and set the value to a space. Nothing.
06:14 Uh, this is interesting because we have a script that both adds the data, and then it also takes it away.
06:21 If that password is incorrect. All right, that's pretty cool. Right? I, I hope you're enjoying how, how cool this is.
06:30 Like we can type in password script and we get data over here. It loads it, and there it is. We got it.
06:37 Yes. And we can go over to our data and we can edit this data and it'll show up here, no matter what sort of, if that password is correct, that's pretty cool, but it is not that safe.
06:48 Right? In one case, the password is literally written into a formula. In the other case, the password is literally written into the app script.
06:58 So it is still accessible within the sheet, right? Someone comes to our sheet, they copy it. They look through everything, right?
07:05 They look through every tab, they look through every sheet, they look through every app script, they go up to extensions, they click on app script.
07:11 It's right there. <laugh> the first thing you see is password script, right? Um, we might want to encode this. Okay.
07:20 So we might want to, and now this is level three, this is the third level, and this is how we're going to really do it.
07:28 I'm going to comment out the, uh, information we just have there. And I'm gonna put this, we have this other script that I wrote.
07:40 Let me move my face over here for this. And in this, when we did exactly the same setup in this code, where we have ONED, if the attempted password is the same, we're gonna write the value, uh, of the, she, um, write the value of the formula that we want.
07:59 If the password is incorrect, we're gonna delete that, uh, formula. Okay. So here's an interesting thing is I had to write extra code here, which essentially allows us to both create a password and also encode that password in base 64 encoding and decode it.
08:22 Okay. So what it does now, <laugh>, let's go back here. We have a password menu and we can set our password.
08:30 So let's create a new password, not password, actually let's do password just to show you what happens. It will save it to this encoded.
08:40 And this is password. This is actually the word password, but it's encoded in bay 64. I will show you something cool.
08:46 In later in this video, um, in our encoded data, there's nothing on this sheet. There's literally no, uh, text. There's no password.
08:55 If we type in password on encoded sheet, we now see it. It is going through this script. It first encoded the password, literally the word password.
09:08 Then when we created that password with the script in the, uh, new menu, but it's going through it and it's decoding it and then setting the value.
09:21 So I wanna make a note about this incoding and decoding. It's not that safe, even though that the word password is not saved anywhere, right.
09:34 We can delete password and we can type in password. That's the only password we have. It's not going to, okay.
09:42 It's found on <laugh> your, but even in this script, you can't see the word password. Um, it's not saved, right?
09:54 It's actually saved in this encoded place right here. Here's where it gets a little, uh, not safe. So we can go to bay 64, decode.org.
10:04 We can type in this phrase like CGF C, Z UTF eight. We can hit the word decode, and there's the word password.
10:16 So it is a, just a different text than the word password, but it is very hard to under know that, right?
10:24 It's very hard to know that we encoded this space 64, unless you really read this code. And you see, uh, this part here, encode password utilities dot base, 64 N code that is literally the encoding of the password.
10:40 So this is pretty cool that we can then set any password we want. We can create the password. This will rewrite any old password so we can write something like gibberish.
10:54 And if we go to our encoded, this is a different text and we can decode it as well. And we'll see that it is gibber, the literal, literally the word gibberish.
11:03 But now if we go to login gibberish, if we go to our encoded data, it shows up, we have created a password enabled cheap.
11:16 Now of course, of, of course, there's even more, uh, risk inherent because we have this password menu that says create password.
11:25 Um, we, we can delete it if we want over here. Now we have no password. Um, in fact, I think it will, if we have no password and this has nothing in it, it will be the same.
11:37 And so now we have the data. All right. The thing I just fixed that if the password here is blank, then it's not going to show up.
11:47 I just added that line of script here and attempted password is not blank. So that is fixed. And I hope this video was educational to you for a few things.
11:59 One to know how to do, um, import range, here's import range. We're, we're using this import range from another sheet we're using the, if very basic thing to sort of hide information, um, and then write password, right?
12:15 If you, if you got the code, correct, boom, you got it. We can also do things in, I have other videos you can watch where we use check boxes to show data.
12:24 That's a really fun thing. And this is sort of a, a mutated version of that video. Uh, second, I did do an app script or script for you to have a text that is exactly the same as login, whatever you enter here.
12:39 And then the third way we did it was we encoded a, uh, password. We used a menu up here. We created a password.
12:47 Uh, we can write in a password. Yes. And now this is encoded here and we go into login the password and we show our encoded data is here.
12:59 We have entered it. And so that's really cool. So we have nothing in the sheet. We have all the stuff in the app script.
13:05 So even if you do get the app script, you really have to also know that, uh, about encoding and which algorithm and what's encoded.
13:12 You can definitely do this a few other ways, probably not in this video. I hope this was fun and interesting to see a password protected Google sheet and the data inside of it.
13:24 Really. Thank you. Keep watching better sheets, doco.