Access and modify protected ranges and sheets. A protected range can protect either a static
range of cells or a named range. A protected sheet may include unprotected regions. For
spreadsheets created with the older version of Google Sheets, use the
class instead.
PageProtection
// Protect range A1:B10, then remove all other users from the list of editors. var ss = SpreadsheetApp.getActive(); var range = ss.getRange('A1:B10'); var protection = range.protect().setDescription('Sample protected range'); // Ensure the current user is an editor before removing others. Otherwise, if the user's edit // permission comes from a group, the script throws an exception upon removing the group. var me = Session.getEffectiveUser(); protection.addEditor(me); protection.removeEditors(protection.getEditors()); if (protection.canDomainEdit()) { protection.setDomainEdit(false); }
// Remove all range protections in the spreadsheet that the user has permission to edit. var ss = SpreadsheetApp.getActive(); var protections = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE); for (var i = 0; i < protections.length; i++) { var protection = protections[i]; if (protection.canEdit()) { protection.remove(); } }
// Protect the active sheet, then remove all other users from the list of editors. var sheet = SpreadsheetApp.getActiveSheet(); var protection = sheet.protect().setDescription('Sample protected sheet'); // Ensure the current user is an editor before removing others. Otherwise, if the user's edit // permission comes from a group, the script throws an exception upon removing the group. var me = Session.getEffectiveUser(); protection.addEditor(me); protection.removeEditors(protection.getEditors()); if (protection.canDomainEdit()) { protection.setDomainEdit(false); }
Methods
Method | Return type | Brief description |
---|---|---|
addEditor(emailAddress) | Protection | Adds the given user to the list of editors for the protected sheet or range. |
addEditor(user) | Protection | Adds the given user to the list of editors for the protected sheet or range. |
addEditors(emailAddresses) | Protection | Adds the given array of users to the list of editors for the protected sheet or range. |
addTargetAudience(audienceId) | Protection | Adds the specified target audience as an editor of the protected range. |
canDomainEdit() | Boolean | Determines whether all users in the domain that owns the spreadsheet have permission to edit the protected range or sheet. |
canEdit() | Boolean | Determines whether the user has permission to edit the protected range or sheet. |
getDescription() | String | Gets the description of the protected range or sheet. |
getEditors() | User[] | Gets the list of editors for the protected range or sheet. |
getProtectionType() | ProtectionType | Gets the type of the protected area, either RANGE or SHEET . |
getRange() | Range | Gets the range that is being protected. |
getRangeName() | String | Gets the name of the protected range if it is associated with a named range. |
getTargetAudiences() | TargetAudience[] | Returns the IDs of the target audiences that can edit the protected range. |
getUnprotectedRanges() | Range[] | Gets an array of unprotected ranges within a protected sheet. |
isWarningOnly() | Boolean | Determines if the protected area is using "warning based" protection. |
remove() | void | Unprotects the range or sheet. |
removeEditor(emailAddress) | Protection | Removes the given user from the list of editors for the protected sheet or range. |
removeEditor(user) | Protection | Removes the given user from the list of editors for the protected sheet or range. |
removeEditors(emailAddresses) | Protection | Removes the given array of users from the list of editors for the protected sheet or range. |
removeTargetAudience(audienceId) | Protection | Removes the specified target audience as an editor of the protected range. |
setDescription(description) | Protection | Sets the description of the protected range or sheet. |
setDomainEdit(editable) | Protection | Sets whether all users in the domain that owns the spreadsheet have permission to edit the protected range or sheet. |
setNamedRange(namedRange) | Protection | Associates the protected range with an existing named range. |
setRange(range) | Protection | Adjusts the range that is being protected. |
setRangeName(rangeName) | Protection | Associates the protected range with an existing named range. |
setUnprotectedRanges(ranges) | Protection | Unprotects the given array of ranges within a protected sheet. |
setWarningOnly(warningOnly) | Protection | Sets whether or not this protected range is using "warning based" protection. |
Detailed documentation
addEditor(emailAddress)
Adds the given user to the list of editors for the protected sheet or range. This method does
not automatically give the user permission to edit the spreadsheet itself; to do that in
addition, call Spreadsheet.addEditor(emailAddress)
.
// Opens the spreadsheet file by its URL. If you created your script from within // a Google Sheets file, you can use SpreadsheetApp.getActiveSpreadsheet() instead. // TODO(developer): Replace the URL with your own. const ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/abc123456/edit'); // Adds an editor to the spreadsheet using an email address. // TODO(developer): Replace the email address with a valid email. ss.addEditor('[email protected]'); // Gets a sheet by its name and protects it. const sheet = ss.getSheetByName('Sheet1'); const sampleProtectedSheet = sheet.protect(); // Adds an editor of the protected sheet using an email address. // TODO(developer): Replace the email address with a valid email. sampleProtectedSheet.addEditor('[email protected]'); // Gets the editors of the protected sheet. const editors = sampleProtectedSheet.getEditors(); // Logs the editors' email addresses to the console. for (const editor of editors) { console.log(editor.getEmail()); }
Parameters
Name | Type | Description |
---|---|---|
emailAddress | String | The email address of the user to add. |
Return
Protection
— The object representing the protection settings, for chaining.
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly
-
https://www.googleapis.com/auth/spreadsheets
addEditor(user)
Adds the given user to the list of editors for the protected sheet or range. This method does
not automatically give the user permission to edit the spreadsheet itself; to do that in
addition, call Spreadsheet.addEditor(user)
.
// Opens the spreadsheet file by its URL. If you created your script from within // a Google Sheets file, you can use SpreadsheetApp.getActiveSpreadsheet() instead. // TODO(developer): Replace the URL with your own. const ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/abc123456/edit'); // Gets a sheet by its name. const sheet = ss.getSheetByName('Sheet1'); // Protects the sheet. const sampleProtectedSheet = sheet.protect(); // Adds the active user as an editor of the protected sheet. sampleProtectedSheet.addEditor(Session.getActiveUser()); // Gets the editors of the protected sheet. const editors = sampleProtectedSheet.getEditors(); // Logs the editors' email addresses to the console. for (const editor of editors) { console.log(editor.getEmail()); }
Parameters
Name | Type | Description |
---|---|---|
user | User | A representation of the user to add. |
Return
Protection
— The object representing the protection settings, for chaining.
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly
-
https://www.googleapis.com/auth/spreadsheets
addEditors(emailAddresses)
Adds the given array of users to the list of editors for the protected sheet or range. This
method does not automatically give the users permission to edit the spreadsheet itself; to do
that in addition, call Spreadsheet.addEditors(emailAddresses)
.
// Opens the spreadsheet file by its URL. If you created your script from within // a Google Sheets file, you can use SpreadsheetApp.getActiveSpreadsheet() instead. // TODO(developer): Replace the URL with your own. const ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/abc123456/edit'); // Gets a sheet by its name. const sheet = ss.getSheetByName('Sheet1'); // Creates variables for the email addresses to add as editors. // TODO(developer): Replace the email addresses with valid ones. const TEST_EMAIL_1 = '[email protected]'; const TEST_EMAIL_2 = '[email protected]'; // Protects the sheet. const sampleProtectedSheet = sheet.protect(); // Adds editors to the protected sheet using the email address variables. sampleProtectedSheet.addEditors([TEST_EMAIL_1, TEST_EMAIL_2]); // Gets the editors of the protected sheet. const editors = sampleProtectedSheet.getEditors(); // Logs the editors' email addresses to the console. for (const editor of editors) { console.log(editor.getEmail()); }
Parameters
Name | Type | Description |
---|---|---|
emailAddresses | String[] | An array of email addresses of the users to add. |
Return
Protection
— The object representing the protection settings, for chaining.
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly
-
https://www.googleapis.com/auth/spreadsheets
addTargetAudience(audienceId)
Adds the specified target audience as an editor of the protected range.
Parameters
Name | Type | Description |
---|---|---|
audienceId | String | The ID of the target audience to add. |
Return
Protection
— The object representing the protection settings, for chaining.
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly
-
https://www.googleapis.com/auth/spreadsheets
canDomainEdit()
Determines whether all users in the domain that owns the spreadsheet have permission to edit the protected range or sheet. Throws an exception if the user does not have permission to edit the protected range or sheet.
// Opens the spreadsheet file by its URL. If you created your script from within // a Google Sheets file, you can use SpreadsheetApp.getActiveSpreadsheet() instead. // TODO(developer): Replace the URL with your own. const ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/abc123456/edit'); // Gets a sheet by its name. const sheet = ss.getSheetByName('Sheet1'); // Protects the sheet. const sampleProtectedSheet = sheet.protect(); // Logs whether domain users have permission to edit the protected sheet to the console. console.log(sampleProtectedSheet.canDomainEdit());
Return
Boolean
— true
if all users in the domain that owns the spreadsheet have permission to
edit the protected range or sheet; false
if they don't.
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly
-
https://www.googleapis.com/auth/spreadsheets
canEdit()
Determines whether the user has permission to edit the protected range or sheet. The spreadsheet owner is always able to edit protected ranges and sheets.
// Remove all range protections in the spreadsheet that the user has permission to edit. var ss = SpreadsheetApp.getActive(); var protections = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE); for (var i = 0; i < protections.length; i++) { var protection = protections[i]; if (protection.canEdit()) { protection.remove(); } }
Return
Boolean
— true
if the user has permission to edit the protected range or sheet; false
if not
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly
-
https://www.googleapis.com/auth/spreadsheets
getDescription()
Gets the description of the protected range or sheet. If no description is set, this method returns an empty string.
// Opens the spreadsheet file by its URL. If you created your script from within // a Google Sheets file, you can use SpreadsheetApp.getActiveSpreadsheet() instead. // TODO(developer): Replace the URL with your own. const ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/abc123456/edit'); // Gets a sheet by its name. const sheet = ss.getSheetByName('Sheet1'); // Protects the sheet and sets the description. const sampleProtectedSheet = sheet.protect().setDescription('Sample sheet is protected'); // Gets the description of the protected sheet and logs it to the console. const sampleProtectedSheetDescription = sampleProtectedSheet.getDescription(); console.log(sampleProtectedSheetDescription);
Return
String
— The description of the protected range or sheet, or an empty string if no description
is set.
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly
-
https://www.googleapis.com/auth/spreadsheets
getEditors()
Gets the list of editors for the protected range or sheet. Throws an exception if the user does not have permission to edit the protected range or sheet.
// Protect the active sheet, then remove all other users from the list of editors. var sheet = SpreadsheetApp.getActiveSheet(); var protection = sheet.protect().setDescription('Sample protected sheet'); // Ensure the current user is an editor before removing others. Otherwise, if the user's edit // permission comes from a group, the script throws an exception upon removing the group. var me = Session.getEffectiveUser(); protection.addEditor(me); protection.removeEditors(protection.getEditors()); if (protection.canDomainEdit()) { protection.setDomainEdit(false); }
Return
User[]
— an array of users with permission to edit the protected range or sheet
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly
-
https://www.googleapis.com/auth/spreadsheets
getProtectionType()
Gets the type of the protected area, either RANGE
or SHEET
.
// Opens the spreadsheet file by its URL. If you created your script from within // a Google Sheets file, you can use SpreadsheetApp.getActiveSpreadsheet() instead. // TODO(developer): Replace the URL with your own. const ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/abc123456/edit'); // Gets a sheet by its name. const sheet = ss.getSheetByName('Sheet1'); // Protects the sheet. const sampleProtectedSheet = sheet.protect(); // Gets the type of the protected area. const protectionType = sampleProtectedSheet.getProtectionType(); // Logs 'SHEET'to the console since the type of the protected area is a sheet. console.log(protectionType.toString());
Return
ProtectionType
— The type of protected area, either RANGE
or SHEET
.
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly
-
https://www.googleapis.com/auth/spreadsheets
getRange()
Gets the range that is being protected. If the protection applies to the sheet instead of a range, this method returns a range that spans the entire sheet.
// Opens the spreadsheet file by its URL. If you created your script from within // a Google Sheets file, you can use SpreadsheetApp.getActiveSpreadsheet() instead. // TODO(developer): Replace the URL with your own. const ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/abc123456/edit'); // Gets a sheet by its name. const sheet = ss.getSheetByName('Sheet1'); // Gets the range 'A1:B10' of Sheet1. const range = sheet.getRange('A1:B10'); // Makes cells A1:B10 a protected range. const sampleProtectedRange = range.protect(); // Gets the protected ranges on the sheet. const protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE); // Logs the A1 notation of the first protected range on the sheet. console.log(protections[0].getRange().getA1Notation());
Return
Range
— The range that is being protected.
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly
-
https://www.googleapis.com/auth/spreadsheets
getRangeName()
Gets the name of the protected range if it is associated with a named range. Returns null
if the protection is not associated with a named range. Note that scripts must explicitly
call setRangeName(rangeName)
to associate a protected range with a named range; calling
Range.protect()
to create a protection from a Range
that happens to be a
named range, without calling setRangeName(rangeName)
, is not sufficient to associate
them. However, creating a protected range from a named range in the Google Sheets UI associates
them automatically.
// Protect a named range in a spreadsheet and log the name of the protected range. var ss = SpreadsheetApp.getActive(); var range = ss.getRange('A1:B10'); var protection = range.protect(); ss.setNamedRange('Test', range); // Create a named range. protection.setRangeName('Test'); // Associate the protection with the named range. Logger.log(protection.getRangeName()); // Verify the name of the protected range.
Return
String
— the name of the protected named range, or null
if the protected range is not
associated with a named range
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly
-
https://www.googleapis.com/auth/spreadsheets
getTargetAudiences()
Returns the IDs of the target audiences that can edit the protected range.
Return
TargetAudience[]
— An array of the IDs of target audiences.
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly
-
https://www.googleapis.com/auth/spreadsheets
getUnprotectedRanges()
Gets an array of unprotected ranges within a protected sheet. If the Protection
object
corresponds to a protected range instead of a protected sheet, this method returns an empty
array. To change the unprotected ranges, use setUnprotectedRanges(ranges)
to set a
new array of ranges; to re-protect the entire sheet, set an empty array.
// Unprotect cells E2:F5 in addition to any other unprotected ranges in the protected sheet. var sheet = SpreadsheetApp.getActiveSheet(); var protection = sheet.protect(); var unprotected = protection.getUnprotectedRanges(); unprotected.push(sheet.getRange('E2:F5')); protection.setUnprotectedRanges(unprotected);
Return
Range[]
— an array of unprotected ranges within a protected sheet
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly
-
https://www.googleapis.com/auth/spreadsheets
isWarningOnly()
Determines if the protected area is using "warning based" protection. Warning-based protection
means that every user can edit data in the area, except editing prompts a warning asking the
user to confirm the edit. By default, protected ranges or sheets are not warning-based. To
change to the warning state, use setWarningOnly(warningOnly)
.
// Opens the spreadsheet file by its URL. If you created your script from within // a Google Sheets file, you can use SpreadsheetApp.getActiveSpreadsheet() instead. // TODO(developer): Replace the URL with your own. const ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/abc123456/edit') // Gets a sheet by its name. const sheet = ss.getSheetByName('Sheet1'); // Protects the sheet. const sampleProtectedSheet = sheet.protect(); // Sets the warning status for the protected sheet as true. sampleProtectedSheet.setWarningOnly(true); const protectedSheetWarningStatus = sampleProtectedSheet.isWarningOnly(); // Logs the warning status of the protected sheet to the console. console.log(protectedSheetWarningStatus);
Return
Boolean
— true
if the protected range or sheet is only using warning-based protection.
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly
-
https://www.googleapis.com/auth/spreadsheets
remove()
Unprotects the range or sheet.
// Remove all range protections in the spreadsheet that the user has permission to edit. var ss = SpreadsheetApp.getActive(); var protections = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE); for (var i = 0; i < protections.length; i++) { var protection = protections[i]; if (protection.canEdit()) { protection.remove(); } }
// Remove sheet protection from the active sheet, if the user has permission to edit it. var sheet = SpreadsheetApp.getActiveSheet(); var protection = sheet.getProtections(SpreadsheetApp.ProtectionType.SHEET)[0]; if (protection && protection.canEdit()) { protection.remove(); }
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly
-
https://www.googleapis.com/auth/spreadsheets
removeEditor(emailAddress)
Removes the given user from the list of editors for the protected sheet or range. Note that if the user is a member of a Google Group that has edit permission, or if all users in the domain have edit permission, the user are still be able to edit the protected area. Neither the owner of the spreadsheet nor the current user can be removed.
// Opens the spreadsheet file by its URL. If you created your script from within // a Google Sheets file, you can use SpreadsheetApp.getActiveSpreadsheet() instead. // TODO(developer): Replace the URL with your own. const ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/abc123456/edit'); // Gets a sheet by its name. const sheet = ss.getSheetByName('Sheet1'); // Creates a variable for an email address. // TODO(developer): Replace the email address with a valid one. const TEST_EMAIL = '[email protected]'; // Protects the sheet. const sampleProtectedSheet = sheet.protect(); // Adds an editor to the protected sheet using the email address variable. sampleProtectedSheet.addEditor(TEST_EMAIL); // Removes the editor from the protected sheet using the email address variable. sampleProtectedSheet.removeEditor(TEST_EMAIL); // Gets the editors of the protected sheet. const editors = sampleProtectedSheet.getEditors(); // Logs the editors' email addresses to the console. for (const editor of editors) { console.log(editor.getEmail()); }
Parameters
Name | Type | Description |
---|---|---|
emailAddress | String | The email address of the user to remove. |
Return
Protection
— The object representing the protection settings, for chaining.
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly
-
https://www.googleapis.com/auth/spreadsheets
removeEditor(user)
Removes the given user from the list of editors for the protected sheet or range. Note that if the user is a member of a Google Group that has edit permission, or if all users in the domain have edit permission, the user is still be able to edit the protected area as well. Neither the owner of the spreadsheet nor the current user can be removed.
// Opens the spreadsheet file by its URL. If you created your script from within // a Google Sheets file, you can use SpreadsheetApp.getActiveSpreadsheet() instead. // TODO(developer): Replace the URL with your own. const ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/abc123456/edit'); // Gets a sheet by its name. const sheet = ss.getSheetByName('Sheet1'); // Protects the sheet. const sampleProtectedSheet = sheet.protect(); // Removes the active user from the editors of the protected sheet. sampleProtectedSheet.removeEditor(Session.getActiveUser()); // Gets the editors of the protected sheet. const editors = sampleProtectedSheet.getEditors(); // Logs the editors' email addresses to the console. for (const editor of editors) { console.log(editor.getEmail()); }
Parameters
Name | Type | Description |
---|---|---|
user | User | A representation of the user to remove. |
Return
Protection
— the object representing the protection settings, for chaining
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly
-
https://www.googleapis.com/auth/spreadsheets
removeEditors(emailAddresses)
Removes the given array of users from the list of editors for the protected sheet or range. Note that if any of the users are members of a Google Group that has edit permission, or if all users in the domain have edit permission, those users are still be able to edit the protected area. Neither the owner of the spreadsheet nor the current user can be removed.
// Protect the active sheet, then remove all other users from the list of editors. var sheet = SpreadsheetApp.getActiveSheet(); var protection = sheet.protect().setDescription('Sample protected sheet'); // Ensure the current user is an editor before removing others. Otherwise, if the user's edit // permission comes from a group, the script throws an exception upon removing the group. var me = Session.getEffectiveUser(); protection.addEditor(me); protection.removeEditors(protection.getEditors()); if (protection.canDomainEdit()) { protection.setDomainEdit(false); }
Parameters
Name | Type | Description |
---|---|---|
emailAddresses | String[] | An array of email addresses of the users to remove. |
Return
Protection
— the object representing the protection settings, for chaining
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly
-
https://www.googleapis.com/auth/spreadsheets
removeTargetAudience(audienceId)
Removes the specified target audience as an editor of the protected range.
Parameters
Name | Type | Description |
---|---|---|
audienceId | String | The ID of the target audience to remove. |
Return
Protection
— The object representing the protection settings, for chaining.
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly
-
https://www.googleapis.com/auth/spreadsheets
setDescription(description)
Sets the description of the protected range or sheet.
// Opens the spreadsheet file by its URL. If you created your script from within // a Google Sheets file, you can use SpreadsheetApp.getActiveSpreadsheet() instead. // TODO(developer): Replace the URL with your own. const ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/abc123456/edit'); // Gets the sheet 'Sheet1' by its name. const sheet = ss.getSheetByName('Sheet1'); // Protects the sheet. const sampleProtectedSheet = sheet.protect(); // Sets the sheet description to 'Sheet1 is protected.' sampleProtectedSheet.setDescription('Sheet1 is protected'); // Gets the description of the protected sheet. const sampleProtectedSheetDescription = sampleProtectedSheet.getDescription(); // Logs the description of the protected sheet to the console. console.log(sampleProtectedSheetDescription);
Parameters
Name | Type | Description |
---|---|---|
description | String | The description of the protected range or sheet. |
Return
Protection
— The object representing the protection settings, for chaining.
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly
-
https://www.googleapis.com/auth/spreadsheets
setDomainEdit(editable)
Sets whether all users in the domain that owns the spreadsheet have permission to edit the protected range or sheet. Note that any users who have explicit edit permission are able to edit the protected area regardless of this setting. Throws an exception if the spreadsheet does not belong to a Google Workspace domain (that is, if it is owned by a gmail.com account).
Parameters
Name | Type | Description |
---|---|---|
editable | Boolean | true if all users in the domain that owns the spreadsheet should have
permission to edit the protected range or sheet; false if not. |
Return
Protection
— the object representing the protection settings, for chaining
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly
-
https://www.googleapis.com/auth/spreadsheets
setNamedRange(namedRange)
Associates the protected range with an existing named range. If the named range covers a
different area from the current protected range, this method moves the protection to cover the
the named range instead. The named range must be on the same sheet as the current protected
range. Note that scripts must explicitly call this method to associate a protected range with a
named range; calling Range.protect()
to create a protection from a Range
that happens to be a named range, without calling setRangeName(rangeName)
, is not
sufficient to associate them. However, creating a protected range from a named range in the
Google Sheets UI associates them automatically.
// Opens the spreadsheet file by its URL. If you created your script from within // a Google Sheets file, you can use SpreadsheetApp.getActiveSpreadsheet() instead. // TODO(developer): Replace the URL with your own. const ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/abc123456/edit'); // Protects cells A1:D10 on Sheet1. const sheet = ss.getSheetByName('Sheet1'); const protectedRange = sheet.getRange('A1:D10').protect(); // Logs the current protected range, A1:D10. console.log(protectedRange.getRange().getA1Notation()); // Creates a named range for cells E1:J10 called 'NewRange.' const newRange = sheet.getRange('E1:J10'); ss.setNamedRange('NewRange', newRange); const namedRange = ss.getNamedRanges()[0]; // Updates the protected range to the named range, 'NewRange.' // This updates the protected range on Sheet1 from A1:D10 to E1:J10. protectedRange.setNamedRange(namedRange); // Logs the updated protected range to the console. console.log(protectedRange.getRange().getA1Notation());
Parameters
Name | Type | Description |
---|---|---|
namedRange | NamedRange | The existing named range to associate with the protected range. |
Return
Protection
— The object representing the protection settings, for chaining.
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly
-
https://www.googleapis.com/auth/spreadsheets
setRange(range)
Adjusts the range that is being protected. If the given range covers a different area from the current protected range, this method moves the protection to cover the new range instead.
// Opens the spreadsheet file by its URL. If you created your script from within // a Google Sheets file, you can use SpreadsheetApp.getActiveSpreadsheet() instead. // TODO(developer): Replace the URL with your own. const ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/abc123456/edit'); // Protects cells A1:D10 on Sheet1 of the spreadsheet. const sheet = ss.getSheetByName('Sheet1'); const protectedRange = sheet.getRange('A1:D10').protect(); // Logs the original protected range, A1:D10, to the console. console.log(protectedRange.getRange().getA1Notation()); // Gets the range E1:J10. const newRange = sheet.getRange('E1:J10'); // Updates the protected range to E1:J10. protectedRange.setRange(newRange); // Logs the updated protected range to the console. console.log(protectedRange.getRange().getA1Notation());
Parameters
Name | Type | Description |
---|---|---|
range | Range | The new range to protect from edits. |
Return
Protection
— The object representing the protection settings, for chaining.
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly
-
https://www.googleapis.com/auth/spreadsheets
setRangeName(rangeName)
Associates the protected range with an existing named range. If the named range covers a
different area from the current protected range, this method moves the protection to cover the
the named range instead. The named range must be on the same sheet as the current protected
range. Note that scripts must explicitly call this method to associate a protected range with a
named range; calling Range.protect()
to create a protection from a Range
that happens to be a named range, without calling setRangeName(rangeName)
, is not
sufficient to associate them. However, creating a protected range from a named range in the
Google Sheets UI associates them automatically.
// Protect a named range in a spreadsheet and log the name of the protected range. var ss = SpreadsheetApp.getActive(); var range = ss.getRange('A1:B10'); var protection = range.protect(); ss.setNamedRange('Test', range); // Create a named range. protection.setRangeName('Test'); // Associate the protection with the named range. Logger.log(protection.getRangeName()); // Verify the name of the protected range.
Parameters
Name | Type | Description |
---|---|---|
rangeName | String | The name of the named range to be protected. |
Return
Protection
— the object representing the protection settings, for chaining
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly
-
https://www.googleapis.com/auth/spreadsheets
setUnprotectedRanges(ranges)
Unprotects the given array of ranges within a protected sheet. Throws an exception if the
Protection
object corresponds to a protected range instead of a protected sheet or if
any of the ranges are not on the protected sheet. To change the unprotected ranges, set a new
array of ranges; to re-protect the entire sheet, set an empty array.
// Protect the active sheet except B2:C5, then remove all other users from the list of editors. var sheet = SpreadsheetApp.getActiveSheet(); var protection = sheet.protect().setDescription('Sample protected sheet'); var unprotected = sheet.getRange('B2:C5'); protection.setUnprotectedRanges([unprotected]); // Ensure the current user is an editor before removing others. Otherwise, if the user's edit // permission comes from a group, the script throws an exception upon removing the group. var me = Session.getEffectiveUser(); protection.addEditor(me); protection.removeEditors(protection.getEditors()); if (protection.canDomainEdit()) { protection.setDomainEdit(false); }
Parameters
Name | Type | Description |
---|---|---|
ranges | Range[] | The array of ranges to leave unprotected within a protected sheet. |
Return
Protection
— the object representing the protection settings, for chaining
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly
-
https://www.googleapis.com/auth/spreadsheets
setWarningOnly(warningOnly)
Sets whether or not this protected range is using "warning based" protection. Warning-based
protection means that every user can edit data in the area, except editing prompts a warning
asking the user to confirm the edit. By default, protected ranges or sheets are not
warning-based. To check warning state, use isWarningOnly()
.
// Opens the spreadsheet file by its URL. If you created your script from within // a Google Sheets file, you can use SpreadsheetApp.getActiveSpreadsheet() instead. // TODO(developer): Replace the URL with your own. const ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/abc123456/edit'); // Gets the sheet 'Sheet1' by its name. const sheet = ss.getSheetByName('Sheet1'); // Protects the sheet and sets the protection to warning-based. const sampleProtectedSheet = sheet.protect().setWarningOnly(true); // Logs whether the protected sheet is warning-based to the console. console.log(sampleProtectedSheet.isWarningOnly());
Parameters
Name | Type | Description |
---|---|---|
warningOnly | Boolean |
Return
Protection
— The object representing the protection settings, for chaining.
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly
-
https://www.googleapis.com/auth/spreadsheets