When Google introduced sheets, the idea was to have a spreadsheet tool that people could share in real-time.

Not all of the features of Excel are available with Google Sheets. However, with what it has, google sheets are still handy.

Since they developed it to collaborate in real-time, data security comes into the picture. And why is it so much fuss, you will ask?

The sheets can be filled with inaccurate data, either intentionally or accidentally.

Therefore, the best possible way to add a layer of security under the google sheets is by locking the cells.

This way, you ensure that only the right person is making changes in the data.

In this article, I will walk you through different methods of locking cells in Google Sheets.

Are you excited to go? Because we are!

But first, make sure you grab the example workbook to follow along by using the button above.

Lock Specific Cells or Ranges

It is possible to lock specific cells or a range of cells inside google sheets.

If you want to lock particular cells or capacity of cells in Google Sheets, follow the steps below.

Navigate towards the sheet you want to add cell or range locks to.

I have an file named Lock Cells opened inside Google sheets with some data in it. Select the entire range of cells you would like to lock. In this case, it should be B2:C7

With the range of cells selected, right-click on the selection, and you can see various options popping up as above. Select the Protect range option out of those.

1. The Protected sheets & ranges option will pop up on the extreme right-hand side of the sheet. There are two options within it. As of now, we will be locking the selected range only.
   • The Range option is by default selected with range B2:C7 to lock those cells.
   • The Sheet option allows you to protect the entire google sheet.
2. Therefore, keep the Range option selected.
3. You can see the automatically selected range of cells B2:C7 inside the Select data range option. It is the range to be locked for editing.
4. Click on the Set permissions button to proceed further

Note: There is an Enter a description comment box. Where you could add comments of your own regarding the cells, you are trying to lock.

Once you click on the Set permissions button, a new wizard named Range editing permissions will open up, as shown above.

1. By default, Only you can edit this range of cells option is selected, and we will keep it as is.
2. You have to click Done and Tada! The cells B2:C7 are locked for editing, and no other user can edit those apart from you.

Suppose another user tries to edit any of the cells from range B2:C7. In that case, they will get a message pop-up as Shown above: asking them to contact the spreadsheet owner to remove the protection.

Well, this is one of the ways of locking a range of cells in Google Sheet.

Lock Entire Row or Column

What if you want to lock the entire column or row for editing?

You might have guessed it by now. It is possible!

Let us lock entire column D for editing by other users. To do so, firstly, you need to select the whole column D as above.

Keeping column D selected, right-click and select Protect range option.

Under the Protected sheets and ranges, you can see the entire column range is added to be locked. Hit the Set permission button to move further.

Set the option as Only you to Restrict who can edit this range under the Range editing permissions, hit the Done button, and you are through.

You have successfully locked the entire column D for editing, and no other user can edit it now.

Note: An interesting fact here! The google sheet, by default, shows the first thousand rows. And allow the option to add rows below those. However, even if you add new rows in the sheet, all of column D will still be locked for editing by other users.

On similar lines, can you try and lock an entire row from google sheet? I will keep it as an exercise for you. If you have come this far, you should ideally be able to lock the entire row successfully (any row from google sheets).

Give Edit Permission on Locked Cells to Select Users

You have seen how to restrict users from editing the cells inside google sheet. However, there might be situations when you want to allow specific users to edit the sheets.

For example, you are a team of five data analysts for a big firm in the finance team. You want to be on holiday, and another team member needs to fill the data up inside the sheet. In this situation, the team members will not be able to, as they don’t have edit permission.

However, you can easily allow them permission to edit the sheet. Follow the steps below to enable another user to edit the google sheet.

Select the entire range for which you wanted to grant permission. In this case, it will be B2:C7. On the right-hand side, under Protected sheets & ranges, you will see that this range is already locked for editing.

Click on that ‘Lock Cells!B2:C7’, and you will be able to see the Change permissions tab. Click on that tab.

Once you click on the Change permissions tab, a new pop-up window will open named Range editing permissions.

Under this, by default, Only you can edit the cells; if you want to add another user who could edit the cells, click on the Only me dropdown.

In that dropdown, you will see the Custom option. Click on that. You can use this option to customize the users who can edit the sheet.

Under the Add editors: input box, add the email id of the person you would like to permit to edit this sheet.

Once you are ok with the amendments you made, hit the Done button that allows a new user to edit this google sheet.

Show Pop Up Warning of Locked Cells

There might be cases when you don’t want to lock cells but warn the user to edit the cells. In this way, you are not restricting someone by editing this sheet but just giving them a warning that editing is prohibited for this worksheet.

To achieve this result, go to the Data tab, navigate towards Protect range option. It will lead you towards the Protected sheets & ranges window.

Inside the Protected sheets and ranges, make sure to mention the range of cells adequately. Hit the Set permissions button to be able to add protection to the fields.

 Once you hit that, the Range editing permissions wizard will open up as shown above.

In the Range editing permissions wizard.

1. You have to select the Show a warning when editing this range radio button.
2. Hit the Done button to save the changes.

After you hit the Done button, every time a user (even if it is you) tries to make changes inside those ranges, they will get a warning pop-up, as shown in the screenshot above.

You can click on the OK button, and again if you try to edit, you will see the same message.

You can check the Don’t show this again for 5 minutes check box to eliminate this. By clicking this checkbox, you will get rid of this warning pop-up for the next five minutes.

Alert! This method of protecting edit permissions for cell ranges is less secure. The user can still edit the cells irrespective of the warning message.

Lock an Entire Sheet

So far, you have been looking into locking some portion of the entire sheet. It may be a range of cells, a column, or an entire row.

But what if we want to lock the whole sheet itself for editing by a user? Well, again! It is possible!

If you wish to protect an entire sheet, you again have to go to the Data tab and then the Protect range option.

1. The Protected sheets & ranges window will open up at the extreme right of your sheet.
2. Once you are there in that Protected sheets & ranges window, you don’t go for Range this time. Instead, you click on the Sheet option to be able to lock the entire sheet for editing.
3. There, you can select the sheet you want to lock from the dropdown menu.
4. Click on the Set permissions button.

Again, the Range editing permission pop-up window will appear on the screen.  

There, you can restrict the editing permission to Only you and lock the entire sheet named Lock Cells. The screenshot above will give you a better realization:

Don’t forget to hit the Done button after you restrict the range editing permissions.

A point to note here: You can always set the Custom option and add people who will have access to edit the entire sheet.

Lock an Entire Sheet Except for Selected Cells or Ranges

While locking a sheet, we are making all of it non-editable to any user.

What if the user is supposed to update some specific range values? If you lock the whole sheet, he will not be able to update that table. Isn’t it?

For such cases, lock the entire sheet except for some selected cells. This way, you will allow a user to change chosen fields only, and the rest of the sheet will be clean and junk-free.

1. Navigate towards the Data tab then Protected sheets & ranges.  
2. Go to the Sheets section.
3. Make sure you select the sheet to protect through the dropdown.
4. Check the Except certain cells check box.

1. Tick the Except certain cells checkbox.
2. you will see an option to add a data range that can be made available for editing. I will add B2:C7 as a range there.
3. Click on the Set permissions button, and you can add the user permissions either for yourself or for someone else.

Note: You can add multiple cell ranges through Add another range option.

Unlock a Locked Cell or Range

When you lock cells, there are ample chances that, at some point, you would like to remove the locks.

Follow the steps below to remove the lock from a given range of cells. It is quite a simple process, though.

Navigate towards the Data tab and select the Protected sheets and ranges option.

The Protect sheets & ranges window will appear at the extreme right of the sheet. You can see the previously defined rules for cell lock. Click on that rule to open it.

Once you click on that rule, you can see that there is a Delete icon. You can delete this rule by clicking on that icon. The cells are now unlocked.

There will be a pop-up box that will ask you for your confirmation. You have to hit the Remove button to remove the rule and unlock the cells permanently.

This way is of unlocking the cells under google sheets.

Lock Cells, Ranges, and Sheets with a Password

One question often arises while working with google sheets and locking/unlocking the sheets and ranges discussed here. Can we lock the google sheets or their range of cells with any password?

Google Sheets doesn’t provide any such tool that can allow you to password protect your sheet or range of cells within it. Google sheets already has sharing security. A workbook can only be seen by someone you share it with. And that itself is security.  Putting a password to a file you have sole rights to share and allow edit access doesn’t make sense.

However, some third-party tools provide the option to encrypt your google sheet. Those are not the ideal ways of encrypting your google sheets, though. I would not recommend those to you.

Locking and Unlocking Programmatically with Scripts

Is it possible to automate the locking and unlocking of google sheets?

Can you write a script of your own that can automate this task for you? Of course, a Yes!

The Google Apps script is an excellent way of automating repetitive tasks. You will be using it to achieve this result. It allows you to create reliable, fast, and easily compatible google workspace solutions.

