Search code examples
pythoncsvgoogle-sheetsgoogle-sheets-apigspread

Not able to append values from a dataFrame to a google sheet at specific columns

import pandas as pd
import pygsheets
import gspread
from gspread_dataframe import set_with_dataframe
from google.oauth2.service_account import Credentials

def csv_to_sheets():
   tokenPath ='path for service account file.json'
   scopes = ['https://www.googleapis.com/auth/spreadsheets',
        'https://www.googleapis.com/auth/drive']

   credentials = Credentials.from_service_account_file(tokenPath, scopes=scopes)
   gc = gspread.authorize(credentials)
   gs = gc.open('csv_to_gSheet')
   workSheet1 = gs.worksheet('sheet1')
   my_csv = pd.read_csv("my csv file path")
   my_csv_values = my_csv.values.tolist()
   gs.values_append('sheet1', {'valueInputOption': 'RAW'}, {'values': my_csv_values}, range='S:AE')`

csv_to_sheets()

After running the following code I get an error saying :

TypeError: values_append() got multiple values for argument 'range'

And if I remove the range from append method then the values get appended from the first column ie. from 'A till the data ends' but not in the columns I want ie. from 'S:AE'. Also, how to append date in the first column and rest of the data in the specified range. Please help. Thank you in advance.

Solution

  • Modification points:

    • It seems that the arguments of values_append is values_append(range, params, body). Ref I thought that this might be the reason of your issue.

    When this is refrected in your script, how about the following modification?

    From:

    gs.values_append('sheet1', {'valueInputOption': 'RAW'}, {'values': my_csv_values}, range='S:AE')

    To:

    gs.values_append("sheet1!S:AE", {"valueInputOption": "RAW"}, {"values": my_csv_values})

    • In this modification, the value of my_csv_values is appended to the last row of columns "S" to "AE".

    • If you want to append the values to the last row of the data range, how about the following modification?

      • From

          gs.values_append('sheet1', {'valueInputOption': 'RAW'}, {'values': my_csv_values}, range='S:AE')

      • To

          workSheet1.update("S" + str(len(workSheet1.get_all_values()) + 1), my_csv_values, value_input_option="USER_ENTERED")

    References: