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.

CodePudding user response：

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:

• values_append(range, params, body)
• update(range_name, values=None, **kwargs)