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.

    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)
    

    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)
    

    References: