Home->Tutorials->Google Sheet help->
Applying protection to selected range of cells in google sheet for specific users using google script
[Updated as on Mar 08, 2022]
Google sheet allows users to apply protection to a particular sheet or a range of cells in a sheet thereby restricting editing only by the owner or by selected users as may be decided by the user. Sometimes it may become necessary for the owner of a spreadsheet to permit editing of certain ranges of cells only by designated users as fixed by the owner. For instance, a Company needs to collect daily stock position from its depots. The depot managers have to submit the required data in a google sheet which is shared commonly to all depots. The format in which the depot managers are required to submit the daily stock position may look somewhat like this:
Though the sheet is protected such that only depot managers have permission to edit a certain range of cells in the spreadsheet, there is a risk of one depot manager accidently deleting or editing the figures of another depot. In order to avoid such incidents, editing rights need to be given to each user only for specified ranges. For instance in the above table, Column name ‘Depot A’ should be edited only by ‘Depot A’ Manager. Similarly other columns should be edited only by the respective managers of depots. This restriction can be introduced manually by adding range protection for each individual range. However imagine in this example there are 100 depots and edit restriction is to be applied manually for 100 columns. This will be a cumbersome process if it is to be done manually by applying protection for each individual columns and assigning specific users for each such columns.
In this article we will discuss a simple google script code to automatically add edit restrictions as well as to delete the restriction added. First we will prepare a table in sheet 2 containing the list of depots, address of range of cells allocated to each depot and the gmail id of the depot Managers. The data in the table will be used by the script in applying protection to the sheet.
In the example above protection is to be applied to each individual column from Row no.4 to Row No.14 in Sheet 1 with edit right given only to the respective depot manager’s mail id.
Select ‘Extensions’ from menu and click ‘Apps script’. Copy the code given below to Code.gs and save and run the code.
function protectnew() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
// input data is read from the table from row 2 to 6
for (var i = 2;i<7;i++) {
Logger.log(i);
var r = SpreadsheetApp.getActive().getRange("Sheet2!a" + i).getValues();
var rr = SpreadsheetApp.getActive().getRange("Sheet2!b" +i).getValues();
var rrr = SpreadsheetApp.getActive().getRange("Sheet2!c" +i).getValues();
// selecting the range to be protected and assigning to variable range
var range = ss.getRange(rr);
// assigning a name to the protection applied which in this case is the value in column B of the table namely ‘Depot name’.
var protection = range.protect().setDescription(rrr);
// adding user name given in column A of the table to the protected range
protection.removeEditors(protection.getEditors());
protection.addEditors(r);
}
}
Select ‘Data’ from menu and click ‘Protect sheets and ranges’. Protections can be seen created in Sheet 1 for the range of cells shown in column name ‘Range’ in the table in sheet 2 with edit right assigned to the respective mail ids shown in ‘’column Úser Id’ as shown below. Each of such protection is assigned a name as given in column ‘Depot name’.
You can click the protection name ‘Depot A and see protection with name ’Depot A’ created for range Sheet1 B4:B14 with edit permission assigned to mail id ‘apple@gmail.com’ in addition to the owner of the spreadsheet.
Google script to unprotect specific range of cells
The same table created in Sheet 2 could be used for removing protection for all or any of the protected ranges as may be required. Here the protection with name ‘Depot B’ as shown in row no.3 in sheet 2 is proposed to be removed. Variable ‘i’ is limited to the value 3 in the script below.
function removeprotection() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
// row number in the table for which protection is to be removed is to be set here
for (var i = 3 ; i<4; i++) {
Logger.log(i);
var rr = SpreadsheetApp.getActive().getRange("Sheet2!b" +i).getValues();
var rrr = SpreadsheetApp.getActive().getRange("Sheet2!c" +i).getValues();
var ran = ss.getRange(rr);
var first = ss.getSheetByName("Sheet1");
var protections = first.getProtections(SpreadsheetApp.ProtectionType.RANGE);
// if the range protection existing in the sheet matches with name of the protection in the selected row in the table, the protection is deleted
for (var ii = 0; ii < protections.length; ii++) {
if (protections[ii].getDescription() == rrr) {
protections[ii].remove();
}
}
}
}
Run the script and view the list of protected ranges in the sheet and it can be seen that the protection name ‘Depot B’ is missing in the list.
Page views: