How can I change the owner of a Google Sheets spreadsheet?

Question

With the following, I can programmatically create a spreadsheet in Google sheets, but the owner of the sheet is the developer account (a crazy string ending in "gserviceaccount.com"), and my normal account can't view the spreadsheet. What else do I need to do in order to add Google users to the read/write permissions?

from oauth2client.service_account import ServiceAccountCredentials
from googleapiclient import discovery

# ... json_key is the json blob that has the credentials
scope = ['https://spreadsheets.google.com/feeds']
credentials = ServiceAccountCredentials.from_json_keyfile_dict(json_key, scope)
service = discovery.build('sheets', 'v4', credentials=credentials)

spreadsheet = {
  "properties": {"title": "my test spreadsheet"}
}

service.spreadsheets().create(body=spreadsheet).execute()

Edit:
I tried changing the scope to ['https://www.googleapis.com/auth/drive'] but the answer below still doesn't work for me. When I run

print [xx for xx in dir(service) if not xx.startswith('_')]

I get

['new_batch_http_request', u'spreadsheets']

In other words, permissions() isn't a method in service as I have service defined. What should I be doing differently?

Answer 1

I figured it out from reading the comment left by Chris. All that was missing from his comments is you do in fact need to use particular scopes in his drive_service. Notice the changes in scope I use to build the different objects:

from oauth2client.service_account import ServiceAccountCredentials
from googleapiclient.discovery import build

key = '/path/to/service_account.json'

# Build 'Spreadsheet' object

spreadsheets_scope = [ 'https://www.googleapis.com/auth/spreadsheets' ]
sheets_credentials = ServiceAccountCredentials.from_json_keyfile_name(key, spreadsheets_scope)

sheets_service = build('sheets', 'v4', credentials=sheets_credentials)

# returns 'Spreadsheet' dict
# https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets#resource-spreadsheet
spreadsheet = sheets_service.spreadsheets().create(
    body={
        "properties": {
            'title': 'spreadsheets test',
        },
        "sheets": [],
    }
).execute()


# id for the created file
spreadsheetId = spreadsheet['spreadsheetId']
# url of your file
spreadsheetUrl = spreadsheet['spreadsheetUrl']

# Build 'Permissions' object
drive_scope = [ 'https://www.googleapis.com/auth/drive' ]
drive_credentials = ServiceAccountCredentials.from_json_keyfile_name(key, drive_scope)

drive_service = build('drive', 'v3', credentials=drive_credentials)

# returns 'Permissions' dict
permissions = drive_service.permissions().create(
    fileId=spreadsheetId,
    transferOwnership=True,
    body={
        'type': 'user',
        'role': 'owner',
        'emailAddress': 'example@email.com',
    }
).execute()

# apply permission 
drive_service.files().update(
    fileId=spreadsheetId,
    body={'permissionIds': [permissions['id']]}
).execute()

print ('\nOpen me:\n\n%s\n' % spreadsheetUrl)

So the logic is, a 'Spreadsheet Resource' is made from build with all its properties and sheet data, with the owner set to your service account. Next, a 'Drive Resource' is made, this is the Resource with the permissions() method. execute() returns a newly created permissions id used to update() the spreadsheet file.

Answer 2

Service is just a generic name for the result of a discovery.build call. In this case, not having the 'permissions' method is just that its not available on the same service. The following code should be sufficient if changing owner isn't required. To add someone with read and write access, the following works for me:

def grant_permissions(spreadsheet_id, writer):
    drive_service = discovery.build('drive', 'v3')

    permission = drive_service.permissions().create(
        fileId=spreadsheet_id,
        body={
            'type': 'user',
            'role': 'writer',
            'emailAddress': writer,
        }
    ).execute()

    drive_service.files().update(
        fileId=spreadsheet_id,
        body={'permissionIds': [permission['id']]}
    ).execute()

To actually change the owner, the transfer ownership flag must be set:

def change_owner(spreadsheet_id, writer):
    drive_service = discovery.build('drive', 'v3')

    permission = drive_service.permissions().create(
        fileId=spreadsheet_id,
        transferOwnership=True,
        body={
            'type': 'user',
            'role': 'owner',
            'emailAddress': writer,
        }
    ).execute()

    drive_service.files().update(
        fileId=spreadsheet_id,
        body={'permissionIds': [permission['id']]}
    ).execute()

The service account being used must have the right permissions though. I believe the ones that worked for me was checking the g suite box when first creating the service account.

Answer 3

Try to use the method Permissions: insert from the documentation. You will be able to insert a permission for a file or a Team Drive.

Here is the sample code provided from the documentation:

from apiclient import errors
# ...

def insert_permission(service, file_id, value, perm_type, role):
  """Insert a new permission.

  Args:
    service: Drive API service instance.
    file_id: ID of the file to insert permission for.
    value: User or group e-mail address, domain name or None for 'default'
           type.
    perm_type: The value 'user', 'group', 'domain' or 'default'.
    role: The value 'owner', 'writer' or 'reader'.
  Returns:
    The inserted permission if successful, None otherwise.
  """
  new_permission = {
      'value': value,
      'type': perm_type,
      'role': role
  }
  try:
    return service.permissions().insert(
        fileId=file_id, body=new_permission).execute()
  except errors.HttpError, error:
    print 'An error occurred: %s' % error
  return None

Use Try it now to test live data and see the API request and response.

For further reading, check this SO post.