gspread protect sheet except certain cells

0
votes

I'm using pythons gspread library. It has given me what I need so far but am stumpted on this one: How to do the equivalent of protect sheet except certain cells via pythons gspread?

E.g. I want to protect entire sheet (not just a range) except for particular range (use A1 as example).

I can protect a range no probs but unprotecting from that protected range is where I'm stumped.

Any idea how to do this?

Thanks

1
pythongoogle-sheetsgoogle-sheets-apiprotectedgspread

1 Answers

3
votes

I believe your goal and your current situation as follows.

  • You want to protect a sheet in Google Spreadsheet.
  • In this case, you want to unprotect only cell "A1" of the sheet.
  • You want to achieve this using gspread of python.
  • You have already been able to get and put values for Google Spreadsheet using Sheets API.

I think that in your situation, AddProtectedRangeRequest of batchUpdate method can be used.

Sample script:

client = gspread.authorize(credentials) # Pleaase use your "client" here.

spreadsheetId = "###" # Please set the Spreadsheet ID.
sheetName = "Sheet1" # Please set the sheet name you want to protect.
emailAddress = "###" # Please set your email address.

spreadsheet = client.open_by_key(spreadsheetId)
sheetId = spreadsheet.worksheet(sheetName).id
body = {
    "requests": [
        {
            "addProtectedRange": {
                "protectedRange": {
                    "range": {
                        "sheetId": sheetId
                    },
                    "unprotectedRanges": [
                        {
                            "sheetId": sheetId,
                            "startRowIndex": 0,
                            "endRowIndex": 1,
                            "startColumnIndex": 0,
                            "endColumnIndex": 1
                        }
                    ],
                    "editors": {
                        "domainUsersCanEdit": False,
                        "users": [emailAddress]
                    },
                    "warningOnly": False
                }
            }
        }
    ]
}
spreadsheet.batch_update(body)
  • When you run this script, all cell except for cell "A1" in the sheet of sheetName are protected.

References: