Search code examples
pythonpython-3.xgoogle-sheetsgoogle-sheets-apigspread

gpsread Google Sheets Protect Worksheet with Warning

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?

Solution

  • I believe your goal as follows.

    • Question 1: You want to show the warning when users edited the cell of A1:J10 in the sheet. When users edit the cells in the protected range, you want to show the warning screen.
    • Question 2: You want to protect a sheet instead of a range. When users edit the cells in the protected sheet, you want to show the warning screen.

    Answer for question 1:

    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.

    Sample script:

    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)
    • When above script is run, the cells of "A1:J10" of "Sheet1" is protected. And when users edited the cells in the protected range, the warning screen is opened.

    Answer for question 2:

    In this case, also, how about directly using the batchUpdate method? The sample script is as follows.

    Sample script:

    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)
    • When above script is run, the sheet of "Sheet1" is protected. And when users edited the cells in "Sheet1", the warning screen is opened.

    References: