google sheets - Python: Authentication, Upload file to drive & edit an existing spreadsheet - PyDrive

Question

I need to upload some images & edit some Google Spreadsheets within my desktop application, and chose PyDrive to help me access my drive (seemed easier at the time than the regular Google API). However, I'm having difficulties to make changes to an existing spreadsheet, ex add a new row with a string.

I am able to upload the file to correct folder in Drive, however I haven't found a way to edit the table in a good manner (setContentString replaces the current content; but I need to update the content / add a new row with a a new string. ".GetContentString" does not seem to function as expected neither).

How should one go about it? Do I need to download the spreadsheet & make changes to it in pandas (or similar), or can I do this online?

Please find my code below:

from __future__ import print_function
import pickle
import os.path
from googleapiclient.discovery import build
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request
from pydrive.auth import GoogleAuth
from pydrive.drive import GoogleDrive
from pydrive.files import GoogleDriveFile


# Try to load saved client credentials
gauth = GoogleAuth()

gauth.LoadCredentialsFile("mycreds.txt")
if gauth.credentials is None:
    # Authenticate if they're not there
    gauth.LocalWebserverAuth()
elif gauth.access_token_expired:
    # Refresh them if expired
    gauth.Refresh()
else:
    # Initialize the saved creds
    gauth.Authorize()
# Save the current credentials to a file
gauth.SaveCredentialsFile("mycreds.txt")

drive = GoogleDrive(gauth)

def uploadImage(typeInput ,imgFile, listInput):
    # Determine the folder ID
    if type == "Type1":
        folderID ="folderID1"
    elif type == "Type2":
        folderID = "folderID2"


    str1 = "'" + folderID + "'" + " in parents and trashed=false"

    file_list = drive.ListFile({'q': str1}).GetList()
    imgTitles = []
    for file in file_list:
        q = (file['title'])
        imgTitles.append(q)
        # print(str(q))
    print(str(imgTitles))

    imgNr = imgTitles[0]
    imgNr = str(imgNr)[6:]
    imgNr = int(imgNr) + 1
    imgFileName = "Image_" + str(imgNr)

    imageToUpload = drive.CreateFile({'parents': [{'id': folderID}]})
    imageToUpload['title'] = imgFileName
    imageToUpload.SetContentFile(imgFile)
    imageToUpload.Upload()
    print('title: %s, id: %s' % (imageToUpload['title'], imageToUpload['id']))
    # call saveCoordsToTable
    addToTable(listInput, "typeInput", "imgFileName")


def addToTable(list_input, type_input, fileName_input):
    if type == "Type1":
        fileID = "folderID1"
    elif type == "Type2":
        fileID = "folderID2"


    folderID = "FolderIDxyz"  # The folder where the Google Sheets are

    str2 = "'" + fileID + "'" + " in parents and trashed=false"

    GSheet = drive.CreateFile({'q': str2})

    content = GSheet.GetContentString("test.csv")
    GSheet.SetContentString(content + str(list_input,
                                          type_input)  # Should take the current content and add a string to it. I probably need to specify somehow that it is a new row, but I have no idea how.
    GSheet.Upload()

uploadImage("Type1", img.png, [1, 2, 3, 4])

Thanks!

question from:https://stackoverflow.com/questions/65922721/python-authentication-upload-file-to-drive-edit-an-existing-spreadsheet-py

Answer 1

You should use separate library for editing the sheets. You can use pygsheet or directly use the Google Sheets API.

For pygsheet, you can use insert_rows and append_table to add rows with values and values.append for Google Sheets.

Google Sheets API example: link

References:

• pygsheets
• Google Sheets API