I want to reflect the Pandas data frame on the spreadsheet.

I would like to add a data frame to the bottom of the spreadsheet using the API of the spreadsheet. Is there any good way?

What do you want to do

I want to add a data frame to the bottom of the spreadsheet

code:

import pandas as pd
import gspread
import csv
import json
from oauth2client.service_account import ServiceAccountCredentials
from gspread_dataframe import get_as_dataframe, set_with_dataframe

df = pd.read_csv('a.csv')

SPREADSHEET = 'Target Spreadsheet'
WORKSHEET='Sheet1'

sh=gc.open_by_key (SPREADSHEET)
sh.values_append (WORKSHEET, {'valueInputOption': 'USER_ENTERED'}, {'values': df.values.tolist()})

df contains the target data frame.

Error Contents:

gspread.exception.APIerror: {'code':400, 'message': 'Invalid JSON payload received.Unexpected token.\n.0,13500,50490.0,NaN,NaN,NaN,NaN,\n^', 'status': 'INVALID_ARGUMENT'}

The JSON file on the spreadsheet is also correctly specified and stored

data frame images:

Mr. A, 30 years old man
Mr. B, 25 years old man

I have looked into various things, but I would appreciate it if you could let me know because I could not find any similar errors.

python pandas google-spreadsheet

2022-09-30 15:04

1 Answers

It's easy with colab.I've never used it directly from the local python, so I can't compare it, but
Disadvantages

Python version is slightly lower
If you have local data, it will be via Google-drive, etc.

 from google.colab import auth
auth.authenticate_user()

import gspread
from google.auth import default
credits,_=default()

gc = gspread.authorize (creds)

import pandas aspd
worksheet=gc.open('Title').sheet1

df=pd.DataFrame({'Name':['A', 'B', 'Age':[30,25], 'Gender':['Man', 'Man']})
display(df)
worksheet.append_rows([df.columns.values.tolist()] + df.values.tolist())

2022-09-30 15:04

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