How to Password Protect a Google Sheet [Free Script]

Key Takeaways: How to Password Protect a Google Sheet

To Password Protect a Google Sheet

Setup App Script Code:

Get the provided App Script Code.
Open a New Google Sheets File.
Go to Extensions > App Script.
Paste the code.
Save the project.

Setup HTML Code:

Get the provided HTML code.
Go to the Extensions > App Sctipt.
Click on the “+” sign, and make a new “HTML” file.
Save the project.

Use the Protection Method:

Go to your Sheets file.
Click on the newly added Menu item “Protect File“.
Then, select “Decrypt File“
It will ask for a password, and sheets file will be protected.

Hi. In this article, we will learn how to password protect a Google sheet. We all use sheets, and some of us already know that there is no direct built-in method in Google Sheets to protect a sheet on opening. You have ways to lock cells, range, and sheets, but when it comes to password protecting an entire file on opening, then we don’t have any way to go with so we use useful workarounds and some tricks to do it.

Today, I will show you how to password protect a Google Sheet using an app script. This can also be done by using a free add-on from Google Sheets extensions, but I prefer the app script method, and I don’t find any useful free add-on so far. If you find any please comment below to let me know.

Table of Contents

Importance of Password Protect a Google Sheet

Sometimes, we are working on private data. The data is very critical, and we don’t want to share it with anyone intentionally or unintentionally. We have a method to protect ranges and sheets, but in this case, we want to hide the entire file. The reason behind password protection is to hide the data. If you have read our previous article, when we lock cells or ranges there are three cases.

A user can see the data but cannot edit it.
A user can see the data and edit it after passing a warning note.
User cannot see or edit data.

In case 3, we are protecting the entire sheet on its opening. So, whenever a user will try to open this sheet from any source, then a password will be asked and without a password, no one can access the file even in the view mode. And, because this method is not built-in, and is a little complex as well, so it becomes very important to learn how to password protect a Google Sheet.

How to Password Protect a Google Sheet?

We have an app script code for you that can be used for free to password protect your sheet. Then we have three HTML files to make a good layout of the interface of this tool in the main menu. I will share all the files with you, all you need to do as a beginner is to set up those code files with your Google Sheets, and you will be able to password protect a Google Sheet file very easily.

How to Password Protect a Google Sheet – The App Script Code

In this section, we will see how to password protect a Google Sheet file, in this section, we will learn about the primary code for app script, and I will show you how to get the code, how to configure it with your Google sheet file, and how to use it to protect your Google Sheets. So, let’s get started with the below code, you can simply copy this code from here, it’s free to use.

var GLOBALID = "SpreadSheetProtector0";

function clearDb() {

var prop = PropertiesService.getUserProperties();

if (prop.getProperty("sheetencrypted-state-"+GLOBALID) != null) {

prop.deleteProperty("sheetencrypted-state-"+GLOBALID);

}

if (prop.getProperty("sheetencrypted-password-"+GLOBALID) != null) {

prop.deleteProperty("sheetencrypted-password-"+GLOBALID);

}

if (prop.getProperty("sheetencrypted-id-"+GLOBALID) != null) {

prop.deleteProperty("sheetencrypted-id-"+GLOBALID);

}

}

function showChangePasswordForm() {

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

spreadsheet.show(HtmlService.createHtmlOutputFromFile('changepassword'));

}

function changePassword(obj) {

Logger.log(obj.oldpassword);

var prop = PropertiesService.getUserProperties();

if (prop.getProperty("sheetencrypted-password-"+GLOBALID) != null) {

if (prop.getProperty("sheetencrypted-password-"+GLOBALID) != obj.oldpassword) {

return({'status':'notmatching'});

}

}

prop.setProperty("sheetencrypted-password-"+GLOBALID, obj.newpassword);

return({'status':'done'});

}

function checkstate1() {

var prop = PropertiesService.getUserProperties();

Logger.log("State - "+prop.getProperty("sheetencrypted-state-"+GLOBALID));

Logger.log("Id - "+prop.getProperty("sheetencrypted-id-"+GLOBALID));

Logger.log("Password - "+prop.getProperty("sheetencrypted-password-"+GLOBALID));

var ss = SpreadsheetApp.getActiveSpreadsheet();

var id = ss.getActiveSheet().getSheetId();

Logger.log(DriveApp.getFileById(SpreadsheetApp.getActiveSpreadsheet().getId()).getUrl()+"&gid="+id);

SpreadsheetApp.getActiveSpreadsheet().getSheets()[0].getRange('C1').setValue(ScriptApp.getService().getUrl());

}

function EncodeFromSheet() {

Logger.log("Starting EncodeFromSheet");

var prop = PropertiesService.getUserProperties();

var encrypted = prop.getProperty("sheetencrypted-state-"+GLOBALID);

if (encrypted == 2) {

Browser.msgBox('ATTENTION', 'The sheet is already encrypted!!', Browser.Buttons.OK);

return;

}

Logger.log("Sheet is un-encrypted. Proceeding.");

var password='';

if (prop.getProperty("sheetencrypted-password-"+GLOBALID) == null) {

Logger.log("Got null password, asking for one");

password=Browser.inputBox("Create a new password.", Browser.Buttons.OK_CANCEL);

if(password == 'cancel') {

return;

}

prop.setProperty("sheetencrypted-password-"+GLOBALID, password);

prop.setProperty("sheetencrypted-id-"+GLOBALID, SpreadsheetApp.getActiveSpreadsheet().getId());

prop.setProperty("sheetencrypted-url-"+GLOBALID, DriveApp.getFileById(SpreadsheetApp.getActiveSpreadsheet().getId()).getUrl());

Logger.log("Going to encoding after getting password");

EnCodeSheet(false);

getWebAppUrl();

}

else {

Logger.log("Got a paasword. Encrypting");

SpreadsheetApp.getActiveSpreadsheet().show(HtmlService.createHtmlOutputFromFile('inputpasswordencrypt'));

}

}

function encodeForRequest(obj) {

var prop = PropertiesService.getUserProperties();

Logger.log("|"+obj.password+"|"+prop.getProperty("sheetencrypted-password-"+GLOBALID)+"|");

if (prop.getProperty("sheetencrypted-password-"+GLOBALID) != obj.password) {

Logger.log("Passwords not matching. Return false");

return({'status':'failed'});

}

else {

EnCodeSheet(false);

getWebAppUrl();

return({'status':'success'});

}

}

function EnCodeSheet(id) {

var prop = PropertiesService.getUserProperties();

Logger.log(id);

var activesheet;

if(id == false) {

activesheet=SpreadsheetApp.getActiveSpreadsheet();

activesheet.setActiveSelection("A1:A1");

}

else {

activesheet=SpreadsheetApp.openById(prop.getProperty("sheetencrypted-id-"+GLOBALID));

}

if (prop.getProperty("sheetencrypted-state-"+GLOBALID) == 2) {

return;

}

for (var k=0; k<activesheet.getSheets().length; k++) {

var ss = activesheet.getSheets()[k];

var range = ss.getDataRange();

var vals = range.getValues();

//var actvals=[];



for (var i=2;i<vals.length; i++) {

for (var j=0; j<vals[i].length; j++) {

if (vals[i][j] != "") {

if (ss.getRange(i+1, j+1, 1, 1).getFormula() == "") {

vals[i][j]=encrypt(vals[i][j], 1);

ss.getRange(i+1, j+1, 1, 1).setValue(vals[i][j]);

}

}

}

}

}

prop.setProperty("sheetencrypted-state-"+GLOBALID, 2);

}

function DecodeFromSheet() {

var prop = PropertiesService.getUserProperties();

if (prop.getProperty("sheetencrypted-state-"+GLOBALID) == 1) {

Browser.msgBox('ATTENTION', 'The sheet is already in normal state!!', Browser.Buttons.OK);

return;

}

if (prop.getProperty("sheetencrypted-password-"+GLOBALID) == null) {

Browser.msgBox("You have not encoded the file yet!!!!");

return;

}

else {

SpreadsheetApp.getActiveSpreadsheet().show(HtmlService.createHtmlOutputFromFile('inputpassworddecrypt')); getWebAppUrl();

}

}

function decodeForRequest(obj) {

var prop = PropertiesService.getUserProperties();

Logger.log("Starting decodeForRequest - " +obj.password);

if (prop.getProperty("sheetencrypted-password-"+GLOBALID) != obj.password) {

Logger.log("Login failed");

return({'status':'failed'});

}

else {

Logger.log("Login success");

DeCodeSheet(false);

getWebAppUrl();

return({'status':'success'});

}

}

// 1 - sheet is in normal state.

// 2 - sheet is encrypted.



function DeCodeSheet(id) {

Logger.log("From DecodeSheet");

var prop = PropertiesService.getUserProperties();

var activesheet;

if(id == false) {

activesheet=SpreadsheetApp.getActiveSpreadsheet();

activesheet.setActiveSelection("A1:A1");

}

else {

activesheet=SpreadsheetApp.openById(prop.getProperty("sheetencrypted-id-"+GLOBALID));

}

if (prop.getProperty("sheetencrypted-state-"+GLOBALID) == 1) {

Logger.log("Already decoded");

return;

}

for (var k=0; k<activesheet.getSheets().length; k++) {

var ss = activesheet.getSheets()[k];

var range = ss.getDataRange();

var vals = range.getValues();

//var actvals=[];



for (var i=2;i<vals.length; i++) {

for (var j=0; j<vals[i].length; j++) {

if (vals[i][j] != "") {

if (ss.getRange(i+1, j+1, 1, 1).getFormula() == "") {

vals[i][j]=decrypt(vals[i][j], 1);

ss.getRange(i+1, j+1, 1, 1).setValue(vals[i][j]);

}

}

}

}

}

prop.setProperty("sheetencrypted-state-"+GLOBALID, 1);

}



function encrypt(text, key) {

var endResult = "";

key = key*7;

Logger.log(typeof(text));

if(typeof(text) == "number") {

text=text.toString();

}

if(typeof(text) != "string") {

Logger.log("Got invalid "+typeof(text)+" "+text);

return text;

}

var aa=text.split('');

var a; var b;

for(var j=0; j<aa.length; j++) {

a=text.charCodeAt(j);

if(j==0 && String.fromCharCode(a)==6) {

//= at start of cell will convert value to formula.

endResult += String.fromCharCode(a);

continue;

}

for(var i = 0; i < key; i++) {

if( !(a >= 123 || a < 31)) {

if(a+1 != 123) {

a += 1;

}

else {

a = 32;

}

}

}

endResult += String.fromCharCode(a);

}

return endResult;

}

function decrypt(text,key) {

var endResult = "";

key = key*7;

Logger.log(typeof(text));

if(typeof(text) == "number") {

text=text.toString();

}

if(typeof(text) != "string") {

Logger.log("Got invalid "+typeof(text)+" "+text);

return text;

}

var aa=text.split('');

var a;

for(var j=0; j<aa.length; j++) {

a=text.charCodeAt(j);

if(j==0 && String.fromCharCode(a)==6) {

//= at start of cell will convert value to formula.

endResult += String.fromCharCode(a);

continue;

}

for(var i = 0; i < key; i++) {

if( !(a >= 123 || a < 31)) {

if(a-1 != 31) {

a -= 1;

}

else {

a = 122;

}

}

else {

break;

}

}

endResult += String.fromCharCode(a);

}

return endResult;

}

function getHtml(msg,butt) {

html='<html>'+

'<head>'+

'</head>'+

'<body>'+

'<div style="width:100%; text-align:center; font-family:Georgia;">'+

'<h2 style="font-size:40px;"><i>Input You password.</i></h2>'+

'<form type="submit" action="'+ScriptApp.getService().getUrl()+'" method="post" style="font-size:22px;">'+

'<label>'+msg+'</label>'+

'<input type="password" name="password" value="" style="padding:5px; width:300px;" />'+

'<input type="submit" name="submit" value="'+butt+'" style="padding:5px;" />'+

'</form>'+

'</div>'+

'</body>'+

'</html>';

return html;

}

function doGet() {

var prop = PropertiesService.getUserProperties();

var password='';

var html='';

if (prop.getProperty("sheetencrypted-password-"+GLOBALID) == null) {

html='<html><body>You have not set any password</body></html>';

}

else {

var butt;

if(prop.getProperty("sheetencrypted-state-"+GLOBALID) == 1) {

butt='Encrypt';

}

else {

butt='decrypt';

}

html=getHtml('',butt);

}

return HtmlService.createHtmlOutput(html)

}

function doPost(e) {

var prop = PropertiesService.getUserProperties();

var html='';

if (prop.getProperty("sheetencrypted-password-"+GLOBALID) == null) {

html='<html><body>You have not set any password</body></html>';

}

else {

var butt;

if(prop.getProperty("sheetencrypted-state-"+GLOBALID) == 1) {

butt='Encrypt';

}

else {

butt='Decrypt';

}

var docurl=prop.getProperty("sheetencrypted-url-"+GLOBALID);

if(e.parameter.password != prop.getProperty("sheetencrypted-password-"+GLOBALID)) {

html=getHtml('<span style="color:red;">Incorrect password. Please retry!!!</span><br/>', butt);

return HtmlService.createHtmlOutput(html);

}

else {

if(e.parameter.submit == 'Encrypt') {

EnCodeSheet(true);

html=getHtml('<span style="color:green;">Encoded Successfully!! <a href="'+docurl+'">Click here to go back.</a></span><br/>', 'Decrypt');

}

else {

DeCodeSheet(true);

html=getHtml('<span style="color:green;">Decoded Successfully!! <a href="'+docurl+'">Click here to go back.</a></span><br/>', 'Encrypt');

}

return HtmlService.createHtmlOutput(html);

}

}

}



function onOpen() {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var menuEntries = [ {name: "Initialize", functionName: "Initialize"},

null,

{name: "Encrypt File", functionName: "EncodeFromSheet"},

{name: "Decrypt File", functionName: "DecodeFromSheet"},

null,

{name: "Change Password", functionName: "showChangePasswordForm"},

{name: "Get Webapp URL", functionName: "getWebAppUrl"}];

ss.addMenu("Protect File", menuEntries);

}

function getWebAppUrl() {

SpreadsheetApp.getActiveSpreadsheet().getSheets()[0].getRange('C1').setValue('=HYPERLINK("'+ScriptApp.getService().getUrl()+'", "http://script.google.com/...")');

}

function onInstall() {

onOpen();

}

function Initialize() {

return;

}

var GLOBALID = "SpreadSheetProtector0";

function clearDb() {

var prop = PropertiesService.getUserProperties();

if (prop.getProperty("sheetencrypted-state-"+GLOBALID) != null) {

prop.deleteProperty("sheetencrypted-state-"+GLOBALID);

}

if (prop.getProperty("sheetencrypted-password-"+GLOBALID) != null) {

prop.deleteProperty("sheetencrypted-password-"+GLOBALID);

}

if (prop.getProperty("sheetencrypted-id-"+GLOBALID) != null) {

prop.deleteProperty("sheetencrypted-id-"+GLOBALID);

}

}

function showChangePasswordForm() {

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

spreadsheet.show(HtmlService.createHtmlOutputFromFile('changepassword'));

}

function changePassword(obj) {

Logger.log(obj.oldpassword);

var prop = PropertiesService.getUserProperties();

if (prop.getProperty("sheetencrypted-password-"+GLOBALID) != null) {

if (prop.getProperty("sheetencrypted-password-"+GLOBALID) != obj.oldpassword) {

return({'status':'notmatching'});

}

}

prop.setProperty("sheetencrypted-password-"+GLOBALID, obj.newpassword);

return({'status':'done'});

}

function checkstate1() {

var prop = PropertiesService.getUserProperties();

Logger.log("State - "+prop.getProperty("sheetencrypted-state-"+GLOBALID));

Logger.log("Id - "+prop.getProperty("sheetencrypted-id-"+GLOBALID));

Logger.log("Password - "+prop.getProperty("sheetencrypted-password-"+GLOBALID));

var ss = SpreadsheetApp.getActiveSpreadsheet();

var id = ss.getActiveSheet().getSheetId();

Logger.log(DriveApp.getFileById(SpreadsheetApp.getActiveSpreadsheet().getId()).getUrl()+"&gid="+id);

SpreadsheetApp.getActiveSpreadsheet().getSheets()[0].getRange('C1').setValue(ScriptApp.getService().getUrl());

}

