Skip to Content

How to Password Protect Google Sheet (FREE Example Template) – 2024

Posted on Last updated:

Google Sheets offers powerful tools to organize and share data. For added safety, users can password-protect Google Sheets while sharing. They can do it using the Apps Script code discussed in this article.

Adding a password to your Google Sheets ensures that confidential data is safe and accessible only to authorized users.

This is a quick guide to password-protecting Google sheet data. It includes steps, benefits, and precautions for password protection. So, read the article till the end and make sure you don’t miss anything.

Benefits of Google Sheets Password Protection

Google Sheets data can be easily shared with anyone on the internet. This flexibility has its own benefits and drawbacks.

Simply put, owners may risk unwanted access to confidential data by being careless while sharing the spreadsheets.

The standard solution for securing data is password protection.

Here are a few of the benefits of Google Sheets password protection,

  • Privacy – By using password-protected Google Sheets, you authorize specific people to access and edit it. This is the perfect way to improve the privacy of your confidential data.
  • Access Control – Google Sheets allows owners to assign different levels of access to the shared users. You can choose if the shared users can view, edit, or comment on your document.
  • Ease of Use – Previously, owners of the spreadsheet used to copy and paste the confidential data in a separate Google Sheet without knowing that there are built-in features to protect data within the same file. This unintentionally forced them to save and keep track of multiple files.

Apart from the above, it also allows users to save time and focus on the things that matter most.

How to Password Protect Google Sheet

Users can enhance the security of the Google Sheets tab within a matter of minutes. The steps are pretty straightforward.

Consider the following example where we have employee data, including their annual salary, in the last column. Being the HR manager of a reputed organization, we are asked to keep this data confidential.

How to Password Protect Google Sheet - Quickest Method

STEP #1 – Use the Apps Script to create a Custom Menu

Before we jump into the steps, here’s the Apps Script code.

<pre class="wp-block-syntaxhighlighter-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()+"&amp;gid="+id);
  SpreadsheetApp.getActiveSpreadsheet().getSheets()&#x5B;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&lt;activesheet.getSheets().length; k++) {
    var ss = activesheet.getSheets()&#x5B;k];
    var range = ss.getDataRange();
    var vals = range.getValues();
    //var actvals=&#x5B;];
  
  
    for (var i=2;i&lt;vals.length; i++) {
      for (var j=0; j&lt;vals&#x5B;i].length; j++) {
        if (vals&#x5B;i]&#x5B;j] != "") {
          if (ss.getRange(i+1, j+1, 1, 1).getFormula() == "") {
            vals&#x5B;i]&#x5B;j]=encrypt(vals&#x5B;i]&#x5B;j], 1);
            ss.getRange(i+1, j+1, 1, 1).setValue(vals&#x5B;i]&#x5B;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&lt;activesheet.getSheets().length; k++) {
    var ss = activesheet.getSheets()&#x5B;k];
    var range = ss.getDataRange();
    var vals = range.getValues();
    //var actvals=&#x5B;];
  
  
    for (var i=2;i&lt;vals.length; i++) {
      for (var j=0; j&lt;vals&#x5B;i].length; j++) {
        if (vals&#x5B;i]&#x5B;j] != "") {
          if (ss.getRange(i+1, j+1, 1, 1).getFormula() == "") {
            vals&#x5B;i]&#x5B;j]=decrypt(vals&#x5B;i]&#x5B;j], 1);
            ss.getRange(i+1, j+1, 1, 1).setValue(vals&#x5B;i]&#x5B;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&lt;aa.length; j++) {
    a=text.charCodeAt(j);
    if(j==0 &amp;&amp; String.fromCharCode(a)==6) {
      //= at start of cell will convert value to formula.
      endResult += String.fromCharCode(a);
      continue;
    }
    for(var i = 0; i &lt; key; i++) {
      if(  !(a >= 123 || a &lt; 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&lt;aa.length; j++) {
    a=text.charCodeAt(j);
    if(j==0 &amp;&amp; String.fromCharCode(a)==6) {
      //= at start of cell will convert value to formula.
      endResult += String.fromCharCode(a);
      continue;
    }
    for(var i = 0; i &lt; key; i++) {
      if(  !(a >= 123 || a &lt; 31)) {
        if(a-1 != 31) {
          a -= 1;
        }
        else {
          a = 122;
        }
      }
      else {
        break;
      }
    }
    endResult += String.fromCharCode(a);
  }
   return endResult;
}

function getHtml(msg,butt) {
  html='&lt;html>'+
  '&lt;head>'+
  '&lt;/head>'+
    '&lt;body>'+
    '&lt;div style="width:100%; text-align:center; font-family:Georgia;">'+
      '&lt;h2 style="font-size:40px;">&lt;i>Input You password.&lt;/i>&lt;/h2>'+
        '&lt;form type="submit" action="'+ScriptApp.getService().getUrl()+'" method="post" style="font-size:22px;">'+
        '&lt;label>'+msg+'&lt;/label>'+
        '&lt;input type="password" name="password" value="" style="padding:5px; width:300px;" />'+
        '&lt;input type="submit" name="submit" value="'+butt+'" style="padding:5px;" />'+
      '&lt;/form>'+
    '&lt;/div>'+
      '&lt;/body>'+
    '&lt;/html>';
  return html;
}

function doGet() {
  var prop = PropertiesService.getUserProperties();
  var password='';
  var html='';
  if (prop.getProperty("sheetencrypted-password-"+GLOBALID) == null) {
    html='&lt;html>&lt;body>You have not set any password&lt;/body>&lt;/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='&lt;html>&lt;body>You have not set any password&lt;/body>&lt;/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('&lt;span style="color:red;">Incorrect password. Please retry!!!&lt;/span>&lt;br/>', butt);
      return HtmlService.createHtmlOutput(html);
    }
    else {
      if(e.parameter.submit == 'Encrypt') {
        EnCodeSheet(true);
        html=getHtml('&lt;span style="color:green;">Encoded Successfully!! &lt;a href="'+docurl+'">Click here to go back.&lt;/a>&lt;/span>&lt;br/>', 'Decrypt');
      }
      else {
        DeCodeSheet(true);
        html=getHtml('&lt;span style="color:green;">Decoded Successfully!!  &lt;a href="'+docurl+'">Click here to go back.&lt;/a>&lt;/span>&lt;br/>', 'Encrypt');
      }
      return HtmlService.createHtmlOutput(html);
    }
  } 
}


function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = &#x5B; {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()&#x5B;0].getRange('C1').setValue('=HYPERLINK("'+ScriptApp.getService().getUrl()+'", "http://script.google.com/...")');
}

function onInstall() {
  onOpen();
}

function Initialize() {
  return;
}

</pre>

Make sure to copy the above code on your clipboard using the keyboard shortcut “CTRL +C“.

Now, let’s look at the steps to create a custom menu for password-protecting the Google Sheets,

  • Open the Google Sheets
  • Hover to the main menu
  • Click the “Extensions” tab
  • Now, select “Apps script” from the popup
  • A new tab will open on your browser, as shown below,

    (Make sure you are in the Code.js file as highlighted in the above screenshot)
  • Erase the existing code by selecting all the code and then pressing the “Backspace” on your keyboard
  • Paste the code we copied above
  • Next, you need to click the “Deploy” button on the upper-rightmost corner of the screen
  • Choose “New deployment” from the popup
  • A new popup will appear on your screen
  • Click the Gear icon as shown below
  • Select “Web app” from the list
  • Now, add the name or description in the empty box as shown in the following image
  • Press the “Deploy” button

As we are deploying the code for the first time, the following popup is displayed on the screen.

It is asking us to authorize the code and Apps Script to make changes to your current Google Sheets.

Here’s how you can proceed further,

  • Click the “Authorize access” button
  • Choose your Google account

    (Make sure to choose the same Google account used to create the spreadsheet)
  • The following window will open on your browser
  • Click the link named “Advanced“, as shown below
  • Now, press the “Go to ProtectedSheet (unsafe)” button as shown below
  • Grant the following permissions to the Apps script code by clicking the “Allow” button as shown in the following image
  • The authorization window will close, and a new popup will appear on the screen
  • Click the “Done” button to close the popup

Finally, to add the custom menu to the Google Sheets, you need to run the code by clicking the “Run” button from the toolbar section of the Apps Script window. Refer to the following image.

Use the following link to download the Google Sheet template where we have used the Apps Script code to create a new Custom Menu. It is intended for those who don’t wish to spend time on the ins and outs of Apps Script code.

Click Here To Download!

The template is totally free to download. You can customize it as per your preference.

STEP #2 – Protect the Google Sheets using the newly designed Custom Menu

Once the deployment is done and the code is run, you will see the following option in the main menu after going back to the Google Sheets.

If the tab is not visible then make sure to refresh the page.

There are several options, as follows:

  • Initialize – It lets you run the code without opening the Apps Script editor in the new tab and pressing the Run button every time.
  • Encrypt File – You can add the password to your file using this option.
  • Decrypt File – In contrast to the previous option, it lets you remove the password from your Google Sheets.
  • Change Password – As from the name itself, this one is used to change the password assigned to your Google Sheets using the Encrypt option.
  • Get Web Page URL – It inserts a link in your Google Sheet that can be used to go to the Apps Script editor to view and edit the code.

Now, let us have a quick look at the steps to add the password to your Google Sheets.

  • Click the “Protect File” tab from the main menu
  • Select “Encrypt File” from the list
  • A new popup will appear
  • Enter the password for your Google Sheets
  • You will see the following notification on your screen

Once the code is executed successfully, the above notice should change to “Finished running script.”

After this step, the existing data will be encrypted and look as follows.

Now, whenever you wish to decrypt (view or restore) the data, all you need to do is go to the “Protect File” tab from the main menu and choose the “Decrypt File” option.

A popup will appear on the screen asking for the password.

Once you enter the password, the data will be accessible to you.

How to restrict users from editing the password-protected Google Sheets

In the above section, we learned to password-protect Google Sheets. However, the users will be able to view as well as edit the file after entering the password.

If you wish to restrict users from editing the Google Sheets, then you can use the “Protect sheet” tool.

Here are the steps:

  • Open the Google Sheet
  • Hover to the main menu
  • Click the “Data” tab
  • Select the “Protect sheets and ranges” from the list
  • A new popup will be displayed on the right-hand side of the Screen
  • Click the link named “Add a sheet or range“, as shown below
  • You can choose to restrict the edit access either to a cell range or the entire sheet. For now, let us go ahead with the entire sheet by clicking the tab as shown in the following image,
  • Select the desired sheet by clicking the dropdown as follows
  • Press the “Set permissions” button
  • A new popup will appear on your screen
  • Make sure the radio button before the “Restrict who can edit this range” option is ticked
  • Select the “Only you” option from the dropdown, as shown below
  • Press the “Done” button

After following the above steps, you will see a padlock icon before the name of the sheet. Refer to the following image.

Now, even after entering the password to access your Google Sheets, the user can only view and not edit it.

To Summarize: How to Password Protect Google Sheet

There are a range of third-party add-ons available for protecting your Google Sheets data using passwords. But you need to pay one-time or monthly charges for them.

On the other hand, the Apps Script code we have provided to you is FREE.

It allows you to extend the Google Sheets functionality to include the password management tool.

Comment below if you are stuck somewhere, following the steps mentioned in this article.

You can also make a copy of the Password Protected Google Sheets Template we created. It’s FREE!

For more such templates and tips to use Google Sheets like a pro, please explore our blog section.

AK

Tuesday 13th of February 2024

If I use this method, will people I share the spreadsheet with have to enable the same script, and go through the same window with "google hasn't verified this app", in order to submit the password and open the document? Can you please describe what the experience will look like for the user who the file (and password) is shared with?