Have you ever needed to unlock a protected sheet? In this article, I’ll show you how to remove sheet protection in Google Sheets.
Locking a sheet allows you to prevent users from making changes to your spreadsheet. When you lock a sheet, it can apply to a range or the entire sheet.
If you want to lift this restriction and allow others to have editing permissions, you need to unlock the sheet tab.
In this article, you’ll learn how to easily unlock your sheets using the Data menu, the Sheet Manager add-on, and a custom application script.
Get your copy of the sample workbook and follow along!
Unlocking a Sheet from the Data Menu
You can unlock a sheet from the Data menu.
Select the Data menu and click on Protect sheets and ranges.
A Protect sheets and ranges dialog box will open on the right side of the spreadsheet. From there, select the name of the sheet you want to unlock.
After selecting the sheet you want to unlock, click on the Remove icon in the following window and then click Done.
Google Sheets will ask if you’re sure you want to remove this protected range. Click on the Remove button.
You have now unlocked the sheet!
Unlocking a Sheet with the Sheet Manager Add-on
You can use the Sheet Manager add-on to streamline the process of unlocking sheets.
The Sheet Manager add-on allows you to unlock not only a single sheet but also multiple sheets at once.
To download the add-on, go to the Extensions menu, select Add-ons, and click on Get add-ons. You will be directed to the Google Workspace Marketplace.
In the Google Workspace Marketplace, search for Sheet Manager by Ablebits. Click on Install. You will be prompted to grant the necessary permissions.
Exit the Google Workspace Marketplace and then go back to the Extensions menu. You will find the Sheet Manager add-on in the drop-down list.
Click on Sheet Manager and select Start from the options in the drop-down list.
When the Sheet Manager window opens on the right side, select the sheets you want to unlock. To select multiple sheets, hold down the Ctrl key.
After selecting the sheets to unlock, click on the padlock icon in the menu and select Unlock.
You can also find the Unlock option by right-clicking on one of the selected sheets.
Once you’re done, you will have unlocked the selected sheets.
Unlocking a Sheet with a Custom Application Script
An alternative to using an add-on is to create a custom unlock function using an application script.
You can open the application script editor from the Extensions menu and then click on Apps Script.
function sheetsUnprotect() {
var ss = SpreadsheetApp.getActive();
var protections = ss.getProtections(SpreadsheetApp.ProtectionType.SHEET);
for (i = 0; i < protections.length; i++) {
protections[i].remove();
}
}
function onOpen(e) {
SpreadsheetApp.getUi()
.createMenu("Sheets")
.addItem('Unlock sheets', 'sheetsUnprotect')
.addToUi();
}This script creates a custom menu called Sheets upon reloading the spreadsheet.
The option within the submenu, Unlock sheets, unlocks all sheets in the spreadsheet when clicked.
Conclusions
Sheet protection helps safeguard data from unauthorized access.
When you want to grant access to other users or when the data no longer contains sensitive information, you will want to unlock the sheet.
You can unlock a sheet either from the sheet tab’s context menu, by using the Sheet Manager add-on, or with a custom application script.
How do you unlock your sheet tabs? Do you have any other tips? Let me know in the comments below!
Learn more about Google Sheets and improve your productivity with useful tips and tricks on Crawlan.com.
