2

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

ohdanny
  • 33
  • 5
  • What are the ranges you want to protect? are your `3:500` and `3:800` rows or columns? You need to specify a start and end for both columns and rows. – Rafa Guillermo Sep 26 '19 at 08:56

1 Answers1

1

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": [
                        "user1@email.com",
                        "user2@email.com",
                      ],
                    }
                  },
                  "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": [
                        "user1@email.com",
                        "serviceacc@projectname-XXXXXXXXXXXXX.iam.gserviceaccount.com",
                      ],
                    }
                  },
                  "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:

Nimantha
  • 6,405
  • 6
  • 28
  • 69
Rafa Guillermo
  • 14,474
  • 3
  • 18
  • 54
  • It seems that OP might want to achieve this with gspread. When the sample script for using gspread is added, it might be more useful for OP. If I misunderstood OP's goal, I apologize. – Tanaike Sep 27 '19 at 12:47
  • @Tanaike gspread doesn't have the functionality to edit protected ranges, this needs to be done with the API. – Rafa Guillermo Sep 27 '19 at 13:00
  • Thank you for replying. "edit protected ranges" is achieved by the the method of spreadsheets.batchUpdate of Sheets. In your script, the method of spreadsheets.batchUpdate of Sheets API is used by google-api-python-client. In this case, gspread can use the method of batchUpdate of Sheets API. [Ref](https://gspread.readthedocs.io/en/latest/api.html#gspread.models.Spreadsheet.batch_update) So I thought that when not only your sample script with google-api-python-client, but also a sample script with gspread are proposed, they might be more useful for OP and other users. – Tanaike Sep 27 '19 at 13:07
  • @Tanaike Thank you for the link to the documentation, I will now update my answer with gspread usage. – Rafa Guillermo Sep 27 '19 at 13:59
  • 1
    Thank you for replying and adding more information. I think that this is a good answer and will be useful for OP and other users. – Tanaike Sep 27 '19 at 14:09