Do you want to protect a sheet from accidental changes? In this article, I will show you how to lock your sheet tabs in Google Sheets. There are many situations where you may want to protect certain ranges or an entire sheet in your spreadsheet. After spending a lot of time organizing and cleaning up a set of data, you may not want others to modify your work. If other people have access to the document, you may want to lock entire sheets containing important information.
In this article, you will learn how to lock a sheet using the menu bar, the right-click menu, a keyboard shortcut, and a custom script. You will also learn how to lock multiple sheets at once.
Get your copy of the example workbook used in this article and follow along!
Locking a Sheet Tab
There are several ways to lock a sheet.
Locking a Sheet Tab with the Sheet Menu
Right-click anywhere on the sheet tab you want to lock. This will open the “Protected sheets and ranges” menu where you can lock the sheet.
Locking a Sheet Tab with the Right-Click Menu
Right-click anywhere on the worksheet of the tab you want to lock. This will open the “Protected sheets and ranges” menu where you can lock the sheet.
Locking a Sheet Tab with the Data Menu
Go to the “Data” menu and choose the “Protect sheets and ranges” option. This will open the “Protected sheets and ranges” menu where you can lock the sheet.
Locking a Sheet Tab with a Keyboard Shortcut
Press Alt, H, O, P on your keyboard to open the “Protected sheets and ranges” menu.
“Protected sheets and ranges” Menu
When you follow any of the above methods, you will open the “Protected sheets and ranges” dialog box on the right side of the spreadsheet. From there, you can protect a selected sheet or range.
Follow these steps to protect a sheet:
- Click on the “Add a sheet or range” menu.
- Select the “Sheet” tab and write a description in the text box.
Adding a descriptive name can help you remember why you are locking the sheet.
- Click on the box that displays the sheet name and select the sheet you want to lock.
- Click on the “Set Permissions” button.
The “Set Permissions” dialog box appears when you click on “Set Permissions”. You can choose what happens when other users try to edit the locked sheet.
- Select the radio button “Restrict who can edit this range” and choose “You only” from the dropdown list so that only you can edit the locked sheet.
You can also choose the “Custom” option. This allows you to add the full names or email addresses of other users who can edit the locked sheet.
The last option you can select in the “Restrict who can edit this range” setting is the “Copy permissions from another range” option. This option is effective when you already have restrictions in place on other sheets.
“Copy permissions from another range” will list the ranges or sheets with restriction settings, and you can select the one you want to use.
You may not want to completely block users from editing a sheet. In cases like this, you can use the “Show a warning when editing this range” option.
The user is warned that the sheet contains crucial information and that they should be careful before making any changes.
Locking a Sheet Tab Except for Certain Cells
It is possible to protect a sheet while leaving certain cells unprotected.
For example, in this illustration, some names in the “Name” column are missing. We can lock the sheet but leave the empty cells unlocked, and this is very easy to do.
Enable the “Except certain cells” option by checking the box next to it.
Click on the grid icon and select the range you do not want to lock. Click the “OK” button when you’re done.
Click on “Add another range” to select another range.
You can use the “X” to remove any of the unprotected ranges. Then click the “Done” button to apply the new permission settings.
Now, only these empty cells can be edited by anyone.
Locking Multiple Sheet Tabs at Once with the Sheets Manager Add-on
If your spreadsheet has multiple sheets and you want to lock several of them, doing it one by one can be tedious.
There is no native solution in Google Sheets to lock multiple sheets at once. However, there is an add-on you can download from the Google Workspace Marketplace called “Sheets Manager” that allows you to lock multiple sheets at once.
To download the add-on, go to the “Extensions” menu and select “Add-ons,” then click on “Get add-ons.” This takes you to the Google Workspace Marketplace.
In the Workspace Marketplace, search for “Sheets Manager” by Ablebits. Click on “Install.” You will be asked to grant permission.
Exit the Workspace Marketplace. Go back to the “Extensions” menu. You will find the “Sheets Manager” add-on in the dropdown menu. Select it and click “Start” to use it.
When you start the add-on, a sidebar appears on the right side of the spreadsheet. From there, you can lock multiple sheets.
The add-on lists the names of all visible sheets. To lock multiple sheets, hold down the Ctrl key and select multiple sheets.
While the sheets you want to lock are selected, click on the padlock icon and select your locking preferences. Here, the “Restrict editing” option is selected.
You can also access this option by right-clicking on the selected sheets.
After a few seconds, the three selected sheets are locked.
The downside of using this add-on is that you cannot exclude certain cells when protecting a sheet. With the add-on, all cells in the sheet are locked.
The “Sheets Manager” add-on is a free tool, but you can subscribe to a paid version to get the most out of it.
Locking Sheet Tabs with an Apps Script
You can also use an Apps Script to protect your spreadsheet from unauthorized changes.
Go to the “Extensions” menu and select “Apps Script” to open the script editor.
function sheetsProtect() {
var ss = SpreadsheetApp.getActive();
var sheets = ss.getSheets();
var editorSheet = ss.getSheetByName('Editors');
var editorList = editorSheet.getRange(1, 1, 1, 1).getValues();
for(i = 0; i < sheets.length; i++) {
sheets[i].protect().setDescription('Lock with Apps Script').addEditors(editorList);
}
}
function onOpen(e) {
SpreadsheetApp.getUi()
.createMenu("Lock Sheet")
.addItem('Protect Sheet', 'sheetsProtect')
.addToUi();
}Copy and paste the above code into the Apps Script editor. Click the “Save” button and then the “Run” button, granting the necessary permissions.
The script creates a custom menu “Lock Sheet” when you refresh the sheet. When you click on it, you will find the submenu “Protect Sheet”.
When you click on “Protect Sheet”, all sheets are protected at once, while granting editing permission to a specified user whose email address is listed in the first cell of the “Editors” sheet.
Conclusion
When working on a shared spreadsheet, security is generally very important. While locking a range or a sheet is not a high-level security measure, it allows you to control the level of interaction other users have with your dataset.
Whether you use the built-in protection option available in the spreadsheet, the “Sheets Manager” add-on, or the custom script “Lock Sheets”, you can implement different measures that limit what other users can do in your spreadsheet.
Do you lock your sheets? Do you have any other custom solutions for locking a sheet? Let me know in the comments!
