Error updating a Google Spreadsheet with Python: The resource body includes fields which are not directly writable

Question

I'm trying to understand how to update a google sheet based on a local CSV file using Python. I do not want to create a new file as I need to ensure the file_id stays the same as it's used to feed a BI report in DataStudio.

Here's my code:

from __future__ import print_function
from googleapiclient import errors
from googleapiclient.http import MediaFileUpload
from googleapiclient.discovery import build
from httplib2 import Http
from oauth2client import file, client, tools


def get_authenticated(SCOPES, credential_file='credentials.json',
                  token_file='token.json', service_name='drive',
                  api_version='v3'):
    # The file token.json stores the user's access and refresh tokens, and is
    # created automatically when the authorization flow completes for the first
    # time.
    store = file.Storage(token_file)
    creds = store.get()
    if not creds or creds.invalid:
        flow = client.flow_from_clientsecrets(credential_file, SCOPES)
        creds = tools.run_flow(flow, store)
    service = build(service_name, api_version, http=creds.authorize(Http()))
    return service


def update_file(service, file_id, new_name, new_description, new_mime_type, new_filename):
    """Update an existing file's metadata and content.
    Args:
        service: Drive API service instance.
        file_id: ID of the file to update.
        new_name: New name for the file.
        new_description: New description for the file.
        new_mime_type: New MIME type for the file.
        new_filename: Filename of the new content to upload.
        new_revision: Whether or not to create a new revision for this file.
    Returns:
        Updated file metadata if successful, None otherwise.
    """
    try:
        # First retrieve the file from the API.
        file = service.files().get(fileId=file_id).execute()

        # File's new metadata.
        file['name'] = new_name
        file['description'] = new_description
        file['mimeType'] = new_mime_type
        file['trashed'] = True

        # File's new content.
        media_body = MediaFileUpload(
            new_filename, mimetype=new_mime_type, resumable=True)

        # Send the request to the API.
        updated_file = service.files().update(
            fileId=file_id,
            body=file,
            media_body=media_body).execute()
        return updated_file
    except errors.HttpError as error:
        print('An error occurred: %s' % error)
        return None

SCOPES = ['https://www.googleapis.com/auth/drive.file']

update_file(get_authenticated(SCOPES),
            "1eCz4lJGqPVdLWWa907BhyZCjCgTEPpFNHhveG8d0ktI",
            "test_name",
            "test_description",
            "application/vnd.google-apps.file",
            "gDriveTest.csv")

but I'm getting this error:

An error occurred: <HttpError 403 when requesting https://www.googleapis.com/upload/drive/v3/files/1eCz4lJGqPVdLWWa907BhyZCjCgTEPpFNHhveG8d0ktI?alt=json&uploadType=resumable returned "The resource body includes fields which are not directly writable.">

What's wrong?

Answer 1

I believe your goal and current situation as follows.

• You want to update Google Spreadsheet by uploading a CSV file from the local PC using googleapis for python.
• You have already been able to get and put the values for Google Drive using Drive API.

Modification points:

• The value retrieved from service.files().get(fileId=file_id).execute() includes the file ID. The file ID cannot be overwritten. I think that this is the reason of your error message of The resource body includes fields which are not directly writable..
• I think that in your case, mimeType is not required to be changed.
• And, in your new metadata, trashed is put as True. In this case, after the Spreadsheet was updated, the Spreadsheet is moved to the trash box. I thought that this might not the direction you expect.

When above points are reflected to your script, it becomes as follows.

Modified script:

From:

# File's new metadata.
file = service.files().get(fileId=file_id).execute()

# File's new metadata.
file['name'] = new_name
file['description'] = new_description
file['mimeType'] = new_mime_type
file['trashed'] = True

# File's new content.
media_body = MediaFileUpload(
    new_filename, mimetype=new_mime_type, resumable=True)

# Send the request to the API.
updated_file = service.files().update(
    fileId=file_id,
    body=file,
    media_body=media_body).execute()
return updated_file

To:

# File's new metadata.
metadata = {
    'name': new_name,
    'description': new_description,
    # 'trashed': True, # I thought that this might not be required.
}

# File's new content.
media_body = MediaFileUpload(new_filename, mimetype="text/csv", resumable=True)

# Send the request to the API.
updated_file = service.files().update(
    fileId=file_id,
    body=metadata,
    media_body=media_body).execute()
return updated_file

Reference:

• Files: update