Hey. We’ll cover how to password protect a Google sheet in this section. All of us use sheets, and some of us are already aware that Google Sheets does not have a direct built-in feature for protecting a sheet upon opening. You can lock cells, ranges, and sheets, but we are at a loss for how to password-protect an entire file upon opening, so we turn to some helpful workarounds and tricks.
I’ll show you how to password protect a Google Sheet today. A free Google Sheets add-on can also be used to accomplish this, but I prefer the app script approach and haven’t found any helpful free add-ons yet. Please let me know by leaving a comment below if you find any.
Why is it Important to Password Protect a Google Sheet
At times, we work with sensitive information. We don’t want to intentionally or unintentionally share the data with anyone because it is imperative. We want to hide the entire file in this case, but we have a way to protect sheets and ranges. Data hiding is the purpose of password protection. As you may recall from our earlier post, there are three cases in which we lock cells or ranges.
- The data is visible to the user but cannot be edited.
- After passing a warning note, the user can view and edit the data.
- The user cannot view or edit data.
In scenario number three, the entire sheet is shielded at the opening. As a result, a password will always be requested when attempting to open this sheet from any source, and without one, not even in view mode, can anyone access the file? It also becomes very important to learn how to password protect a Google Sheet because this method is not built-in and can be complicated.
How to Password Protect a Google Sheet?
To password protect your sheet, we have an app script code that you can use for free. Then, we have three HTML files to create a well-organized tool interface for the main menu. I’ll give you access to all the files; all you have to do to protect a Google Sheets file with a password as a beginner is to configure those code files with your Google Sheets.
You may also like>>> How to Lock Cells in Google Sheets [4 Ways]
How to Password Protect a Google Sheet – Using App Script Code
We will learn about the main app script code in this section, as well as how to password protect a Google Sheet file. I will walk you through obtaining the code, configuring it with your Google Sheets file, and using it to safeguard your Google Sheets. Let’s start by using the code below, which is free to use and can be copied from this file location.
Script Code>>>
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>
Take a copy of the code above.
Step 2>
Start the Google Sheets file (opening a new or clearly formatted file is recommended).
Step 3>
Select Extensions > Apps Script.
Step 4>
Paste the copied code into the code.gs section.
Step 5>
Select “Save Project“.
Step 6>
Select “Review Permissions“.
Step 7>
There will be a safety page; select “Advance“.
Step 8>
To proceed, choose a Google Account now.
Step 9>
After reading the disclaimer, select “go to untitled project unsafe” to continue in an unsafe manner.
Step 10>
Select “Allow“.
You’ll then be taken back to the app script code, where you can verify it’s operating correctly.
You won’t see any errors if you copied the code correctly, and your code file is set up to work with your Google Sheets file.
It’s time to add and set up HTML files.
How to Password Protect a Google Sheet – Set up HTML files
This section will cover how to add and configure HTML files with your app script code to complete your password protection menu, as well as how to password protect a Google Sheet. Now let’s get started with the files; all you have to do is copy and paste them.
HTML Code for Password Changing
Script Code>>>
<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>
Change Password
| Old Password | |
| New Password |
HTML code for encrypting password input
Script Code>>>
<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>
</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>
HTML code for decrypting input passwords
Script Code>>>
<html>
<form id='myForm' style="font-family:Georgia;">
<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>
</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 1>
Click the Plus icon to add files to your App script code editor, then select HTML.
Step 2>
I will advise you to rename your files in accordance with the code; for example, your file name should be (changepassword.html) without any spaces or capital letters.
Step 3>
In the code editor, copy the first HTML code, then save the file.
Step 4>
Add another file, and rename it (inputpasswordencrypt.html) avoiding spaces and capital letters.
Copy the second code, then save.
Remember that the file extension is .html, and it will be appended to your file names automatically.
Step 5>
Add one more file name it (inputpassworddecrypt.html) and paste the third code into this file.
All of your files are now prepared; all you need to do is save them and return to your sheets file.
Methods for Using Google Sheets Password Protection Tool
We now have to interact with the password protector tool’s user interface after setting up all the files. As we navigate to the main menu, you will notice a new item called “Protect File”.
Step 1>
Select “Protect File” from the top menu.
Step 2>
A pop-up prompt will show up; select Encrypt File to create a new password.
Note: Use your Google Account to finish the one-time authorization process.
Step 3>
There will be another prompt; enter your password here. then select “OK” to establish the password.
Step 4>
You can go to Decrypt File and remove the password to unprotect this sheet.
Step 5>
To change the password, select the Change Password option, enter the new password, followed by the old one, and click the Done button.
It shows how simple it is for you to manage these choices. And this is how a Google Sheet can be password-protected.
You may also like>>> How to Use HLOOKUP Function in Google Sheets [Complete Manual]
Frequently Asked Questions
Is It Possible to Password Protect a Google Doc Using Google Scripts?
Yes, you can password-protect a Google Doc using Google Scripts. You can use the ability to write Google Docs scripts to create a script that password-protects your document so that only those with permission can access it. This gives your private data on the Google Docs platform an additional layer of protection.
Is it possible to password-protect my sheet using the Google Sheets API?
Yes, you can password-protect your sheet using the Google Sheets API. You can extract data from Google Sheets and apply extra security measures, such as limiting access with passwords, by using the API. By adding this extra layer of security to your sheet, you can make sure that nobody but authorized users can view or modify the data.
Conclusion
You can easily password-protect your Google Sheets for additional data security by using the free script described in this article. Check the code and file names again if you experience any problems. In the digital age, taking these precautions is a responsible decision because data security is so important. Thank you for selecting our resources to improve your Google Sheets skills. Stay tuned for more helpful guides.
