Add a Protected Range to an existing NamedRange

Question

I have an existing worksheet with an existing NamedRange for it and I would like to call the batch_update method of the API to protect that range from being edited by anyone other than the user that makes the batch_update call.

I have seen an example on how to add protected ranges via a new range definition, but not from an existing NamedRange.

I know I need to send the addProtectedRangeResponse request. Can I define the request body with a Sheetname!NamedRange notation?

this_range = worksheet_name + "!" + nrange
batch_update_spreadsheet_request_body = {
    'requests': [
        {
            "addProtectedRange": {
                "protectedRange": {
                "range": {
                    "name": this_range,
                },
                    "description": "Protecting xyz",
                    "warningOnly": False
                }
            }
        }
    ],  
}

EDIT: Given @Tanaike feedback, I adapted the call to something like:

        body = {
            "requests": [
                {
                    "addProtectedRange": {
                        "protectedRange": {
                            "namedRangeId": namedRangeId,
                            "description": "Protecting via gsheets_manager",
                            "warningOnly": False,
                            "requestingUserCanEdit": False
                        }
                    }
                }
            ]
        }
        res2 = service.spreadsheets().batchUpdate(spreadsheetId=ssId, body=body).execute()
        print(res2)

But although it lists the new protections, it still lists 5 different users (all of them) as editors. If I try to manually edit the protection added by my gsheets_manager script, it complains that the range is invalid:

Interestingly, it seems to ignore the requestUserCanEdit flag according to the returning message:

{u'spreadsheetId': u'NNNNNNNNNNNNNNNNNNNNNNNNNNNN', u'replies': [{u'addProtectedRange': {u'protectedRange': {u'requestingUserCanEdit': True, u'description': u'Protecting via gsheets_manager', u'namedRangeId': u'1793914032', u'editors': {}, u'protectedRangeId': 2012740267, u'range': {u'endColumnIndex': 1, u'sheetId': 1196959832, u'startColumnIndex': 0}}}}]}

Any ideas?

Tanaike Tanaike · Accepted Answer · 2019-01-02T23:36:57

How about using namedRangeId for your situation? The flow of the sample script is as follows.

Retrieve namedRangeId using spreadsheets().get of Sheets API.
Set a protected range using namedRangeId using spreadsheets().batchUpdate of Sheets API.

Sample script:

nrange = "### name ###"
ssId = "### spreadsheetId ###"

res1 = service.spreadsheets().get(spreadsheetId=ssId, fields="namedRanges").execute()
namedRangeId = ""
for e in res1['namedRanges']:
    if e['name'] == nrange:
        namedRangeId = e['namedRangeId']
        break
body = {
    "requests": [
        {
            "addProtectedRange": {
                "protectedRange": {
                    "namedRangeId": namedRangeId,
                    "description": "Protecting xyz",
                    "warningOnly": False
                }
            }
        }
    ]
}
res2 = service.spreadsheets().batchUpdate(spreadsheetId=ssId, body=body).execute()
print(res2)

Note:

This script supposes that Sheets API can be used for your environment.
This is a simple sample script. So please modify it to your situation.

References:

If this was not what you want, I'm sorry.

Edit:

In my above answer, I modified your script using your settings. If you want to protect the named range, please modify body as follows.

body

body = {
    "requests": [
        {
            "addProtectedRange": {
                "protectedRange": {
                    "namedRangeId": namedRangeId,
                    "description": "Protecting xyz",
                    "warningOnly": False,
                    "editors": {"users": ["### your email address ###"]}, # Added
                }
            }
        }
    ]
}

By this, the named range can be modified by only you. I'm using such settings and I confirm that it works in my environment. But if in your situation, this didn't work, I'm sorry.