To protect a range of cells in a Google Sheet using Apps Script, you can use the protect() method of the Range class. Here’s an example code snippet:
function protectRange() { var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); var sheet = spreadsheet.getActiveSheet(); var range = sheet.getRange('A1:B5'); // Replace with the range you want to protect var protection = range.protect(); // Optional: Set protection settings protection.setDescription('Protected Range'); protection.setWarningOnly(true); protection.removeEditors(sheet.getEditors()); protection.addEditor('[email protected]'); // Replace with the email of the user you want to allow editing // Optional: Add unprotected ranges var unprotectedRange = sheet.getRange('C1:D5'); // Replace with any additional ranges you want to leave unprotected protection.setUnprotectedRanges([unprotectedRange]); }
In this code, the protectRange function protects a specific range of cells in the Google Sheet. The active spreadsheet is obtained using SpreadsheetApp.getActiveSpreadsheet(). You can modify this line to access a specific spreadsheet if needed.
Next, the active sheet is obtained using getActiveSheet().
You can define the range of cells you want to protect using the getRange() method. In this example, the range is set to 'A1:B5', but you should change it to your desired range.
The protect() method is called on the range object to create a protection for the specified range.
You can optionally set protection settings using methods such as setDescription(), setWarningOnly(), removeEditors(), and addEditor() on the protection object. These methods allow you to provide a description for the protection, set it as warning-only (users can edit with a warning), remove all editors except for specific ones, and add specific editors. Modify these methods based on your protection requirements.
You can also optionally add unprotected ranges using the setUnprotectedRanges() method on the protection object. This method accepts an array of ranges that should be left unprotected. You can define additional ranges to be unprotected if needed.
You can call the protectRange function from another function or set up triggers to execute it based on your needs. When executed, it will protect the specified range in the Google Sheet according to the specified protection settings.
Feel free to modify the code to suit your specific requirements, such as protecting multiple ranges, customizing protection settings, protecting specific sheets, or performing additional operations before or after protecting the range.