Error adding data frame to Google spreadsheet: type.googleapis.com/google.protobuf.ListValue

I would like to reflect the data (one line) that I have crawled to the bottom line of the spreadsheet, but the error below appears and does not reflect well.
Is there a solution?

code

JSON_KEYFILE= 'JSON file in API'
SPREADSHEET = 'TEST'
WORKSHEET='Sheet1'

scope=['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']

# Authentication Information Settings
serviceAccountCredentials=ServiceAccountCredentials.from_json_keyfile_name(JSON_KEYFILE, scope)
gspreadClient=gspread.authorize (serviceAccountCredentials)
worksheet=gspreadClient.open(SPREADSHEET).worksheet(WORKSHEET)

df=pd.read_csv('test.csv', encoding="shift_jis")
ls = df.loc [0:0]

ls=ls.to_json()

spread=client.open (SPREADSHEET) 
sheet=spread.worksheet(WORKSHEET)
worksheet.append_row(ls)

error code

API error occurs in worksheet.append_row(ls).

gspread.exceptions.APIerror: {'code':400, 'message': 'Invalid value at\'data.values[0]\'(type.googleapis.com/google.protobuf.ListValue),"{"\\u58f2\\u4e0a\\u65e5":{"0":"2021\\\/06\\"}, "\\u30a4\\u303u307"\\\\u3030e7(\\\\u307\\\\u307\u30e7\\\\\u30\\)u30e7\\\\u4ef6\\u6570": {"0":0}, "\\u30aa\\u30fc\\u30c0\\u30fc\\u767a\\u751f\\u500b\\u6570": {"0":0}, "\\u30aaaa\\u30fc0\u30fc\u30d\\u30e3\\u30f3\\u30b\u30b\u\u\u30b\\\u6570"{0u30a}4e0a\\u984d": {"0":0}, "\\u5831\\u916c\\u5bfe\\u8c61\u30af\u30ea\\u30c3\\u30af\u30af\u6570": {"0":0}, "\\\u5831\u916c61\u30ea\u30fc9(\u30c9(\u63d0\\u643a\\\\\u8d0\\\u6a\\\\u6a\u0\\utype.googleapis.com/google.rpc.BadRequest', 'fieldViolations': [{'field': 'data.values[0]', 'description': 'Invalid value at \'data.values[0]\' (type.googleapis.com/google.protobuf.ListValue), "{"\\u58f2\\u4e0a\\u65e5":{"0":"2021\\/06\\/14"},"\\u30a4\\u30f3\\u30d7\\u30ec\\u30c3\\u30b7\\u30e7\\u30f3(I)":{"0":19},"\\u30af\\u30ea\\u30c3\\u30af(C)":{"0":36},"\\uUS>30aa\\u30fc\\u30fc0\\u30fc\\u767a\\u751f\\u4ef6\\u6570": {"0":0}, "\\u30aaa\\u30fc\\u30fc\\u30fc\\u30fc\\\u767a\u500b\u6570": {":0}, "\\u30aaa\u30fc0\u30u30fc0\\\\u30\u30\u30\u30\u30US>831\\u916c\\u5bfe\\u8c61\\u7d14\\u58f2\\u4e0a\\u984d": {"0":0}, "\\u5831\u916c\\u5bfe\\u8c61\u30ea\\\u30c3\\u30af\u30c3\\u30af\u6570": {":0}, "\\\\\\u5831\u916c2\u5bfe\\\\\u8uc303u30

python python3 google-spreadsheet

2022-09-30 17:29

1 Answers

As metropolis comments, if you want to insert values into Spreadsheet using the Sheets API, the data portion that enters the cell must be a list.So, how about the following modifications?

In this pattern, dataframe is converted to a list and inserted into a cell using the values_update method.

From:

ls=df.loc [0:0]
ls=ls.to_json()

spread=client.open (SPREADSHEET) 
sheet=spread.worksheet(WORKSHEET)
worksheet.append_row(ls)

To:

ls=df.loc [0:0]

values = [ls.columns.values.tolist()]
values.extend(ls.values.tolist())

spread=client.open (SPREADSHEET)
spread.values_update(WORKSHEET, params={'valueInputOption':'USER_ENTERED'}, body={'values':values})

This pattern uses a library with gspread-dataframe.

From:

ls=df.loc [0:0]
ls=ls.to_json()

spread=client.open (SPREADSHEET) 
sheet=spread.worksheet(WORKSHEET)
worksheet.append_row(ls)

To:

ls=df.loc [0:0]

spread=client.open (SPREADSHEET) 
sheet=spread.worksheet(WORKSHEET)
gd.set_with_dataframe(sheet,ls)

In this case, use import gspread_dataframe as gd.

References:

2022-09-30 17:29

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656