The JavaScript language is a base for scripting; its integration is with Google apps such as Drive, Gmail, Sheets, Docs, etc.

There is also a benefit of using it. You don’t need to install anything! Entire scriptwriting is on a web browser-based editor. That can surely make you interested in looking at it. Right? Let’s explore it then!

Inside your google sheet, go to the Tools tab, then there is an option named Script editor. Click on it.

Once you click on Script editor, a new window will open in your chrome browser.

The Google Apps Script interface is as shown in the image above. It has, by default, myFunction() popping up. Anything we develop inside the Google Apps Script is a function.

We will first rename the project as Lock Cells from the Untitled project.  

Rename the function as lockCells() from myFunction().

You have to write a code that can lock the specific range of cells from your sheet. The code starts here in Google Apps Script with a ‘let’ clause. I have a small code of my own, as shown above, to lock the cells B2:C7.

function lockCells() {
  let mySheet = SpreadsheetApp.getActiveSpreadsheet();
  let range = mySheet.getRange('Lock Cells!B2:C7');
  let protect = range.protect().setDescription('This range is protected and you do not have permission to edit it');
}  

• The first line of code returns with the active spreadsheet (returns null if there is no active worksheet). The SpreadsheetApp.getActiveSpreadsheet() method allows us to get the active worksheet. Under the mySheet variable, we store the result.
• The getRange() method applied on mySheet allows you to specify a specific range. It will be the range you want to lock. Store the result under the range variable.
• The last line of the code uses range.protect() method. It locks the range specified in the second line of code for editing. Also, to set up a warning message for the user trying to access this sheet, the setDescription() method is used. Users will get a warning message with the text shown in that method.

What to do now? You can see the Run button on the ribbon at the upper side of the script interface. Hit it to run this code. It will lock the range of cells B2:C7 for editing.

Since you are with google sheets, Google Apps Script will need your permission to run this code and make changes to the sheet. It pops up the Authorization required window as shown above. You have to hit the Review permissions button to grant permission for execution.

The system will open up a new tab and ask you to sign in with your Google account.

Grant the permission by hitting the Allow button on the next screen.

Once you grant permission for this code to run on your active spreadsheet, you will see an execution log, as shown above. It shows the execution progress, such as when execution is started, when it is completed, or any errors.

OK! since the execution is complete without an error, we can say that code is error-free. Automation is successful. And cells are locked with the script we wrote.

Check it under Google Sheets. You can now see a protected range B2:C7.

Since I have been the author of this code, you can see that I can edit those ranges. However, what if another user tries to access these ranges?

The other users trying to edit this sheet will get an error message as shown above.

Using this way, you can lock cells with a script.

function unlockCells() {
  let xlSheet = SpreadsheetApp.getActiveSpreadsheet();
  let seeProtect = xlSheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
for(let i = 0; i < seeProtect.length; i++) {
  let removeProtect = seeProtect[i]
  if(removeProtect.canEdit()){
    removeProtect.remove();
  }
}
}

To unlock a protected google sheet with a script, you have to use the above script.

• We have defined a new function unlockCells().
• We get the active spreadsheet info using SpreadsheetApp.getActiveSpreadsheet() method. Results are stored in the xlSheet variable.
• To see the protected cells, we use the getProtections() method with the SpreadsheetApp method ProtectionType.RANGE as an input. The results are stored inside the seeProtect variable.
• Finally, we are using the for loop to remove the protection of each protected range. We are using the remove() method to remove the protection from locked cells.

Run this code to unlock the protected ranges from this sheet.

The execution log shows that the code is error-free. Execution is completed.

Suppose you navigate towards the google sheet. In that case, you can see that no ranges are protected under the Protected ranges & sheets window.

These are a few ways in which you can protect your ranges from being edited by another user.

Keyboard Shortcuts for Locking Cells

There are no keyboard shortcuts to lock cells, ranges, or sheets.

However, there is a keyboard shortcut to navigate towards the Protected sheets & ranges window.

Select the range you want to lock on your google sheet, and hit the keyboard shortcut Alt + Shift + D + O to navigate directly towards the range protection tab.

Conclusions

While working on google sheets, it is often a case that users with whom you share the file can edit the sheets.

To prevent this mishap, locking your google sheets or some range of cells that contain sensitive information from editing is the best way.

This way, you add an extra layer of security, keeping your sheets clean and error-free. Moreover, if your sheets have the formulae, those can be protected as well.