How to Password Protect Google Sheets (With Easy Steps)

Our spreadsheets from our organization or other workplaces often may contain some sensitive data which we may not want to share with everyone. For that, we can encrypt our data so that the original information gets hidden from others. And to get those data, the owner of that spreadsheet will have to decrypt those data otherwise no other individuals can decrypt those data. Which we can say is almost the maximum level of security. In this article, we have illustrated the method to password protect Google Sheets. After encrypting our data, the worksheet may look something like the following.

Overview to Password Protect Google Sheets


A Sample of Practice Spreadsheet

You can download the spreadsheet used for describing methods for this article from here.


Is There Any Built-in Command to Password Protect Google Sheets?

The answer is no. There is no built-in command to password protect like Microsoft Excel in Google Sheets. But, Google Sheets is improving more and more day by day. So sooner or later, the developers may include the feature in Google Sheets as well, who knows!


Step-by-Step Procedure to Password Protect Google Sheets

We will be using the following dataset as an example to describe the procedures to password protect Google Sheets. The dataset contains some employees’ information of a particular company.

dataset to Password Protect Google Sheets

To password protect our sheets, we need to avail the password protecting option at the toolbar. But initially there will be no option like that. So, to take advantage of the password protecting feature, we have to use the “Apps Script” feature first. Have a look, at the beginning, the toolbar will contain the following menus only.

defaults menus in the ribbon


Step 1: Open Apps Script

  • First, at the toolbar, select Extensions then click on Apps Script.

Opening Apps Script to insert codes to password protect google sheets

  • A new tab will be opened in your browser.

new apps script tab


Step 2: Insert Codes

  • First, copy the following code and paste it in the gs section. For that, at first, copy the code. Then select the whole content in Code.gs section by Ctrl+A first. Then simply press Ctrl+V to paste the copied code.
var IdPass = "SpreadSheetProtector0";
function clearDb() {
  var prop = PropertiesService.getUserProperties();
  if (prop.getProperty("sheetencrypted-state-"+IdPass) != null) {
    prop.deleteProperty("sheetencrypted-state-"+IdPass);
  }
  if (prop.getProperty("sheetencrypted-password-"+IdPass) != null) {
    prop.deleteProperty("sheetencrypted-password-"+IdPass);
  }
  if (prop.getProperty("sheetencrypted-id-"+IdPass) != null) {
    prop.deleteProperty("sheetencrypted-id-"+IdPass);
  }
}
function showChangePasswordForm() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  spreadsheet.show(HtmlService.createHtmlOutputFromFile('modifypassword'));
}
function modifypassword(obj) {
  Logger.log(obj.oldpassword);
  var prop = PropertiesService.getUserProperties();
  if (prop.getProperty("sheetencrypted-password-"+IdPass) != null) {
    if (prop.getProperty("sheetencrypted-password-"+IdPass) != obj.oldpassword) {
      return({'status':'notmatching'});
    }
  }
  prop.setProperty("sheetencrypted-password-"+IdPass, obj.newpassword);
  return({'status':'done'});
}
function checkstate1() {
  var prop = PropertiesService.getUserProperties();
  Logger.log("State - "+prop.getProperty("sheetencrypted-state-"+IdPass));
  Logger.log("Id - "+prop.getProperty("sheetencrypted-id-"+IdPass));
  Logger.log("Password - "+prop.getProperty("sheetencrypted-password-"+IdPass));
  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-"+IdPass);
  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-"+IdPass) == 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-"+IdPass, password);
    prop.setProperty("sheetencrypted-id-"+IdPass, SpreadsheetApp.getActiveSpreadsheet().getId());
    prop.setProperty("sheetencrypted-url-"+IdPass, 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('encryptbypassword'));
  }
}
function encodeForRequest(obj) {
  var prop = PropertiesService.getUserProperties();
  Logger.log("|"+obj.password+"|"+prop.getProperty("sheetencrypted-password-"+IdPass)+"|");
  if (prop.getProperty("sheetencrypted-password-"+IdPass) != 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-"+IdPass));
  }
  if (prop.getProperty("sheetencrypted-state-"+IdPass) == 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-"+IdPass, 2);
}
function DecodeFromSheet() {
  var prop = PropertiesService.getUserProperties();
  if (prop.getProperty("sheetencrypted-state-"+IdPass) == 1) {
    Browser.msgBox('ATTENTION', 'The sheet is already in normal state!!', Browser.Buttons.OK);
    return;
  }
  if (prop.getProperty("sheetencrypted-password-"+IdPass) == null) {
    Browser.msgBox("You have not encoded the file yet!!!!");
    return;
  }
  else {
    SpreadsheetApp.getActiveSpreadsheet().show(HtmlService.createHtmlOutputFromFile('decryptbypassword'));    getWebAppUrl();
  }
}
function decodeForRequest(obj) {
  var prop = PropertiesService.getUserProperties();
  Logger.log("Starting decodeForRequest - " +obj.password);
  if (prop.getProperty("sheetencrypted-password-"+IdPass) != 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-"+IdPass));
  }
  if (prop.getProperty("sheetencrypted-state-"+IdPass) == 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-"+IdPass, 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-"+IdPass) == null) {
    html='<html><body>You have not set any password</body></html>';
  }
  else {
    var butt;
    if(prop.getProperty("sheetencrypted-state-"+IdPass) == 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-"+IdPass) == null) {
    html='<html><body>You have not set any password</body></html>';
  }
  else {
    var butt;
    if(prop.getProperty("sheetencrypted-state-"+IdPass) == 1) {
      butt='Encrypt';
    }
    else {
      butt='Decrypt';
    }
    var docurl=prop.getProperty("sheetencrypted-url-"+IdPass);
    if(e.parameter.password != prop.getProperty("sheetencrypted-password-"+IdPass)) {
      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: "Activation", functionName: "Activation"},
                      null,
                      {name: "Encrypt Data", functionName: "EncodeFromSheet"},
                      {name: "Decrypt Data", functionName: "DecodeFromSheet"},
                      null,
                     {name: "Modify Password", functionName: "showChangePasswordForm"},
                      {name: "Get Webapp URL", functionName: "getWebAppUrl"}];
  ss.addMenu("Password Protect Sheets", menuEntries);
}
function getWebAppUrl() {
  SpreadsheetApp.getActiveSpreadsheet().getSheets()[0].getRange('C1').setValue('=HYPERLINK("'+ScriptApp.getService().getUrl()+'", "http://script.google.com/...")');
}
function onInstall() {
  onOpen();
}
function Initialize() {
  return;
}

code of the 1st section

  • Next, click on the plus (+) icon then select HTML to add another section.

adding 2nd section in the apps script

  • Rename the section with “modifypassword”.

new section in the apps script

  • Copy the below code and paste in the “modifypassword” section like previously.
<html>
<form id='myForm' style="font-family:Georgia;">
<h1>Change Password</h1>
<table>
<tr><td>Previous Password&nbsp;</td><td><input name='oldpassword' type='password' value="" ></td></tr>
<tr><td>New Password&nbsp;</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="Done" value="Done"></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 Password. Please retry");
   }
}
function showWorking() {
  document.getElementById('submitbutt').style.display="none";
  document.getElementById('submitloader').style.display="block";
}
</script>
</html>

codes in the 2nd section in the apps script

  • Again, click on the plus (+) icon then select HTML and add another section and rename that with “encryptbypassword”. After that, paste the following code in that section.
<html>
<form id='myForm' style="font-family:Georgia;">
<table>
<tr><td><h2>Enter Password to Encrypt</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("Wrong Password. Try Again");
   }
}
function showWorking() {
  document.getElementById('submitbutt').style.display="none";
  document.getElementById('submitloader').style.display="block";
}
</script>
</html>

codes in the 3rd section in the apps script menu

  • Now, click on the + icon then select HTML Rename the section with “decryptbypassword”. Following that, paste the below formula in that section then.
<html>
<form id='myForm' style="font-family:Georgia;">
<table>
<tr><td><h2>Enter Password to Decrypt</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("Wrong Password. Try Again with the Correct One");
   }
}
function showWorking() {
  document.getElementById('submitbutt').style.display="none";
  document.getElementById('submitloader').style.display="block";
}
</script>
</html>

codes in the 4th section in the apps script menu

  • At this point, select Code.gs then at the top of the toolbar there, select Run.

Executing the codes

  • Authorization will be required to follow the given codes. The following window will appear on your screen. Select Review permissions.

Beginning of the authorization process.

  • You will be asked for an email. Here, I have selected mine.

selecting email address of the owner

  • After selecting the email address, a safety page will appear on your screen. Select Advance from there.

safety page appeared

  • Next, select “Go to Untitled project (unsafe)” from the newly appeared window.

going through the unsafe app process

  • Following this, press on Allow.

pressing Allow to progress forward

  • Something as follows will appear in the Execution bar that is in the below of the Apps Script tab.

successful execution message


Step 3: Set Password

  • At this moment, go to your spreadsheet again, and you will see a “Password Protect Sheets” menu at your toolbar which is absolutely new right?

password protect sheets menu created at the toolbar

  • Select that menu then click on “Encrypt Data”.

encrypting data

  • Create a new password as you wish. The password I have created here is “123456”.

creating a new password

  • Your whole dataset is now encrypted. All the texts in your dataset are now converted into a code as follows.

encrypted data

  • Now, to decrypt those codes, select Decrypt Data from the Password Protect Sheets at the toolbar.

decrypting data

  • Enter password “123456” then click Submit.

entering password to decrypt data

  • And the dataset will be back to its previous form. The most amazing part of this whole process is that only the owner of the spreadsheet can decrypt the encrypted data.

normal form of the dataset after decrypting


How to Change Password of Google Sheets

Assume, you have told someone the password of your encrypted data. Now you want to change it.

Steps:

  • First, simply go to the Password Protect Sheets menu again, then select Modify Password.

changing password by using the modify password option from the newly created menu at the toolbar

  • Type down the old password “123456” in the Previous Password bar and then input the new password in the New Password bar as you want then click Done and you are all done as well. I made “2580” the new password here.

changing password by giving input old and new password


How to Protect Google Sheets Without Password

There are features in Google Sheets that allow you to protect your spreadsheet without setting up a password protection. The built-in Protect sheets and ranges will help us with that.


1. Restricting Entire Sheet

You can restrict an entire sheet for all users as well as except for some specific users.


1.1 For All Users

  • If you want to restrict an entire sheet for all then first, go to the Data menu at the toolbar.
  • Then select Protect sheets and ranges option.

using protected sheets and ranges feature in google sheets

  • Next, a sidebar titled Protected sheets & ranges will appear on the right side of your dataset.
  • Select “Add a sheet or range” from there.

using protected sheets and ranges feature in google sheets

  • Type down something as you want in the Enter a Description.
  • Select Sheet and then select the sheet you want to restrict from the drop down menu. Here the title of the sheet I have restricted is “Restricting for All Users”.
  • After that, click on Set permissions.

setting permissions

  • Following this, a new window titled Range editing permissions will appear on your screen. The option Restrict who can edit this range here may already been marked and Only you option from the drop down menu may already been selected by default. If not then do that yourself.
  • Finally, select Done and you are all set.

range editing window

  • Now, if anyone (except the owner) has access to the entire spreadsheet, that person can’t edit that particular sheet.

1.2 Except Specific Users

We can also choose the users at our own will who can have the editing access to a specific sheet.

Steps:

  • Activate the Protected sheets & ranges sidebar, enter a description, select Sheet, and choose the sheet you want to restrict except for some specific users.
  • Then click on Set permissions like previously. The Range editing permissions will appear on your screen.
  • Select Custom from the drop down menu.

selecting custom option from the range editing permissions drop down menu

  • Now, choose whoever you want to have access to that sheet. I have selected my mentor by entering his email in the Add editors Lastly, click on Done and the sheet is restricted for all the users except those you have selected.

selecting editors


2. Locking Specific Cells

We can also lock some specific range of cells instead of restricting the whole sheet. Only the owner of that sheet then can edit that range of cells. We will also use the Protect sheets and ranges feature for that. Presume, in the following dataset, you want to lock the Cell range E5:E13.

dataset for Locking Specific Cells in google sheets

Steps:

  • First, select Cell range E5:E13.
  • Then at the toolbar, go to Data then select the Protect sheets and ranges.

using protected sheets and ranges feature in google sheets

  • Or you can simply right-click on the selected Cell range E5:E13.
  • Then go to View more cell actions and select the Protect range.

using protected sheets and ranges feature by right clicking in google sheets

  • Protected sheets & ranges sidebar will appear. Enter a description then click on Set permissions.

setting permissions

  • Select Only you from the Restrict who can edit this range drop down menu. Finally, click on Done.

customizing range editing window


3. Setting Warning While Editing Cells

You can give access to multiple people and set warnings to specific cell ranges to them while they start editing those cells.

Steps:

  • First, select Cell range E5:E13 and simply activate the Protected sheets & ranges sidebar like previously.
  • Type down “Warning” in the Enter a description box and select Set permissions.

using protected sheets & ranges feature to set warning to a specific range of cells

  • After that, Mark the Show a warning when editing this range from the Range editing permissions.
  • Lastly, click on Done.

marking first section in the range editing permissions window


4. Hiding Sheets

You can even hide a particular sheet in your spreadsheet to keep it private and then unhide them from the toolbar. Suppose, In the following spreadsheet, we want to hide the sheet titled Hiding Sheets.

dataset of hiding sheets in google sheets

Steps:

  • First, click on the Menu ribbon as shown below.
  • Then select Hide Sheet.

hiding sheets

  • Following this, the sheet is hidden now and the following message will appear on your dataset.

message of "hiding sheets is done"

  • To view the hidden sheet, simply go to View at the toolbar then select Hidden sheets.
  • Then click on Show Hidden Sheets.

unhiding the hidden sheets

  • Thereafter, your sheet will no longer be hidden.

unhiding the hidden sheets


Conclusion

This article contains the easiest method to password protect Google Sheets. Hope this helps with your task. Visit our site officewheel.com to find more related articles that will help you to become more efficient and expert in Google Sheets.

Adrey

Adrey

Hello! This is Raiyan Zaman Adrey, a fresh graduate in BSc in Civil Engineering from Bangladesh University of Engineering and Technology. From my high school I have been using Excel in which I was always interested and had fun and this led me to do more research on Excel and Google Sheets as both are kinda similar platform. I have always been enthusiastic, self-motivated, reliable and hard working person and for a long time, I am trying to improve myself more and more so that I can face any challenging situation and adapt myself to any environment.

We will be happy to hear your thoughts

Leave a reply

OfficeWheel
Logo