How to protect your Google Sheet with a password - Full tutorial, No add-on

In this tip, we will create from scratch a simple password protection for your sheet using Google Apps Script
Unlike other videos, there is no add-on or template you need to download, you can just follow the instructions.
UPDATE NOTE: This is an exercise on testing the limits of Google Sheets, but it is not a failproof method. As many of you have pointed out, with enough persistence, or just by clicking away you can bypass the system. As I say in the video, the only way I know that is failproof, is not sharing the file.
➤You can find the template from this video in my patreon page, where you´ll also have priority responses to your questions
practicalsheet...
You can also join my academy practicalsheets.com, where you´ll find, not only the templates for this and all the videos, but also Google Sheets Courses, technical service, and more
➤You can also subscribe to the channel where you´ll find a new Sheets video every week.
Here are some of the places where you can follow and support me:
➤Patreon: practicalsheet...
➤Webpage: practicalsheet...
➤Newsletter: practicalsheet...
➤Telegram: practicalsheet...
➤Facebook: practicalsheet...
➤Twitter: practicalsheet...
Any suggestion, question or insights, feel free to comment below

[@practicalsheets]

This is an exercise on testing the limits of Google Sheets, but it is not a failproof method. As many of you have pointed out, with enough persistence, or just by clicking away you can bypass the system. As I say in the video, the only way I know that is failproof, is not sharing the file. Regards!

[@superpauleesworld8170]

What an EXCELLENT TUTORIAL TEACHER you are. 😇💯👍 Great, kind voice and very easy to follow as you make it all simple. Thank you!!

[@jaspreetsingharora7070]

Is there a way to hide contents until the password is correct ?

[@adoniscontreras8042]

Yes. After you're done working on the sheet, you can click the button "hide sheet". The sheet will not be visible until you enter the password & then click on "show hidden sheets". I'm pretty sure you could program this with script as well but I don't know how

[@gabrielchun7218]

@practicalsheets, can you help us please? The box works, but it still lets access to the file. I just have to click away from the box.

[@tomsandoval-ym6jj]

yep

[@EnzoAugustoPennaAthayde]

same thing here

[@practicalsheets]

You are right It is not fool proof. I don´t think there is any way, other than not sharing the file with the account. I will put a warning message in the description Thanks for the messages! Kind Regards

[@maayanyacobi8665]

if i click on ESC it gives me the prompt "Error resuming script execution" - how do i strength the script to make sure it overcome such cases ?

[@victorakindipe3948]

This is great. Thanks for the video. is it possible to password just a single worksheet? I have different worksheets but only want to password just one.

[@muhammadhaqny]

I was searching for the same solution to protect my Google sheets, It was a great tutorial...

[@padhichittaranjan11]

Very fine sir. Please upload more video like protect all formula in the worksheet

[@nelboqueo5784]

i can bypass the password. i can edit direct to the sheet even without entering the password

[@RexWu]

just clicking away removes the box and then access to the sheet is gained. was wondering if you had a patch for this

[@practicalsheets]

Youre right Ive tried but there is no easy way You could watch this video for a couple of ideas kzbin.info/www/bejne/mIObdaycl95rjsU Regards!

[@chimneera]

Is there a way I can keep my password from showing if someone is spying at my typing. Have it **** as I enter said password?

[@sajinj2457]

Nice tutorial. Please create different sheet access role with password

[@jasaamanah6171]

yes, please

[@Mrnormann11]

Hey. I have never done any coding before i did this. Thank you so mutch for the tutorial. I have manage to get most to work, my only problom is that i cant get it to function unless i hit run in apps scripts. Meaning when i close the file and open it again, its still not password protected. I cant see any mistake in my coding. Any ideas? what did i miss?

[@AndrewLouwrens]

Thanks so much, great help

[@practicalsheets]

Thank you!

[@nelboqueo5784]

there is a propmt that appears "Error resuming script execution" if i click "dismiss" i can access the file

[@maayanyacobi8665]

That's Right ! - if i click on ESC it gives me this prompt above - how do i strength the script to make sure it overcome such cases ?

[@dealmommy]

Same here. Did you find a solution?

[@WaqasMunir]

Love your stuff. Great !

[@practicalsheets]

Thank you!

[@deepakukreti]

I was looking for a solution like this. Thanks for making it simple. Could you also help in some way we could password protect the entire workbook on Google sheets. This method is good for one sheet, but if I wish to protect the entire Workbook it doesn't work.

[@practicalsheets]

Thanks for your words The best way to protect the whole book is to give just "Read" Permission Kind Regards

[@bloggerkickstart9060]

there is a big problem with the system. you can retry it till it won't show any prompt password again. My test shows that around 26 times and it will not show up anymore and you can use the sheet. any way to solve the problem?

[@tripriancetripriance5743]

It is a very helpful tutorial for someone that not using micr***** and to protect the file itself without any add-on or 3rd party application. since I don't have any coding basic and your explanation is very loud and clear. I run for some issue as follow : 1. I have entered the correct password but it keeps ask to re-enter it 2. Password it works if I am open it through Laptop or Desktop, but when it open with Smartphone, it still can be accessed without entering the password could you advise how to fix this issue. Thank you😊

[@muhammadhaqny]

I am also having issue with a password, it keeps asking me for the password whenever I refresh the Google sheet...

[@AllAboutBg]

Thank you!

[@practicalsheets]

Thank you for commenting!

[@boratit8139]

Why I can’t do it ? Do you use any tool or app for supporting. This

[@practicalsheets]

Hello No additional tool Any particular error? Kind Regards

[@bentechie9104]

Good tutorial Man, Great work👍

[@practicalsheets]

Thank you!

[@EnzoAugustoPennaAthayde]

@@practicalsheets isnt functional. if u just click out of the box the script will fail

[@practicalsheets]

@@EnzoAugustoPennaAthayde You are right. As a fail proof system it is not useful. It is an experiment to see how far we can go with Sheets. I'm not sure there is a way to do it. MAybe Google will have a similar feature in the future Regards!

[@practicalsheets]

@@EnzoAugustoPennaAthayde You're right. It is more of an exercise in the capabilities of Sheet. I don't think there is a failproof way of doing it. (Maybe there is, and I don't know how) Thanks for the comment!

[@vallehv10011]

This is awesome, is there a way to activate this password when the sheet link is shared when anyone with the link can see it? When I open the file in an incognito tab it does not ask for the password. It only ask the password when I am log in with a gmail account.

[@practicalsheets]

Hmmm Interesting I think that if you may share it with anyone, it is not possible. The best way would be to share with Edit permit to the people who would have passwords Kind Regards

[@cslahore]

Thanks for sharing such a great video, I was also looking for a script or code which may help to protect the online sheet. But unfortunately, I don't know the macro codes that's why its not executing and generating an error message at line 9. Can you please paste this script code to help more people , they may change their email address and password for their sheets.

[@practicalsheets]

Hello! Please paste the exact error so I can help you figure it out You will be able to copy the exact template in the patreon page. Regards!

[@cslahore]

@@practicalsheets Thanks for considering my request, Its really appriciatable. Error reference is as under: 2:43:59 PM Notice Execution started 2:44:00 PM Error ReferenceError: session is not defined protectByPassword @

[@cslahore]

@@practicalsheets Error message Execution started 2:44:00 PM Error ReferenceError: session is not defined protectByPassword

[@practicalsheets]

@@cslahore Try putting upper case in Session Kind Regards

[@nathanread9969]

Is it possible to just protext one sheet tab within a google sheet so that if that tab is clicked on it asks for a password?

[@practicalsheets]

I think it could be done You could use the onSelectionChange trigger Regards!

[@ETECHAzeem]

Thanks for amazing help dear, But I have one issue with this, all of your method does not work if we share sheet to any one as Viewer only and disable the download and copy option for them. Please do something for this as well So your code also works for users who have only viewing only rights with copy and download option disabled.

[@practicalsheets]

Hello! Thank you for the suggestion I will look into it Kind Regards

[@tanyasmith5138]

Great video, but didn't work for me. )-: The box works, but it still lets me access the file. I just have to click away from the box.

[@muhammadshehrayarkhan2691]

same on my side

[@thegrandzeno1404]

Can you do this on android phones and tablets?

[@practicalsheets]

I don´t think it can be done Regards!

[@ifineNick]

wowow Great!

[@practicalsheets]

Thank you!

[@fundinghomes]

Extensions doesn't exist in my menu bar

[@muhammadhaqny]

I am having an issue with a password, it keeps asking me for the password whenever I refresh the Google sheet...

[@ThanhVuXuan-ty6ct]

excuse me! How to apply for only one sheet?

[@robinnauli4891]

can it be 2 admin ?

[@practicalsheets]

Yes! You should use && or || to concatenate the conditions, something like this if(email!=adminMail1 && email!=adminMail2) Regards!

[@robinnauli4891]

i have another question , can we disable some menu in google spreadsheet , so people can see our script ? @@practicalsheets

[@kumarsandiproy6392]

which language is used here?

[@practicalsheets]

Hello! Google Apps Script is based on Javascript Regards!

[@HendrikBez]

how to show password before it show the data in the sheet

[@practicalsheets]

One option would be to activate a blank sheet before Not the most pro, but it would work Regards!

[@innominatestudio]

Hello, everything works fine until I discovered that this trigger did not pop up when I open my sheet on my tablet.

[@unboxedverdict]

Hey if any of you wants to have the code I have it so just ask and I can post the text

[@practicalsheets]

Excellent! Kind Regards

[@cryptalytics2435]

lol, you can just bypass password with chrome dev console... LMFAO

[@AhmadAli-xx3vr]

I was searching for the same solution to protect my Google sheets, It was a great tutorial...