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.

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.

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.

Step 1: Open Apps Script

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

• A new tab will be opened in your browser.

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;
}

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

• Rename the section with “modifypassword”.

• 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>

• 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>

• 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>

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

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

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

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

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

• Following this, press on Allow.

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

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?

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

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

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

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

• Enter password “123456” then click Submit.

• 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.

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.

• 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.

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.

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

• 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.

• 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.

• 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.

• 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.

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.

Steps:

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

• 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.

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

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

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.

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

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.

Steps:

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

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

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

• Thereafter, your sheet will no longer be hidden.

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.