Search code examples
pythongoogle-apps-scriptgoogle-sheetsgoogle-drive-apigspread

Copy google sheet with service account and then transfer ownership to a different user gspread 5.7.1

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.

Solution

  • 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: