Update CSV file on Google Drive to be used on Data Studio with PyDrive

Question

I need to update a CSV file located on my Drive because I use it on a dashboard in Google Data Studio. Until now, I was using this code :

previous_GDS_df = pd.read_excel(path_to_GDS_file)
pd.concat(objs=[previous_GDS_df, df_GDS]).to_excel(path_to_GDS_file, index=False)
f = drive.CreateFile({'id': spreadsheet_id})
f.SetContentFile(path_to_GDS_file)
f.Upload()

On which :

• "previous_GDS_df" is the content of the CSV file I'm updating,
• ""path_to_GDS_file" the path to my local CSV file, on which I do the modifications,
• "df_GDS" the df of my modifications, the elements I'd like to append to my file on Drive.

Basically, my theory was the following : "I extract the previous content of the file, I append to it the new content then I edit my Drive file with 'SetContentFile' and I upload it all."

The problem is that when I edit my file on Drive, I need to reconnect everytime my file in my dashboard GDS because I think that SetContentFile erase entirely the previous file Drive to write a new one. In this case, I must reconnect the Drive file to GDS because it was deleted and rewrited.

So, I'm looking for a solution to update my Drive file so I will not have to reconnect everytime my file to the dahsboard and the modifications will appear magically.

Do you have a solution ? My theory is surely bad. I'm missing something somewhere.

Thank you for your help, ask me if more information is needed.

--- EDIT --- I've tested some solutions but nothing worked. The best solution i tested was this one (thanks to the helps in the comments) :

from __future__ import print_function

import os.path

from google.auth.transport.requests import Request
from google.oauth2.credentials import Credentials
from google_auth_oauthlib.flow import InstalledAppFlow
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError
from apiclient.http import MediaFileUpload

SCOPES = ['https://www.googleapis.com/auth/drive'] # If modifying these scopes, delete the file token.json.

def getCreds(): # Authentication
    
    creds = None
    # 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.
    if os.path.exists('token.json'):
        creds = Credentials.from_authorized_user_file('token.json', SCOPES)
    # If there are no (valid) credentials available, let the user log in.
    if not creds or not creds.valid:
        if creds and creds.expired and creds.refresh_token:
            creds.refresh(Request())
        else:
            flow = InstalledAppFlow.from_client_secrets_file(
                'credentials.json', SCOPES)
            creds = flow.run_local_server(port=0)
        # Save the credentials for the next run
        with open('token.json', 'w') as token:
            token.write(creds.to_json())
    
    return creds


def updateFile(service, spreadsheet_id, path_to_GDS_file): # Call the API
    media = MediaFileUpload(path_to_GDS_file, mimetype='application/vnd.google-apps.spSreadsheet', resumable=True)
    res = service.files().update(fileId=spreadsheet_id,media_body=media,fields="*").execute()
    return res

def main(spreadsheet_id, path_to_GDS_file):
    creds = getCreds()
    service = build('drive', 'v3', credentials=creds)
    updateFile(service, spreadsheet_id, path_to_GDS_file)

if __name__ == '__main__':
    main()

But the main() function doesn't just append to my csv file on GDrive, it rewrites the whole file so I have to reconnect on Data Studio.

Do you know how I could just append rows to my csv file located on the GDrive ?

Thank you.

Answer 1

I don't know much about pydrive, but in order to update a file via Drive API, you have to use Files: update. This allows you either to just update file metadata, or also file content.

Here's a possible sample that uses the official Python library:

from __future__ import print_function

import os.path

from google.auth.transport.requests import Request
from google.oauth2.credentials import Credentials
from google_auth_oauthlib.flow import InstalledAppFlow
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError
from apiclient.http import MediaFileUpload

SCOPES = ['https://www.googleapis.com/auth/drive'] # If modifying these scopes, delete the file token.json.
fileId = "DRIVE_FILE_ID" # Change to yours
filePath = "LOCAL_FILE_PATH" # Change to yours

def getCreds(): # Authentication
    creds = None
    if os.path.exists('token.pickle'):
        with open('token.pickle', 'rb') as token:
            creds = pickle.load(token)
    # If there are no (valid) credentials available, let the user log in.
    if not creds or not creds.valid:
        if creds and creds.expired and creds.refresh_token:
            creds.refresh(Request())
        else:
            flow = InstalledAppFlow.from_client_secrets_file(
                'credentials.json', SCOPES)
            creds = flow.run_local_server(port=0)
        # Save the credentials for the next run
        with open('token.pickle', 'wb') as token:
            pickle.dump(creds, token)
    return creds

def updateFile(service, fileId): # Call the API
    media = MediaFileUpload(filePath, mimetype='text/csv', resumable=True)
    res = service.files().update(fileId=fileId,media_body=media,fields="*").execute()
    return res

def main():
    creds = getCreds()
    service = build('drive', 'v3', credentials=creds)
    updateFile(service, fileId)

if __name__ == '__main__':
    main()

Note:

You'll first have to download your credentials file, as explained in the quickstart referenced below.

Reference:

• Python Quickstart
• service.files().update()