I am trying to use gspread
library to protect a worksheet and warn user if they are about to make a change even if they're an editor.
In Google Sheets this is called "Show a warning when editing this range"
Gpsread has a function add_protected_range
Here. Via gpsread I run worksheet.add_protected_range('A1:J10', warning_only=True)
and I get an error
gspread.exceptions.APIError: {'code': 400, 'message': 'Invalid requests[0].addProtectedRange: ProtectedRange is warningOnly. Editors cannot be set on it.', 'status': 'INVALID_ARGUMENT'}
Removing warning_only
returns no error but then editors are still able to edit, which is not my goal.
How do I get this to work?
Also this function only works to make the range protected. Is there a way to make the entire sheet protected?
I believe your goal as follows.
A1:J10
in the sheet. When users edit the cells in the protected range, you want to show the warning screen.When warningOnly
is true
, the property of editors
cannot be used. But, when I saw the script add_protected_range
, it seems that the default editors are included. Ref I think that by this, such error occurs. So in this case, in order to avoid this, how about directly using the batchUpdate method? The sample script is as follows.
spreadsheetId = "###" # Please set the Spreadsheet ID.
sheetName = "Sheet1" # Please set the sheet name.
client = gspread.authorize(credentials)
ss = client.open_by_key(spreadsheetId)
sheetId = ss.worksheet(sheetName)._properties['sheetId']
request_body = {
"requests": [
{
"addProtectedRange": {
"protectedRange": {
"range": {
"sheetId": sheetId,
"startRowIndex": 0,
"endRowIndex": 10,
"startColumnIndex": 0,
"endColumnIndex": 10
},
"warningOnly": True
}
}
}
]
}
res = ss.batch_update(request_body)
In this case, also, how about directly using the batchUpdate method? The sample script is as follows.
spreadsheetId = "###" # Please set the Spreadsheet ID.
sheetName = "Sheet1" # Please set the sheet name.
client = gspread.authorize(credentials)
ss = client.open_by_key(spreadsheetId)
sheetId = ss.worksheet(sheetName)._properties['sheetId']
request_body = {
"requests": [
{
"addProtectedRange": {
"protectedRange": {
"range": {
"sheetId": sheetId,
},
"warningOnly": True
}
}
}
]
}
res = ss.batch_update(request_body)