CODEWITHSUNDEEP
pythongoogle-sheets-apiprotectedgspread
ohdanny
ohdanny

Reputation: 33

How do you modify an existing protected range in Google sheets with a python script

I am currently using gspread to access a google sheet and I want to protect a certain range using python but I am not sure how to go about doing it. For example, the protected range is now 'Form Responses 1'!3:500 and I want to update it to 'Form Responses 1'!3:800.

I am still new at using google API and have tried reading the documentation here but I don't fully understand how to implement it. https://developers.google.com/sheets/api/samples/ranges

Upvotes: 2

Views: 2595

Answers (1)

Rafa Guillermo
Rafa Guillermo

Reputation: 15377

Answer:

In order to update a protected range, you need to make a batchUpdate request and specify the namedRangeId and protectedRangeId.

Using the Sheets API:

Build your request:

request = {
            "requests": [
              {
                "updateNamedRange": {
                  "namedRange": {
                    "name" : "New name", #optional, only if you want to change the name
                    "namedRangeId": "<named-range-id>",
                    "range": {
                      "sheetId": "<sheet-id>",
                      "startRowIndex": int,
                      "endRowIndex": int,
                      "startColumnIndex": int,
                      "endColumnIndex": int,
                    },
                  },
                 "fields": "*"
                }
              },
              {
                "updateProtectedRange": {
                  "protectedRange": {
                    "protectedRangeId": <protected-range-id>,
                    "namedRangeId": "<named-range-id>",
                    "warningOnly": False,
                    "editors": {
                      "users": [
                        "[email protected]",
                        "[email protected]",
                      ],
                    }
                  },
                  "fields": "*"
                }
              }
            ]
          }

Then send your request using the discovery library:

from googleapiclient import discovery

def main():
    # authentication code goes here
    service = discovery.build('sheets', 'v4', credentials = <your-#credentials>)

    spreadsheetID = '<your-spreadsheet-ID>'
    request = {dictionary-object-built-above}

    req = service.spreadsheet().batchUpdate(spreadsheetId = spreadsheetID, body = request)
    response = req.execute()

Or by using the gspread library:

Once you have enabled the API and obtained a service account key as per the gspread documentation, you can build your request like above but you need to make sure that you add the service account email to the users that can edit the protected range:

request = {
            "requests": [
              {
                "updateNamedRange": {
                  "namedRange": {
                    "name" : "New name", #optional, only if you want to change the name
                    "namedRangeId": "<named-range-id>",
                    "range": {
                      "sheetId": "<sheet-id>",
                      "startRowIndex": int,
                      "endRowIndex": int,
                      "startColumnIndex": int,
                      "endColumnIndex": int,
                    },
                  },
                 "fields": "*"
                }
              },
              {
                "updateProtectedRange": {
                  "protectedRange": {
                    "protectedRangeId": <protected-range-id>,
                    "namedRangeId": "<named-range-id>",
                    "warningOnly": False,
                    "editors": {
                      "users": [
                        "[email protected]",
                        "[email protected]",
                      ],
                    }
                  },
                  "fields": "*"
                }
              }
            ]
          }

and then make the request with gspread:

import gspread
from oauth2client.service_account import ServiceAccountCredentials

def main():
    scope = ['https://www.googleapis.com/auth/spreadsheets']
    credentials = ServiceAccountCredentials.from_json_keyfile_name('credentials.json', scope)

    gc = gspread.authorize(credentials)
    response = gc.open_by_key('<spreadsheet-id>').batch_update(request)

    print(response)

References:

Upvotes: 1

Related Questions