function EncodeFromSheet() {

Logger.log("Starting EncodeFromSheet");

var prop = PropertiesService.getUserProperties();

var encrypted = prop.getProperty("sheetencrypted-state-"+GLOBALID);

if (encrypted == 2) {

Browser.msgBox('ATTENTION', 'The sheet is already encrypted!!', Browser.Buttons.OK);

return;

}

Logger.log("Sheet is un-encrypted. Proceeding.");

var password='';

if (prop.getProperty("sheetencrypted-password-"+GLOBALID) == null) {

Logger.log("Got null password, asking for one");

password=Browser.inputBox("Create a new password.", Browser.Buttons.OK_CANCEL);

if(password == 'cancel') {

return;

}

prop.setProperty("sheetencrypted-password-"+GLOBALID, password);

prop.setProperty("sheetencrypted-id-"+GLOBALID, SpreadsheetApp.getActiveSpreadsheet().getId());

prop.setProperty("sheetencrypted-url-"+GLOBALID, DriveApp.getFileById(SpreadsheetApp.getActiveSpreadsheet().getId()).getUrl());

Logger.log("Going to encoding after getting password");

EnCodeSheet(false);

getWebAppUrl();

}

else {

Logger.log("Got a paasword. Encrypting");

SpreadsheetApp.getActiveSpreadsheet().show(HtmlService.createHtmlOutputFromFile('inputpasswordencrypt'));

}

}

function encodeForRequest(obj) {

var prop = PropertiesService.getUserProperties();

Logger.log("|"+obj.password+"|"+prop.getProperty("sheetencrypted-password-"+GLOBALID)+"|");

if (prop.getProperty("sheetencrypted-password-"+GLOBALID) != obj.password) {

Logger.log("Passwords not matching. Return false");

return({'status':'failed'});

}

else {

EnCodeSheet(false);

getWebAppUrl();

return({'status':'success'});

}

}

function EnCodeSheet(id) {

var prop = PropertiesService.getUserProperties();

Logger.log(id);

var activesheet;

if(id == false) {

activesheet=SpreadsheetApp.getActiveSpreadsheet();

activesheet.setActiveSelection("A1:A1");

}

else {

activesheet=SpreadsheetApp.openById(prop.getProperty("sheetencrypted-id-"+GLOBALID));

}

if (prop.getProperty("sheetencrypted-state-"+GLOBALID) == 2) {

return;

}

for (var k=0; k<activesheet.getSheets().length; k++) {

var ss = activesheet.getSheets()[k];

var range = ss.getDataRange();

var vals = range.getValues();

//var actvals=[];



for (var i=2;i<vals.length; i++) {

for (var j=0; j<vals[i].length; j++) {

if (vals[i][j] != "") {

if (ss.getRange(i+1, j+1, 1, 1).getFormula() == "") {

vals[i][j]=encrypt(vals[i][j], 1);

ss.getRange(i+1, j+1, 1, 1).setValue(vals[i][j]);

}

}

}

}

}

prop.setProperty("sheetencrypted-state-"+GLOBALID, 2);

}

function DecodeFromSheet() {

var prop = PropertiesService.getUserProperties();

if (prop.getProperty("sheetencrypted-state-"+GLOBALID) == 1) {

Browser.msgBox('ATTENTION', 'The sheet is already in normal state!!', Browser.Buttons.OK);

return;

}

if (prop.getProperty("sheetencrypted-password-"+GLOBALID) == null) {

Browser.msgBox("You have not encoded the file yet!!!!");

return;

}

else {

SpreadsheetApp.getActiveSpreadsheet().show(HtmlService.createHtmlOutputFromFile('inputpassworddecrypt')); getWebAppUrl();

}

}

function decodeForRequest(obj) {

var prop = PropertiesService.getUserProperties();

Logger.log("Starting decodeForRequest - " +obj.password);

if (prop.getProperty("sheetencrypted-password-"+GLOBALID) != obj.password) {

Logger.log("Login failed");

return({'status':'failed'});

}

else {

Logger.log("Login success");

DeCodeSheet(false);

getWebAppUrl();

return({'status':'success'});

}

}

// 1 - sheet is in normal state.

// 2 - sheet is encrypted.



function DeCodeSheet(id) {

Logger.log("From DecodeSheet");

var prop = PropertiesService.getUserProperties();

var activesheet;

if(id == false) {

activesheet=SpreadsheetApp.getActiveSpreadsheet();

activesheet.setActiveSelection("A1:A1");

}

else {

activesheet=SpreadsheetApp.openById(prop.getProperty("sheetencrypted-id-"+GLOBALID));

}

if (prop.getProperty("sheetencrypted-state-"+GLOBALID) == 1) {

Logger.log("Already decoded");

return;

}

for (var k=0; k<activesheet.getSheets().length; k++) {

var ss = activesheet.getSheets()[k];

var range = ss.getDataRange();

var vals = range.getValues();

//var actvals=[];



for (var i=2;i<vals.length; i++) {

for (var j=0; j<vals[i].length; j++) {

if (vals[i][j] != "") {

if (ss.getRange(i+1, j+1, 1, 1).getFormula() == "") {

vals[i][j]=decrypt(vals[i][j], 1);

ss.getRange(i+1, j+1, 1, 1).setValue(vals[i][j]);

}

}

}

}

}

prop.setProperty("sheetencrypted-state-"+GLOBALID, 1);

}



function encrypt(text, key) {

var endResult = "";

key = key*7;

Logger.log(typeof(text));

if(typeof(text) == "number") {

text=text.toString();

}

if(typeof(text) != "string") {

Logger.log("Got invalid "+typeof(text)+" "+text);

return text;

}

var aa=text.split('');

var a; var b;

for(var j=0; j<aa.length; j++) {

a=text.charCodeAt(j);

if(j==0 && String.fromCharCode(a)==6) {

//= at start of cell will convert value to formula.

endResult += String.fromCharCode(a);

continue;

}

for(var i = 0; i < key; i++) {

if( !(a >= 123 || a < 31)) {

if(a+1 != 123) {

a += 1;

}

else {

a = 32;

}

}

}

endResult += String.fromCharCode(a);

}

return endResult;

}

function decrypt(text,key) {

var endResult = "";

key = key*7;

Logger.log(typeof(text));

if(typeof(text) == "number") {

text=text.toString();

}

if(typeof(text) != "string") {

Logger.log("Got invalid "+typeof(text)+" "+text);

return text;

}

var aa=text.split('');

var a;

for(var j=0; j<aa.length; j++) {

a=text.charCodeAt(j);

if(j==0 && String.fromCharCode(a)==6) {

//= at start of cell will convert value to formula.

endResult += String.fromCharCode(a);

continue;

}

for(var i = 0; i < key; i++) {

if( !(a >= 123 || a < 31)) {

if(a-1 != 31) {

a -= 1;

}

else {

a = 122;

}

}

else {

break;

}

}

endResult += String.fromCharCode(a);

}

return endResult;

}

function getHtml(msg,butt) {

html='<html>'+

'<head>'+

'</head>'+

'<body>'+

'<div style="width:100%; text-align:center; font-family:Georgia;">'+

'<h2 style="font-size:40px;"><i>Input You password.</i></h2>'+

'<form type="submit" action="'+ScriptApp.getService().getUrl()+'" method="post" style="font-size:22px;">'+

'<label>'+msg+'</label>'+

'<input type="password" name="password" value="" style="padding:5px; width:300px;" />'+

'<input type="submit" name="submit" value="'+butt+'" style="padding:5px;" />'+

'</form>'+

'</div>'+

'</body>'+

'</html>';

return html;

}

function doGet() {

var prop = PropertiesService.getUserProperties();

var password='';

var html='';

if (prop.getProperty("sheetencrypted-password-"+GLOBALID) == null) {

html='<html><body>You have not set any password</body></html>';

}

else {

var butt;

if(prop.getProperty("sheetencrypted-state-"+GLOBALID) == 1) {

butt='Encrypt';

}

else {

butt='decrypt';

}

html=getHtml('',butt);

}

return HtmlService.createHtmlOutput(html)

}

function doPost(e) {

var prop = PropertiesService.getUserProperties();

var html='';

if (prop.getProperty("sheetencrypted-password-"+GLOBALID) == null) {

html='<html><body>You have not set any password</body></html>';

}

else {

var butt;

if(prop.getProperty("sheetencrypted-state-"+GLOBALID) == 1) {

butt='Encrypt';

}

else {

butt='Decrypt';

}

var docurl=prop.getProperty("sheetencrypted-url-"+GLOBALID);

if(e.parameter.password != prop.getProperty("sheetencrypted-password-"+GLOBALID)) {

html=getHtml('<span style="color:red;">Incorrect password. Please retry!!!</span><br/>', butt);

return HtmlService.createHtmlOutput(html);

}

else {

if(e.parameter.submit == 'Encrypt') {

EnCodeSheet(true);

html=getHtml('<span style="color:green;">Encoded Successfully!! <a href="'+docurl+'">Click here to go back.</a></span><br/>', 'Decrypt');

}

else {

DeCodeSheet(true);

html=getHtml('<span style="color:green;">Decoded Successfully!! <a href="'+docurl+'">Click here to go back.</a></span><br/>', 'Encrypt');

}

return HtmlService.createHtmlOutput(html);

}

}

}



function onOpen() {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var menuEntries = [ {name: "Initialize", functionName: "Initialize"},

null,

{name: "Encrypt File", functionName: "EncodeFromSheet"},

{name: "Decrypt File", functionName: "DecodeFromSheet"},

null,

{name: "Change Password", functionName: "showChangePasswordForm"},

{name: "Get Webapp URL", functionName: "getWebAppUrl"}];

ss.addMenu("Protect File", menuEntries);

}

function getWebAppUrl() {

SpreadsheetApp.getActiveSpreadsheet().getSheets()[0].getRange('C1').setValue('=HYPERLINK("'+ScriptApp.getService().getUrl()+'", "http://script.google.com/...")');

}

function onInstall() {

onOpen();

}

function Initialize() {

return;

}

Step 1

Copy the above code.

Step 2

Open your Google Sheets file (a new file is recommended, or a file with clear formatting)

Step 3

Go to Extensions > Apps script

Step 4

In the code.gs section, paste the copied code

Step 5

Click on the save project

Step 6

Click on review permissions

Step 7

A safety page will appear, click on advance

Step 8

Now select a Google Account to continue

Step 9

Now read the disclaimer, and click on “go to untitled project unsafe” if you want to proceed unsafely.

Step 10

Click on Allow

And you will be redirected back to your app script code, and you can now see it’s running properly.

If you copied the code properly, you will not see any kind of error, and your code file is configured with your Google Sheets file.

Now, we need to add and configure HTML files.

How to Password Protect a Google Sheet – The HTML files

In this section, we will learn how to password protect a Google Sheet, and add and configure HTML files with your app script code to finalize your password protection menu. Let’s directly start with the files, you simply need to copy and paste these.

Step 1: HTML code for Change Password


<html>

<form id='myForm' style="font-family:Georgia;">

<h1>Change Password</h1>

<table>

<tr><td>Old Password</td><td><input name='oldpassword' type='password' value="" ></td></tr>

<tr><td>New Password</td><td><input name='newpassword' type='password' value="" ></td></tr>

</table>

<div id="submitbuttcontainer"><img id="submitloader" style="display:none;" src='https://lh6.googleusercontent.com/-S87nMBe6KWE/TuB9dR48F0I/AAAAAAAAByQ/0Z96LirzDqg/s27/load.gif' />

<input id="submitbutt" type='button' onclick='showWorking();google.script.run.withSuccessHandler(onSuccess).changePassword(document.getElementById("myForm"));' name="Submit" value="Submit"></div>

</form>

<script>

function onSuccess(obj) {

document.getElementById('submitbutt').style.display="block";

document.getElementById('submitloader').style.display="none";

if(obj.status == 'done') {

google.script.host.closeDialog();

}

else {

alert("Incorrect Old Password. Please retry");

}

}

function showWorking() {

document.getElementById('submitbutt').style.display="none";

document.getElementById('submitloader').style.display="block";

}

</script>

</html>

Step 2: HTML code for Input Password Encryption


<html>

<form id='myForm' style="font-family:Georgia;">

<table>

<tr><td><h2>Enter your Password</h2></td></tr>

