Lord Ratte Lord Ratte - 1 year ago 147
HTTP Question

How does one copy the users on protected ranges of Google Sheets with the REST API?

I am trying to copy a Google sheet using an HTTP get request. The following is an excerpt from my code.

data = {"title": name_gdocs, "description": record_url, "parents": fields_dict['parents']}
request_url = "https://www.googleapis.com/drive/v2/files/%s/copy?access_token=%s" % (template_id, access_token)
headers = {'Content-type': 'application/json', 'Accept': 'text/plain'}
data_json = json.dumps(data)

req = urllib2.Request(request_url, data_json, headers)
content = urllib2.urlopen(req, timeout=TIMEOUT).read()
content = json.loads(content)

The sheet is coppied and so are the protected ranges from the sheet but the copied document's protected ranges are only able to be accessed by the owner as apposed to the original document's specified users.

I can verify that the users that had access to the original document still have access in the new new one. They just lost access to the protected range.

Is there some sort of flag I can use to make a deep copy or something of the like?

Additionally, I would prefer doing it, only, with the drive API because I don't have access to the sheets API for this project. Otherwise I would just use that.

Answer Source

The solution is to use the Sheets API. The Drive API does not have the functionality to duplicate the owners on the protected ranges. Keep in mind that I used the REST API but the same function calls should essentially be made with language specific libraries.

Making the copy

The url syntax, as specified in the documentation to make the copy is POST https://www.googleapis.com/drive/v2/files/{fileId}/copy

This can be called with something like:

data = {"title": name_of_doc, "description": short_description, "parents": list_of_parent_folders}
# On absence of parents folder,the copy will be places in the root directory of the drive.

data_json = json.dumps(data)
request_url = "https://www.googleapis.com/drive/v2/files/%s/copy?access_token=%s" % (template_id, access_token)
headers = {'Content-type': 'application/json', 'Accept': 'text/plain'}
req = urllib2.Request(request_url, data_json, headers)
content_json = urllib2.urlopen(req, timeout=TIMEOUT).read()
content = json.loads(content_json)

Getting the protected-range owners

The documentation says that the request needs to use the GET method as follows GET https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}

Note: The function update_protected_range_to_google_sheet will be defined later on

if content.get('id') and content.get('mimeType','') == 'application/vnd.google-apps.spreadsheet':
# Make sure that the document is a Google Sheet

    request_url = "https://sheets.googleapis.com/v4/spreadsheets/%s?access_token=%s" % (template_id, access_token)
    s_req = urllib2.Request(request_url, None, headers)
    s_content = urllib2.urlopen(s_req, timeout=TIMEOUT).read()
    s_content = json.loads(s_content)
    for sheet in s_content.get('sheets', []):
        for pr in sheet.get('protectedRanges', []):
            update_protected_range_to_google_sheet(access_token, content.get('id'), pr)

Applying the owners

The documentation says to use the batch update call to the Sheets API as the name suggests, many requests can be done at once but in this case, the function will be called once per update for illustrative purposes. Here is the function I used that will be called:

def update_protected_range_to_google_sheet(self,access_token, sheet_id,range_info):
    headers = {'Content-type': 'application/json', 'Accept': 'text/plain'}
    request_url = "https://sheets.googleapis.com/v4/spreadsheets/%s:batchUpdate?access_token=%s" % (sheet_id, access_token)
    data = {
             "requests": [
                      "updateProtectedRange": {
                        "protectedRange": range_info,
                        "fields": "namedRangeId,warningOnly,editors"
    data_json = json.dumps(data)
    req = urllib2.Request(request_url, data_json, headers)
    content_json = urllib2.urlopen(req, timeout=TIMEOUT).read()
    content = json.loads(content_json)


The main reason I couldn't use the Sheets API was that the system I was using, had a custom built wrapper around the Drive API and the authentication was hardcoded to point to a project owned by the libraries creator.

This wouldn't do, so I created my own project on the console and generated a token from that. The new project had the Drive API enabled as well as the Sheets API - something the original project apparently didn't have.