0

Here is the process I would like to make work using the gspread library:

  1. Manually share a google sheet I have created with a service account.
  2. Use the service account to make a copy of the spreadsheet in step 1.
  3. Share the copy of the spreadsheet with another user, X.
  4. Transfer ownership of the copy to that same user, X.

Steps 1-3 are no problem. Step 4 is where I am getting errors.

Here is what I have tried

Step 1: Executed from the google sheets GUI. Share > Add service accounts email > give editor role.

Step 2:

gc = gspread.service_account("insert_path_to_credentials.json")

KEY = "insert-spreadsheet-key-from-step1" 

ss_copy = gc.copy(file_id=KEY)

Step 3 (FAILS):

ss_copy.share(email_address="emailaddress@gmail.com", 
              perm_type="user", 
              role="owner", 
              notify=True)

Error:

APIError: {'code': 403, 'message': "The transferOwnership parameter must be enabled when the permission role is 'owner'.", 'errors': [{'message': "The transferOwnership parameter must be enabled when the permission role is 'owner'.", 'domain': 'global', 'reason': 'forbidden', 'location': 'transferOwnership', 'locationType': 'parameter'}]}

Step 3 (Successful):

ss_copy.share(email_address="emailaddress@gmail.com", 
              perm_type="user", 
              role="writer", 
              notify=True)

Step 4, Try 1: Insert owner permissions then try .transfer_ownership() (Fails):

gc.insert_permission(file_id=ss_copy.id, 
                     value="emailaddress@gmail.com", 
                     perm_type="user", 
                     role="owner")

Error:

APIError: {'code': 403, 'message': "The transferOwnership parameter must be enabled when the permission role is 'owner'.", 'errors': [{'message': "The transferOwnership parameter must be enabled when the permission role is 'owner'.", 'domain': 'global', 'reason': 'forbidden', 'location': 'transferOwnership', 'locationType': 'parameter'}]}

Step 4, Try 1: Insert writer permissions then try .transfer_ownership() (Fails):

gc.insert_permission(file_id=ss_copy.id, 
                     value="emailaddress@gmail.com", 
                     perm_type="user", 
                     role="writer")

ss_copy.transfer_ownership(permission_id="permission-id-generated-by-line-above")

Error:

APIError: {'code': 403, 'message': 'The owner of a file cannot be removed.', 'errors': [{'message': 'The owner of a file cannot be removed.', 'domain': 'global', 'reason': 'cannotRemoveOwner'}]}

I have reviewed these two posts below, but neither one are using the gspread library. They are also written prior to the transfer_ownership() method being added in the more recent versions of gspread:

  1. Transfer Ownership of Google Sheets from Service Account to General Account
  2. Unable to transfer ownership of file from service account to google drive user

The end goal of what I am hoping to do is transfer spreadsheet ownership to another user, so they are able to set up installable App Script triggers. If you try to run App Script in a google sheet owned by a service account you get the error:

"The Script cannot be run because it is owned by a service account. Please copy or transfer the project to a valid account before running."

If there was a way for me to copy the sheet with the service account, share it with another user, AND set up the app script trigger automatically with the trigger owned by the service account that would be ideal, but it is my current understanding that this is not possible.

My next best option is to copy the sheet with the service account, share it with another user, and transfer ownership to them so they can manually set the installable trigger up themselves, however this also appears to be non-trivial do to the issues outlined above.

Braden Anderson
  • 141
  • 1
  • 11

1 Answers1

2

In the current stage, in order to transfer the ownership, it is required to 2 steps. Ref

  1. At the current owner (in your situation, it's the service account.), Set the user as a writer with pendingOwner=true.

  2. At the user who will be the owner, accept the owner transfer as follows.

enter image description here

Of course, the accepting operation can be also done by Drive API. But, in that case, it is required to run the script with the user's access token. From your question, I thought that this operation might be able to be manually done.

When 1st flow of the above is run with a script using gspread, the sample script is as follows.

Sample script:

import gspread


KEY = "###" # Please use your spreadsheet ID.
email = "emailaddress@gmail.com" # Please set email address you want to transfer the ownership.
gc = gspread.service_account(filename="###") # Please use your client.

ss_copy = gc.copy(file_id=KEY)
res = ss_copy.share( email_address=email, perm_type="user", role="writer", notify=True)
permissionId = res.json()["id"]
ss_copy.transfer_ownership(permissionId)
  • When this script is run, the Spreadsheet of KEY is copied and the copied Spreadsheet is shared with email. And, the user of email gets an email. When the user manually opens the Spreadsheet and accepts the transfer of ownership as shown in the above image, the owner of the Spreadsheet is changed from the service account to the user.

References:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • Thanks for your reply! How are you setting `pendingOwner=true` in step 1? Is that something we can do through gspread? – Braden Anderson Apr 05 '23 at 18:54
  • Going to mark this as accepted because your sample script works as described if the email address is an `@gmail.com` address. One place I was getting tripped up was not understanding what `you cannot transfer ownership to a group or a domain email address.` really means in the `transfer_ownership()` documentation. I was trying to transfer ownership to an `@buisnessName.com` email address that uses gmail, which does not work with the code above. I may open a new question to discuss this new problem but I think this one can be considered solved. – Braden Anderson Apr 05 '23 at 19:22
  • @Braden Anderson Thank you for replying. About `How are you setting pendingOwner=true in step 1? Is that something we can do through gspread?`, in the case of gspread, it seems that `transfer_ownership` requests using it. In the case of Drive API, when a new permission is created, `pendingOwner=true` can be included in the request. But, at gspread, the creation of permission and `pendingOwner=true` are separated as 2 requests. About email addresses, in this case, I think that Gmail and the email of Google Workspace can be used. – Tanaike Apr 05 '23 at 23:50