<tr><td><input name='password' class='password' type='password' value="" ></td></tr>

<tr><td><div id="submitbuttcontainer"><img id="submitloader" style="display:none;" src='https://lh6.googleusercontent.com/-S87nMBe6KWE/TuB9dR48F0I/AAAAAAAAByQ/0Z96LirzDqg/s27/load.gif' /><input id="submitbutt" type='button' onclick='showWorking();google.script.run.withSuccessHandler(onSuccess).encodeForRequest(document.getElementById("myForm"));' name="Submit" value="Submit"></div></td></tr>

</table>

&nbsp;

</form>

<script>

function onSuccess(obj) {

document.getElementById('submitbutt').style.display="block";

document.getElementById('submitloader').style.display="none";

if(obj.status == 'success') {

google.script.host.closeDialog();

}

else {

alert("Incorrect Password. Please retry");

}

}

function showWorking() {

document.getElementById('submitbutt').style.display="none";

document.getElementById('submitloader').style.display="block";

}

</script>

</html>

Step 3: HTML code for Input Password Decryption


<html>

<form id='myForm' style="font-family:Georgia;">

&nbsp;

<table>

<tr><td><h2>Enter your Password</h2></td></tr>

<tr><td><input name='password' type='password' value="" ></td></tr>

<tr><td><div id="submitbuttcontainer"><img id="submitloader" style="display:none;" src='https://lh6.googleusercontent.com/-S87nMBe6KWE/TuB9dR48F0I/AAAAAAAAByQ/0Z96LirzDqg/s27/load.gif' /><input id="submitbutt" type='button' onclick='showWorking();google.script.run.withSuccessHandler(onSuccess).decodeForRequest(document.getElementById("myForm"));' name="Submit" value="Submit"></div></td></tr>

</table>

&nbsp;

</form>

<script>

function onSuccess(obj) {

document.getElementById('submitbutt').style.display="block";

document.getElementById('submitloader').style.display="none";

if(obj.status == 'success') {

google.script.host.closeDialog();

}

else {

alert("Incorrect Password. Please retry");

}

}

function showWorking() {

document.getElementById('submitbutt').style.display="none";

document.getElementById('submitloader').style.display="block";

}

</script>

</html>

Step 4

Go to your App script code editor and click on the Plus icon to add files, then click on HTML

Step 5

Name your file, I will suggest you name your files according to the code, this file name should be (changepassword.html) without spaces and capitalization.

Step 6

Now paste the first HTML code in the code editor and save your file

Step 7

Add another file, and this time name your file (inputpasswordencrypt.html) without spaces and capitalization

Paste the second here and save

Note: .html is the file extension, and it will be added automatically to your file names.

Step 8

Add another file and name that file (inputpassworddecrypt.html) and paste the third code here.

Now all your files are ready, you just need to save them and get back to your sheets file

How to use the Password Protection Tool in Google Sheets

Since we have set up all the files, now we need to interact with the user interface of the password protector tool we have created. Let’s go to the main menu and you will see an additional item there with the name “Protect File”.

Step 1

Click on Protect File in the main menu

Step 2

A prompt pop-up will appear, click on Encrypt File to set up a new password

Note: Complete the one-time authorization process with Google Account.

Step 3

Another prompt will appear, you can type your password here. and click Ok to set the password.

Step 4

If you want to unprotect this sheet, you can go to Decrypt File and remove the password.

Step 5

If you want to change the password, then go to the Change Password option, type your old password, then the new password, and click on Done to change it.

So, this is how easily you can control these options. And this is how to password protect a Google Sheet

Frequently Asked Questions

Can I Use the Google Sheets API to Password Protect my Sheet?

Yes, you can use the Google Sheets API to password protect your sheet. By using the API, you can extract data from google sheets and implement additional security measures like setting passwords to restrict access. Safeguarding your sheet with this added layer of protection ensures that only authorized individuals can view and edit the data.

Conclusion

So, this is all about how to password protect a Google Sheet. I hope you like this article and find it easy. I hope you will easily set up all these files. In case you get any errors, try copy-pasting the code again and name your files appropriately. I will see you soon with another useful guide. Thank you and keep learning with Office Demy.