• I can read data and write data to a google sheet.
• I can also write a pandas dataframe to a google sheet cell(1,1).

I am now trying to write the same dataframe to a specific cell (like cell(20,20)) on a sheet.

resources:

• this link is close, but only refers to the sheet and does not specify the cell: How can I Export Pandas DataFrame to Google Sheets using Python?

• the docs are here, but the example seems to only refer to a sheet (so the result is again cell(1,1)): https://docs.gspread.org/en/latest/user-guide.html#using-gspread-with-pandas

what i have tried:

• i have tried to modify the update_cell command with a dataframe.

# this works
sh.sheet1.update_cell(20,20,'hello world')

# but this fails

# example dataframe
d = {'col1': [1, 2], 'col2': [3, 4]}
df = pd.DataFrame(data=d)

# write to a specific cell
sh.sheet1.update_cell(20,20,df)

So my question is, how can i specify the cell that the pandas Dataframe is written to ?

CodePudding user response：

It seems that the data frame cannot be directly used to value of update_cell(row, col, value). Ref So, in your script, how about the following modification?

From:

sh.sheet1.update_cell(20,20,df)

To:

sh.sheet1.update("T20", [df.columns.values.tolist(), *df.values.tolist()], value_input_option="USER_ENTERED")

or, if you don't want to include the header row, please test the following modification.

sh.sheet1.update("T20", df.values.tolist(), value_input_option="USER_ENTERED")

• I thought that 20,20 of sh.sheet1.update_cell(20,20,df) is a cell "T20".

Note:

• From your showing script, I guessed that you are using gspread.

Reference:

• update(range_name, values=None, **kwargs)