gpsread Batch Update Multiple Sheets

Question

Is there a way to Batch Update multiple sheets at once? I am trying to protect every worksheet in a workbook and I am doing:

def protectSheet(sheet):
    body = {
        "requests": [
            {
                "addProtectedRange": {
                    "protectedRange": {
                        "range": {
                            "sheetId": sheet._properties['sheetId'],
                        },
                        "warningOnly": True
                    }
                }
            }
        ]
    }
    try:
        workbook.batch_update(body)
    except gspread.exceptions.APIError:
        logger.warning("%s is already protected" % sheet._properties['title'])

for worksheet in workbook.worksheets():
    protectSheet(worksheet)

But is there a way to make a single call to protect every sheet in a workbook?

Edit: Alternatively is there a way to tell if a worksheet is protected?

Answer 1

I believe your goal as follows.

• In your question, there are following 2 questions.
  1. is there a way to make a single call to protect every sheet in a workbook?
  2. is there a way to tell if a worksheet is protected?.
• You want to achieve above using gspread with python.

Answer for question 1:

The following sample script protects all sheets in the Spreadsheet by one API call. But for example, a sheet in the Spreadsheet has already been protected, an error occurs. So please be careful this.

Sample script:

spreadsheetId = "###"  # Please set the Spreadsheet ID.

client = gspread.authorize(credentials)
ss = client.open_by_key(spreadsheetId)
worksheets = ss.worksheets()
requests = [
    {
        "addProtectedRange": {
            "protectedRange": {
                "range": {
                    "sheetId": e.id,
                },
                "warningOnly": True
            }
        }
    } for e in worksheets]
request_body = {"requests": requests}
res = ss.batch_update(request_body)
print(res)

Answer for question 2:

The following sample script retrieves all protected sheets in the Spreadsheet by one API call. In order to retrieve all protected sheets and ranges by one API call, it is required to use the method of "spreadsheets.get". Unfortunately, it seems that there is no method of "spreadsheets.get" in gspread. So I used the access token from credentials for gspread.

When you run the script, the sheets IDs of protected sheets are retrieved.

Sample script:

import requests  # This is used.

spreadsheetId = "###"  # Please set the Spreadsheet ID.

# client = gspread.authorize(credentials)
access_token = credentials.access_token
endpoint = "https://sheets.googleapis.com/v4/spreadsheets/" + spreadsheetId + "?fields=sheets.protectedRanges.range"
headers = {'Authorization': 'Bearer ' + access_token}
res = requests.get(endpoint, headers=headers)
obj = res.json()
sheetIds = []
for e in obj['sheets']:
    if 'protectedRanges' in e:
        for f in e.get('protectedRanges'):
            if 'range' in f and 'startRowIndex' not in f.get('range') and 'endRowIndex' not in f.get('range') and 'startColumnIndex' not in f.get('range') and 'endColumnIndex' not in f.get('range'):
                if 'sheetId' not in f.get('range'):
                    sheetIds.append(0)
                else:
                    sheetIds.append(f.get('range')['sheetId'])
print(sheetIds)

Other pattern:

As other pattern, the following sample script retrieves all unprotected sheets in the Spreadsheet.

Sample script:

import requests  # This is used.

spreadsheetId = "###"  # Please set the Spreadsheet ID.

# client = gspread.authorize(credentials)
access_token = credentials.access_token
endpoint = "https://sheets.googleapis.com/v4/spreadsheets/" + spreadsheetId + "?fields=sheets.properties%2Csheets.protectedRanges.range"
headers = {'Authorization': 'Bearer ' + access_token}
res = requests.get(endpoint, headers=headers)
obj = res.json()
sheetIds = []
for e in obj['sheets']:
    if 'protectedRanges' in e:
        for f in e.get('protectedRanges'):
            if 'range' not in f or ('range' in f and (
                'startRowIndex' in f.get('range') or
                'endRowIndex' in f.get('range') or
                'startColumnIndex' in f.get('range') or
                    'endColumnIndex' in f.get('range'))):
                sheetIds.append(f.get('range')['sheetId'])
    else:
        sheetIds.append(e['properties']['sheetId'])
print(sheetIds)

Reference:

• spreadsheets.get