Unable to append values to specific columns in google sheets from a csv-CodePudding

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()
   for ele in my_csv_values:
       workSheet1.update("A"   str(len(workSheet1.get_all_values())   1), ele[0], value_input_option="USER_ENTERED")
       workSheet1.update("S"   str(len(workSheet1.get_all_values())   1), ele[1:], value_input_option="USER_ENTERED")`

csv_to_sheets()

I have a csv which has a date column and rest are the data columns. I want to append this csv to google sheet in such a way that, my date column gets appended to column 'A' of google sheet and rest data gets appended from column 'S' and nothing in between. If I try to follow the above approach, I get a huge error while appending my data columns (last line) saying : gspread.exceptions.APIError: {'code': 400, 'message': "Invalid value at 'data.values[0]' (type.googleapis.com/google.protobuf.ListValue)

What should I do to do it together. Please help. Thank you in advance.

This is the input csv with first row as headers.

This is how I need my output in google sheets. The date should be in the first column and remaining data should be appended from column "S".

The input is as shown in the fig.1. The fig.3 shows the output in google sheet. The data till row 16 is already present in the sheet. We need to append the data from row 17 onwards. When we use the modification, and run the script for the 1st time, the values are correctly getting appended in col. "A" and in rest of the columns except for col. "S". (check col. "S" from row 17 to 23). But when I run the same modification script for the 2nd time, the values are correctly appended not only in col. "S" but in other columns as well. (check col. "S" from row 24 to 27)

So, I want to understand why am I getting those values in col. "S" from row 17 to 23 which are not even present in my input and what should I do to get the correct values as sheen in row 24 to 27.

CodePudding user response：

Modification points:

In the case of for ele in my_csv_values:, each row is uploaded and 3 API calls are used every loop.
And, in your script, ele[0] is no array. And, ele[1:] is a 1-dimensional array. In this case, an error occurs. I thought that this might be the reason for your current error message.

When these points are reflected in your script, how about the following modification?

From:

for ele in my_csv_values:
    workSheet1.update("A"   str(len(workSheet1.get_all_values())   1), ele[0], value_input_option="USER_ENTERED")
    workSheet1.update("S"   str(len(workSheet1.get_all_values())   1), ele[1:], value_input_option="USER_ENTERED")`

To:

row = str(len(workSheet1.get_all_values())   1)
workSheet1.batch_update([
    {"range": "A"   row,"values": [[r[0]] for r in my_csv_values]},
    {"range": "S"   row,"values": [r[1:] for r in my_csv_values]},
  ],
  value_input_option="USER_ENTERED",
)

By this modification, this process can be done by 2 API calls.